Tagged: datasette

Quick Geo Queries Using Datasette – Fact Checking a Conversation About Local Building Works on the Isle of Wight

A few weeks ago, chatting with neighbours, comment was made about some earthworks appearing along the roadside a couple of miles away, out towards an area that has been licensed for fracking exploration. Concerns were raised as to whether this new earth embankment might be associated with that.

It is possible to run queries over historical planning applications on the Isle of Wight council website but the results are provided in tabular form:

However, I’ve also been running a scraper over Isle of Wight planning applications for some time, with the data stored in a simple SQLite database. A couple of the columns give latitude and longitude information associated with each application (oftentimes, quite crudely…), and multiple other columns are also available that can be queried over:

It only took a single command line command to fire up a datasette server that meant I could look for planning applications made in recent times in the area:

datasette data.sqlite

A simple query turns up applications in the appropriate parish – the map is automatically created by the datasette server when latitude and longitude columns are returned from a query:

Zooming in gives a possible candidate for the application behind the recent works- a better choice of selected columns would give a more useful tooltip:

A slightly refined query turns up the application in more detail:

And from there it’s easy enough to go to the application  – the one with ID 32578:

Handy…

See also this previous encounter with IW planning applications: All I Did Was Take the Dog Out For a Walk….

PS And also see also: Running Spatial Queries on Food Standards Agency Data Using Ordnance Survey Shapefiles in SpatiaLite via Datasette.

PPS Here’s a handy side effect of running the datasette command from inside a Jupyter notebook – a copy of the query log.

iwplanning

PPS the scraper actually feeds a WordPress plugin I started trying to develop that displays currently open applications in a standing test blog page. I really should tidy that plugin code up and blog it one day…

Working With OpenStreetMap Roads Data Using osmnx

A couple of days ago, I came across the incredible looking osmnx Python package, originally created by Geoff Boeing at UC Berkeley in support of his PhD, via one of his blog posts: OSMnx: Python for Street Networks (there is a citeable paper, but that’s not what I originally found…) There are also some great example notebooks: gboeing/osmnx-examples.

I spent a chunk of today having a play with is, and have posted a notebook walkthrough here.

It’s quite incredible…

Pretty much a one-liner lets you pull back all the roads in a particular area, searched for by name:

The osmnx package represents routes as a networkx graph – so we can do graphy things with it, like finding the shortest distance between two points, aka route planning:

The route can also be plotted on an interactive map. An option lets you hover on a route and display a context sensitive tooltip, in this case the name of the road:

Retrieving routes by area name is handy, but we can also pull back routes within a certain distance of a specified location, or routes that are contained within a particular region specified by a shapefile.

In a previous post (Trying Out Spatialite Support in Datasette) I showed how to put Ordnance Survey BoundaryLine data into a SpatiaLite database and then access the geojson boundary files from a datasette API. We can use that approach again, here wrapped up in a dockerised context:

Using the retrieved boundary shapefile, we can then use osmnx to just grab the roads contained within that region, in this case my local parish:

Once again, we can use an interactive map to display the results:

If we overlay the parish boundary, we see that the routes returned correspond to the graph between nodes that lay within the boundary. Some roads pass straight through the boundary, others appear to lay just outside the boundary.osmnx_Demo11

However, it looks like we can tweak various parameters to get the full extent of the roads within the region:

osmnx_Demo12

As well as routes, we can also get building footprints from OpenStreetMap:

If you know where to look, you can see our house!

Building footprints can also be overlaid on routes:

If we generate a distance to time mapping, the graph representation means we can also colouring nodes according to how far in walking time, for example, they are from a particular location:

We can also overlay routes on isochrone areas to show travel times along routes – so where’s within half an hour’s walk of the Pointer Inn in Newchurch?

Living on a holiday island, I wonder if there’s any mileage (!) in offering a simple service for campsites, hotels etc that would let them print off isochrone walking maps centred on the campsite, hotel etc with various points of interest, and estimated walking times, highlighted?

I’m also wondering how much work would be required in order to add additional support to the osmnx package so that it could use Ordnance Survey, rather than OSM, data?

And finally, one other thing I’d like to explore is how to generate tulip diagrams from route graphs… If anyone has any ideas about that, please let me know via the comments…

PS for building outlines in the UK derived from Ordnance Survey data, see for example Alasdair Rae’s OS OpenMap Local – All Buildings in Great Britain.

PPS And building footprints for the US, courtesy of Microsoft: https://github.com/Microsoft/USBuildingFootprints

Trying Out Spatialite Support in Datasette

A recent datasette release included improved support for the SpatiaLite geo-extensions for SQLite, so to complement some other bits of geo-tinkering I’ve been doing lately – and which I still need to blog – I thought I’d give it a spin.

The write up is in a Jupyter notebook which you can find here and which is embedded in markdown export format below:

SpatiaLite Datasette Demo

This notebook provides a quick walkthrough of getting started with a SpatiaLite geo-database and using it with Datasette.

Get the Data

The SpatiaLite database can be used to store, index and perform various geo-related query operations on various geographical objects including points and shapefiles.

To help me get up to speed, I’m going to try to load in a shapefile delimiting various bits of the UK into a SpatiaLite database, publish it as a datasette, retrieve a boundary for a particular region from the datasette API and then plot the boundary on a map.

The shapefile I’m going to use is of UK administrative areas described by the Ordnance Survey Boundary-Line product.

You can get a copy of the data from https://www.ordnancesurvey.co.uk/opendatadownload/products.html by selecting the Boundary-Line product and providing contact details, at which point you should be emailed a download link.

Download the data and unzip it. This should create a folder named bdline_essh_gb, or something similar. (The paths used in this notebook assumes that it is running inside that directory.)

Inside the bdline_essh_gb ditectory is a Data subdirectory, and inside that is a GB subdirectory containing a variety of unpacked shapefiules, including bit not limited to:

district_borough_unitary_region.dbf parish_region.shp
district_borough_unitary_region.prj parish_region.shx
district_borough_unitary_region.shp scotland_and_wales_const_region.dbf
district_borough_unitary_region.shx scotland_and_wales_const_region.prj
district_borough_unitary_ward_region.cpg scotland_and_wales_const_region.shp
district_borough_unitary_ward_region.dbf scotland_and_wales_const_region.shx
district_borough_unitary_ward_region.prj scotland_and_wales_region.dbf
district_borough_unitary_ward_region.sbn scotland_and_wales_region.prj
district_borough_unitary_ward_region.sbx scotland_and_wales_region.shp
district_borough_unitary_ward_region.shp scotland_and_wales_region.shx
district_borough_unitary_ward_region.shx

Loading Shapefile Data into SQLite

The datasette docs currently suggest creating a SpatiaLite database using a command of the form spatialite DATABASE.db and then loading shapefiles into it using a SpatiaLite commandline command of the form: .loadshp SHAPEFILE TABLENAME CP1252 23032.

That bit of voodoo actually unpacks a bit further as:

.loadshp PATH_AND_SHAPEFILE_NAME TABLENAME FILE_ENCODING [PROJECTION]

The CP1252 file encoding is for the default Microsoft Windows Latin-1 encoding, so I’m guessing that if your shapefile use another encoding you need to change that. (Internally, SpatiaLite uses UTF-8. UTF-8 is also acceptable as a file encoding value in the above commandline command; a full list of acceptable values can be found by trying to load a shapefile using spatialite_gui.) The file encoding is a required argument, as are the path to the shapefile name and the table name. The projection is optional.

The projection relates to the projection used within the shapefile. Setting this correctly allows you to transform the data to other projections, such as the WGS84 (aka EPSG:4326, GPS, latitude / longitude) projection.

The projection is identified using its SRID (Spatial Reference System Identifier) code (lookup). If no code is provided, no projection is identified with the shapefile geodata in the database (it’s give a -1 code). (I had expected the projection to be identified from the .prj (projection) file which contains a WKT description of the projection used in each .shp shapefile.)

I didn’t meet with much success looking for a Python library to identify the required SRID from the Ordnance Survey shapefiles, but did find an online API that seemed to do the trick:

#Load in the shapefile projection details
with open('./Data/GB/district_borough_unitary_region.prj', 'r') as myfile:
  prj=myfile.read()
prj

‘PROJCS[“unnamed”,GEOGCS[“unnamed”,DATUM[“D_OSGB_1936”,SPHEROID[“Airy – 1848”,6377563,299.319997677743]],PRIMEM[“Greenwich”,0],UNIT[“degree”,0.0174532925199433]],PROJECTION[“Transverse_Mercator”],PARAMETER[“latitude_of_origin”,49],PARAMETER[“central_meridian”,-2],PARAMETER[“scale_factor”,0.999601272],PARAMETER[“false_easting”,400000],PARAMETER[“false_northing”,-100000],UNIT[“METER”,1]]’

#Look up the correpsonding SRID
import requests

#http://prj2epsg.org/apidocs.html
params = {'mode': 'wkt', 'terms':prj}
r = requests.get('http://prj2epsg.org/search.json', params=params)

#Use the first hit as the most likely SRID
srid = r.json()['codes'][0]
srid
{'name': 'OSGB 1936 / British National Grid',
'code': '27700',
'url': 'http://prj2epsg.org/epsg/27700.json' }

This tells us that the co-ordinates used in the shapefile as given as OSGB Notrhings and Eastings.

Use this code value (27700) to load our shapefile in with the correct projection code:

# Load the data into a new SpatiaLite database
! spatialite adminboundaries.db ".loadshp ./Data/GB/district_borough_unitary_region adminboundaries UTF-8 27700"
SpatiaLite version ..: 4.3.0a Supported Extensions:
- 'VirtualShape' [direct Shapefile access]
- 'VirtualDbf' [direct DBF access]
- 'VirtualXL' [direct XLS access]
- 'VirtualText' [direct CSV/TXT access]
- 'VirtualNetwork' [Dijkstra shortest path]
- 'RTree' [Spatial Index - R*Tree]
- 'MbrCache' [Spatial Index - MBR cache]
- 'VirtualSpatialIndex' [R*Tree metahandler]
- 'VirtualElementary' [ElemGeoms metahandler]
- 'VirtualXPath' [XML Path Language - XPath]
- 'VirtualFDO' [FDO-OGR interoperability]
- 'VirtualGPKG' [OGC GeoPackage interoperability]
- 'VirtualBBox' [BoundingBox tables]
- 'SpatiaLite' [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 5.1.0, June 1st, 2018
GEOS version ........: 3.6.2-CAPI-1.10.2 4d2925d6
TARGET CPU ..........: x86_64-apple-darwin17.3.0
the SPATIAL_REF_SYS table already contains some row(s)
========
Loading shapefile at './Data/GB/district_borough_unitary_region' into SQLite table 'adminboundaries'

BEGIN;
CREATE TABLE "adminboundaries" (
"PK_UID" INTEGER PRIMARY KEY AUTOINCREMENT,
"NAME" TEXT,
"AREA_CODE" TEXT,
"DESCRIPTIO" TEXT,
"FILE_NAME" TEXT,
"NUMBER" INTEGER,
"NUMBER0" INTEGER,
"POLYGON_ID" INTEGER,
"UNIT_ID" INTEGER,
"CODE" TEXT,
"HECTARES" DOUBLE,
"AREA" DOUBLE,
"TYPE_CODE" TEXT,
"DESCRIPT0" TEXT,
"TYPE_COD0" TEXT,
"DESCRIPT1" TEXT);
SELECT AddGeometryColumn('adminboundaries', 'Geometry', 27700, 'MULTIPOLYGON', 'XY');
COMMIT;

Inserted 380 rows into 'adminboundaries' from SHAPEFILE
========

The Northings/Eastings projection used by the OS shapefile is not directly plottable using many simple interactive web maps. Instead, they need GPS style latitude/longitude co-ordinates. Given that we know the projection of the original shapefile, we can create a new set of transformed co-ordinates using the required lat/long (WGS84) projection.

Let’s create a simple text file containing a SQL script to handle that transformation for us:

%%bash
echo '''
BEGIN;
ALTER TABLE adminboundaries ADD COLUMN wgs84 BLOB;
UPDATE adminboundaries SET wgs84 = Transform(Geometry, 4326);
COMMIT;
''' > project2wsg84.sql
! cat project2wsg84.sql
BEGIN;
ALTER TABLE adminboundaries ADD COLUMN wgs84 BLOB;
UPDATE adminboundaries SET wgs84 = Transform(Geometry, 4326);
COMMIT;

We can now read and execute that script against our database (once again, the file encoding appears to be required?)

! spatialite adminboundaries.db ".read project2wsg84.sql utf-8"
SpatiaLite version ..: 4.3.0a Supported Extensions:
- 'VirtualShape' [direct Shapefile access]
- 'VirtualDbf' [direct DBF access]
- 'VirtualXL' [direct XLS access]
- 'VirtualText' [direct CSV/TXT access]
- 'VirtualNetwork' [Dijkstra shortest path]
- 'RTree' [Spatial Index - R*Tree]
- 'MbrCache' [Spatial Index - MBR cache]
- 'VirtualSpatialIndex' [R*Tree metahandler]
- 'VirtualElementary' [ElemGeoms metahandler]
- 'VirtualXPath' [XML Path Language - XPath]
- 'VirtualFDO' [FDO-OGR interoperability]
- 'VirtualGPKG' [OGC GeoPackage interoperability]
- 'VirtualBBox' [BoundingBox tables]
- 'SpatiaLite' [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 5.1.0, June 1st, 2018
GEOS version ........: 3.6.2-CAPI-1.10.2 4d2925d6
TARGET CPU ..........: x86_64-apple-darwin17.3.0

Accessing the SpatiaLite database Using Datasette

Having created our database and generated a projection approropriate for plotting using an interactive web map, let’s publish it as a datasette and then see if we can access the data from the datasette API.

Here’s a trick I just learned via a tweet from @minrk for running a simple web service from within a Jupyter notebook code cell without blocking the notebook execution:

# Example of running datasette server from inside a code cell
# via https://nbviewer.jupyter.org/gist/minrk/622080cf8af787734805d12bec4ae302
from threading import Thread

def app_in_thread():
    ! datasette adminboundaries.db --load-extension=/usr/local/lib/mod_spatialite.dylib --config sql_time_limit_ms:10000 --cors

t = Thread(target=app_in_thread)
t.start()

# Alternative multitasking package
# https://github.com/micahscopes/nbmultitask
Serve! files=('adminboundaries.db',) on port 8001
[2018-06-28 18:08:04 +0100] [91760] [INFO] Goin' Fast @ http://127.0.0.1:8001
[2018-06-28 18:08:04 +0100] [91760] [INFO] Starting worker [91760]

We should now be able to access the datasette API.

#What region shall we search for a boundary for?
#How about the diamond isle just off the south coast...
region='wight'
import requests

params = {'sql': 'SELECT AsGeoJSON(wgs84) FROM adminboundaries WHERE name LIKE "%{}%"'.format(region)}
json_url = "http://localhost:8001/adminboundaries.json"

r = requests.get(json_url, params=params)
results = r.json()

[2018-06-28 18:30:29 +0100] – (sanic.access)[INFO][1:2]: GET http://localhost:8001/adminboundaries-f771041.json?sql=SELECT+AsGeoJSON%28wgs84%29+FROM+adminboundaries+WHERE+name+LIKE+%22%25wight%25%22 302 0
[2018-06-28 18:30:29 +0100] – (sanic.access)[INFO][1:2]: GET http://localhost:8001/adminboundaries-a912e25.json?sql=SELECT+AsGeoJSON%28wgs84%29+FROM+adminboundaries+WHERE+name+LIKE+%22%25wight%25%22 200 398206

# Get geojson feed served from datasette - the result is actually a string
# so convert the string to actual json, qua py dict
import json
geojson=json.loads(results['rows'][0][0])

Now let’s see if we can render that region as a map.

#folium is a handy package I've used for ages for displaying maps in Jupyer notebooks
import folium
# Get example point from geojson to help center the map
lat,lng = tuple(geojson['coordinates'][0][0][0])
#Render the map
m = folium.Map(location=(lng,lat), zoom_start=10)
m.choropleth(geo_data=geojson, line_color='blue',line_weight=3)
m

So that all seems to work… :-)

Next up, I’ll see if I can load some lat/lng points, cast them as Point datatypes, then run a query to pull out just locations within a specific region…

Seven Ways of Making Use of SQLite

SQLite is a really handy file based database engine. When you put data into a database, it can make it easier to search; it also provides a great tool for looking for stories or story leads hidden in the data. So here are seven ways of getting started with SQLite.

Querying SQLite Databases Using Rich User Interfaces

Whilst SQLite comes with it’s own command line client, many people will find the easiest way of getting started with querying SQLite databases is to use an application, either on the desktop or accessed via a browser based UI.

franchise is a browser based UI that you can access via the cloud or run locally (

(https://github.com/hvf/franchise)). If you have a SQLite database file (make sure the suffix is .sql) you can upload it and explore it using the franchise application. (If you have a CSV or Excel data file, you can upload that too and it will add it to its own temporary SQLite database). Here’s a review: Asking Questions of CSV Data, Using SQL In the Browser, With Franchise.

If you prefer something on the desktop, the cross-platform sqlitebrowser might suit your needs.

Another great way of making use of SQLite is bring it alive using datasette. A single command allows you to publish an interactive, browser based user interface to one or more databases, either on your own computer or via an online host such as Zeit Now, or Heroku. For example, I’ve popped up three databases I scrape together on Heroku and pointed my own URL at them (unfortunately, I tend to run out of free Heroku credits in the last week of the month at which point the site goes down!)

datasette allows you to query the databases through a customisable webpage and supports a range of plugins. For example, the datasette-cluster-map will detect latitude and longitude columns in a datatable and present the results using an interactive map. I gave it a spin with this map of UK Food Hygiene Ratings. You can find several other examples of datasettes published in the wild on the datasette wiki.

Finding Data: SQLite Databases in the Wild

Whilst tools such as datasette are great for quickly getting started with querying a SQLite database, one obvious question that arises is: what SQLite database?

Once you start poking around, however, you can start to find examples of SQLite databases working quietly behind the scenes on you own computer. (Searching your computer for files with a .sqlite suffix is one way of finding them!)

As a specific example, the Safari, Chrome and Firefox web browsers all keep track of your browser history using a SQLite database on your computer (this gist – dropmeaword/browser_history.md – tells you where you can find the files. You can then simply query them using datasette. On a Mac, I can simply run:

datasette ~/Library/Application\ Support/Google/Chrome/Default/History

and I can then start to query my browser history using the datasette browser based UI. Here’s an ‘inspect your browser history’ tutorial to get you started…

Ingesting Data

Applications such as franchise allow you to upload a CSV or Excel datafile and automatically import it into a SQLite database so that it can be queried using SQLite. The datasette ecosystem also includes and application for uploading CSV files and ingesting them into a SQlite database: datasette publish.

Behind the scenes of that application is a Python command line utility called csvs-to-sqlite. A simple command lets yoiu convert a CSV file to a SQLite database:

csvs-to-sqlite myfile.csv mydatabase.db

Whilst csvs-to-sqlite focusses on the conversion of CSV files into a SQLite database, the more general sqlitebiter command line utility can convert CSV, Excel, HTML tables (eg from a URL), JSON, Jupyter Notebooks, Markdown, TSV and Google-Sheets to a SQLite database file using a similar command format.

Using SQLite from the Commandline

Natively, SQLite comes with its own command line shell that allows you to connect to and query a SQLite database from the command line.

As well as command line tools for converting data contained in various file formats into a SQLite database, several command line tools embed that conversion within a command line interface that allows you convert a data file to an in-memory SQLite database and query it directly.

For example, using the csvsql command from csvkit:

csvsql --query "select * from iris as i join irismeta as m on (i.species = m.species)" examples/iris.csv examples/irismeta.csv

Or textql:

Or the simply named q:

q -H "SELECT COUNT(DISTINCT(uuid)) FROM ./clicks.csv"

Querying SQLite Databases from Programming Languages Such as Python and R

If you are developing your own data-centric reproducible research pipelines, it’s likely that you will be using a programming language such as R or the Python pandas library.

There are two main ways for using SQLite in this context. The first is to connect to the database from the programming language and then query it from within the language. For example, in R, you might use the RSQLite package. In Python, you can connect to a SQLite database using the base sqlite3 package.

The other approach is to use SQLite as an in-memory database that provides a SQL query interface to a dataframe. In R, this can be achieved using the sqldf package:

library(sqldf)
sqldf("select * from iris limit 5")

In Python/pandas, you can use the pandasql package:

from pandasql import sqldf, load_births
pysqldf = lambda q: sqldf(q, globals())
births = load_births()
print(pysqldf("SELECT * FROM births LIMIT 10;").head())

In many respects, sqldf and pandasql behave like programming language native versions of command-line utilities such as csvsql, textql and q, although rather than importing a data file into a SQLite database so that it can be queried, they import the contents of the referenced dataframe.

pandas also provides native support for adding dataframes as tables to a connected SQLite database, as well as reading results from queries onto the database back into a dataframe. Once you start getting into the swing of putting data into a database, and then running JOINed queries over multiple tables, you’ll start to wonder why you spent so much time getting hassled by VLOOKUP. As an example, here’s a way of Making a Simple Database to Act as a Lookup for the ONS Register of Geographic Codes.

Using SQLite in Your Own Applications

If you are comfortable with using programming code to manipulate your data, then you may want to explore ways of using SQLite to create your own data driven applications.

One way yo start is to use SQLite completely within the browser. Accessing desktop applications from a webpage is typically a no-no because of browser security restrictions, but SQLite is quite a light application, so it can – and has been – compiled to Javascript so that it can be imported as a Javascript library and run from within a webpage: sql.js. You can see an example of how it can be used to provide a simple browser based, SQLite powered data explorer, running solely within a browser here: official demo or SQLite Viewer.

As well as running SQLite in a browser, SQLite can also be used to power an API. One of the advantages of running a datasette service is that it also exposes a datasette API. This means you can publish your datasette to a web host then other computers can querying it programmatically.

If you are working in a Python Jupyter environment, it’s simple enough to use the Jupyer kernel gateway to create your own APIs. Here’s an example of building a service to allow the lookup of ONS codes from a simple SQLite database: Building a JSON API Using Jupyter Notebooks in Under 5 Minutes.

Another way of using SQLite databases in a Jupyter environment is to use Scripted Forms to For example, here’s one of my own recipes for Creating Simple Interactive Forms Using Python + Markdown Using ScriptedForms + Jupyter that shows how to create a simple interactive form for querying a SQLite database containing descriptions of images used in OpenLearn courses.

SQLite Database Admin Tools

As well as providing a simple explorer and query interface, the sqlitebrowser tool also supports a range of other SQLite database administration functions such as the ability to create, define, modify and delete tables and indexes, or edit, add and delete individual records.

The browser based sqlite-web application provides a similar range of utulities via a browser based, rather than desktop client, UI.

Summary

SQLite is lightweight, in-memory and file based database that allows you to run SQL queries over a wide range of tabular datasets. If you work with data, knowing how to write even simple SQL queries can add a powerful tool to your toolbox. SQLite, and the associated tools created around it, is almost certainly one of the easiest ways in to using this most versatile, portable, and personal data power tool.

PS Here are some more ways of using SQLite:

Datasette ClusterMap Plugin – Querying UK Food Standards Agency (FSA) Food Hygiene Ratings Open Data

Earlier today, Simon Willison announced a new feature of in the datasette landscape: plugins. I’ve not had a chance to explore how to go about creating them, but I did have a quick play with the first of them – datasette-cluster-map.

This looks through the columns of a query results table and displays each row that contains latitude an longitude columns as a marker on an interactive leaflet map. Nearby markers are clustered toegther.

So I thought I’d have a play… Assuming you have python installed, on the command line tun the following (omit the comments). The data I’m going to use fo the demo is UK Food Standards Agency (FSA) food hygiene ratings opendata.

# Install the base datasette package
pip install datasette

# Install the cluster map plugin
pip install datasette-cluster-map 

# Get some data - I'm going to grab some Food Standards Agency ratings data
# Install ouseful-datasupply/food_gov_uk downloader
pip install https://github.com/ouseful-datasupply/food_gov_uk.git
# Grab the data into default sqlite db - fsa_ratings_all.db
oi_fsa collect

# Launch datasette
datasette serve fsa_ratings_all.db

This gives me a local URL to view the datasette UI on in a browser (but it didn’t open the page automatically into the browser?). For example: http://127.0.0.1:8001

By default we see a listing of all the tables in the database:

I want the ratings_table:

We can use selectors to query into the table, but I want to write some custom queries and see the maps:

So – what can we look for? How about a peek around Newport on the Isle of Wight, which has an ish postcode of PO30

Looking closely, there doesn’t seem to be any food rated establishments around the Isle of Wight Prisons?

This is a bit odd, because I’ve previously used FSA data as a proxy for trying to find prison locations. Here’s a crude example of that:

So… all good fun, and no coding really required, just glueing stuff together.

Here are some more example queries:

# Search around a postcode district
SELECT BusinessName, BusinessType, RatingValue, AddressLine1, AddressLine2 , AddressLine3, Latitude, Longitude FROM ratingstable WHERE postcode LIKE 'PO30%'


# Crude search for prisons
SELECT BusinessName, BusinessType, RatingValue, AddressLine1, AddressLine2 , AddressLine3, Latitude, Longitude FROM ratingstable WHERE (AddressLine1 LIKE '%prison%' COLLATE NOCASE OR BusinessName LIKE '%prison%' COLLATE NOCASE OR AddressLine1 LIKE '%HMP%' COLLATE NOCASE OR BusinessName LIKE '%HMP%' COLLATE NOCASE)

# Can we find Amazon distribuion centres?
SELECT BusinessName, BusinessType, RatingValue, AddressLine1, AddressLine2 , AddressLine3, Latitude, Longitude FROM ratingstable WHERE AddressLine1 LIKE 'Amazon%' OR BusinessName LIKE 'Amazon%'

# How about distribution centres in general?
SELECT BusinessName, BusinessType, RatingValue, AddressLine1, AddressLine2 , AddressLine3, Latitude, Longitude FROM ratingstable WHERE AddressLine1 like '%distribution%' COLLATE NOCASE  or BusinessName LIKE '%distribution%' COLLATE NOCASE

# Another handy tick is to get your eye in to queryable things by looking at where
#  contract catering businesses ply their trade. For example, Compass
SELECT BusinessName, BusinessType, RatingValue, AddressLine1, AddressLine2 , AddressLine3, Latitude, Longitude FROM ratingstable WHERE AddressLine1 LIKE '%compass%' COLLATE NOCASE  OR BusinessName LIKE '%compass%' COLLATE NOCASE

One thing I could do is share the database via an online UI. datasette makes this easy – datasette publish heroku fsa_ratings_all.db` would do the trick if you have Heroku credentials handy, but I’ve already trashed my monthly Heroku credits…