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:

(If you have the datasette-cluster-map plugin installed, you get the map for free given the existence of the latitude/longitude columns.)

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.

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…