{ "cells": [ { "cell_type": "markdown", "id": "1a193681-fa46-42f2-a898-6429d6403801", "metadata": {}, "source": [ "# Add content from the Tasmanian Post Office Directories to an SQLite database\n", "\n", "After [downloading all the PDFs](tas-pod-save-text-images.ipynb) of the Tasmanian Post Office Directories from 1890 to 1948, I [extracted the images](tas-pod-save-text-images.ipynb), [uploaded the images to Amazon s3](tas-pod-upload-images.ipynb), and [extracted text from the images using Tesseract](tas-pod-ocr-with-tesseract.ipynb). This notebook brings everything together in an SQLite database ready for delivery through Datasette. Each page of text is indexed by line and linked to the page images.\n", "\n", "This is a slightly modified version of [the code I used](https://glam-workbench.net/trove-journals/create-text-db-indexed-by-line/) with data from the Trove journals section to generate new search interfaces for the [NSW Post Office directories](https://glam-workbench.net/trove-journals/nsw-post-office-directories/), and the [Sydney Telephone directories](https://glam-workbench.net/trove-journals/sydney-telephone-directories/).\n", "\n", "**Explore the [completed search interface](https://glam-workbench.net/tasmanian-post-office-directories/) for the Tasmanian Post Office Directories!**" ] }, { "cell_type": "code", "execution_count": 1, "id": "ab9913e0-6e8f-4302-90b7-4450ff5bb6e2", "metadata": {}, "outputs": [], "source": [ "# Let's import the libraries we need.\n", "import json\n", "import re\n", "from pathlib import Path\n", "\n", "import requests\n", "from natsort import natsorted, ns\n", "from requests.adapters import HTTPAdapter\n", "from requests.packages.urllib3.util.retry import Retry\n", "from sqlite_utils import Database\n", "\n", "s = requests.Session()\n", "retries = Retry(total=5, backoff_factor=1, status_forcelist=[502, 503, 504])\n", "s.mount(\"https://\", HTTPAdapter(max_retries=retries))\n", "s.mount(\"http://\", HTTPAdapter(max_retries=retries))" ] }, { "cell_type": "markdown", "id": "9f7cd482-7ddc-482a-8257-bee2c9cb6711", "metadata": {}, "source": [ "First we'll create a skeleton metadata file for Datasette." ] }, { "cell_type": "code", "execution_count": 39, "id": "f605413a-e1d0-4367-b746-5db7e03e5614", "metadata": { "tags": [ "nbval-skip" ] }, "outputs": [], "source": [ "db_path = Path(\"tasmania-datasette\")\n", "db_path.mkdir(exist_ok=True)\n", "\n", "# This the basic metadata file that will be used by Datasette\n", "# The processing steps below will add details for each table/volume into the metadata file\n", "# Obviously you'd modify this for a different publication!\n", "\n", "metadata = {\n", " \"title\": \"Tasmanian Post Office Directories\",\n", " \"description_html\": \"

This is an experimental interface to the Tasmanian Post Office Directories, which have been digitised from the collections of the State Library of NSW and are now available on Trove.

Searching for entries in the directories on Trove is difficult, because Trove's results are grouped by 'article', which is not very helpful for publications like these where information is organised by row. This interface searches for individual lines of text, rather than pages or articles. So it points you straight to entries of interest. Once you've found something, you can view the entry within the context of the complete page, or click back to Trove to explore further.

\",\n", " \"databases\": {\n", " \"tasmanian-post-office-directories\": {\n", " \"title\": \"Tasmanian Post Office Directories, 1890 to 1948\",\n", " \"source\": \"Trove\",\n", " \"source_url\": \"https://stors.tas.gov.au/ILS/SD_ILS-981598\",\n", " \"tables\": {},\n", " }\n", " },\n", "}" ] }, { "cell_type": "markdown", "id": "7bd2f9f8-5ffe-4e2d-9203-c46fd4fc52e8", "metadata": {}, "source": [ "Now we'll populate the SQLite database." ] }, { "cell_type": "code", "execution_count": null, "id": "855ca876-6a54-4ce4-90b0-c43300cb9709", "metadata": { "tags": [ "nbval-skip" ] }, "outputs": [], "source": [ "# Create the database\n", "# Change the name as apporpriate!\n", "db = Database(Path(db_path, \"tasmanian-post-office-directories.db\"))\n", "\n", "# Create database tables for pages and volumes\n", "page_table = db[\"pages\"]\n", "vols_table = db[\"volumes\"]\n", "\n", "# Loop through the directories of each volume created by the harvesting process (above)\n", "# Use natsorted so that they're processed in date order\n", "vols = natsorted(\n", " [d for d in Path(\"tasmania\").glob(\"AUTAS*\") if d.is_dir()], alg=ns.PATH\n", ")\n", "\n", "for vol in vols:\n", " print(vol)\n", " obj_id = vol.name\n", " # In the case of the PO Directories each volume has a different year (or year range) in the title.\n", " # Here we're extracting the year and id, but extracting the year in this way might not work for other titles.\n", " # The year is used as the title of the table in Datasette\n", " year = re.search(r\"AUTAS\\d+P([0-9\\-]+)PDF\", vol.name).group(1)\n", "\n", " # Add a record for this volume to the database\n", " vols_table.insert({\"vol_id\": obj_id, \"year\": year}, pk=\"vol_id\")\n", "\n", " # Update the metadata file with details of this volume\n", " metadata[\"databases\"][\"tasmanian-post-office-directories\"][\"tables\"][year] = {\n", " \"title\": f\"Tasmanian Post Office Directory, {year}\",\n", " \"source\": \"Libraries Tasmania\",\n", " \"source_url\": f\"https://stors.tas.gov.au/{obj_id}\",\n", " \"searchmode\": \"raw\",\n", " }\n", "\n", " # Create a table for this volume. For the PO directories I'm using the year as the table name.\n", " # If year isn't available, some other way of naming the table would be necessary, such as the full title.\n", "\n", " vol_table = db[year]\n", "\n", " # Loop through all the text page by page for this volume\n", " pages = natsorted(\n", " [p for p in Path(vol, \"tesseract\").glob(\"*.txt\") if p.is_file()], alg=ns.PATH\n", " )\n", " for page in pages:\n", " lines = []\n", " # text = page.read_text()\n", " # Insert details about this page into the pages table\n", " page_num = int(re.search(r\"PDF-(\\d+).txt\", page.name).group(1))\n", " page_table.insert(\n", " {\"page_id\": page.stem, \"page\": page_num, \"vol_id\": obj_id},\n", " pk=(\"page_id\"),\n", " foreign_keys=[(\"vol_id\", \"volumes\")],\n", " )\n", "\n", " # Open the text file and loop through the lines\n", " with page.open(\"r\") as txt:\n", " line_num = 1\n", " for line in txt:\n", " # Get rid of blank lines\n", " line = line.replace(\"\\n\", \"\").strip()\n", " # If line is not blank, add details to a list of lines from this page\n", " if line:\n", " lines.append(\n", " {\n", " \"page\": page_num,\n", " \"line\": line_num,\n", " \"text\": line,\n", " \"page_id\": page.stem,\n", " }\n", " )\n", " line_num += 1\n", " # Insert all the lines from this page into the db\n", " vol_table.insert_all(\n", " lines, pk=(\"page\", \"line\"), foreign_keys=[(\"page_id\", \"pages\", \"page_id\")]\n", " )\n", "\n", " # Add a full text index on the line text\n", " vol_table.enable_fts([\"text\"])\n", " # vol_table.optimize()\n", "\n", "# Save the updated metadata file\n", "with open(Path(db_path, \"datasette-metadata.json\"), \"w\") as md_file:\n", " json.dump(metadata, md_file, indent=4)" ] }, { "cell_type": "markdown", "id": "b5a87623-226f-42f2-84c8-0ee66fef8980", "metadata": {}, "source": [ "You should now have an SQLite database containing the indexed text of all the volumes in the collection. You can explore the database using Datasette. In a Python environment you'll need to install:\n", "\n", "* [Datasette](https://datasette.io/)\n", "* [datasette-search-all](https://github.com/simonw/datasette-search-all)\n", "* [datasette-template-sql](https://datasette.io/plugins/datasette-template-sql)\n", "\n", "Then, from within the directory containing the database, run:\n", "\n", "```shell\n", "datasette tasmanian-post-office-directories.db -m metadata.json\n", "```\n", "\n", "To share your database publicly, look at [Datasette's publishing options](https://docs.datasette.io/en/stable/publish.html)." ] }, { "cell_type": "markdown", "id": "7efc8282-d935-425a-a373-430f46815f46", "metadata": {}, "source": [ "----\n", "Created by [Tim Sherratt](https://timsherratt.org/) for the [GLAM Workbench](https://glam-workbench.net/) as part of the [Everyday Heritage](https://everydayheritage.au/) project." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3.9.9 64-bit", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.9" }, "vscode": { "interpreter": { "hash": "e7370f93d1d0cde622a1f8e1c04877d8463912d04d973331ad4851f04de6915a" } }, "widgets": { "application/vnd.jupyter.widget-state+json": { "state": {}, "version_major": 2, "version_minor": 0 } } }, "nbformat": 4, "nbformat_minor": 5 }