Drawing shapes on a map to query a SpatiaLite database (and other weeknotes)
This week I built a Datasette plugin that lets you query a database by drawing shapes on a map!
SpatiaLite is a SQLite extension that adds a plethora of geospatial querying features. Datasette works well with SpatiaLite already, but every now and then I dip in to a geospatial project to see if there are any neat ways I can extend Datasette’s spatial querying support.
:freedraw and enhances them with an interactive map. You can then draw on the map to populate the form field with a GeoJSON MultiPolygon representing the shape that you drew. Submit that to the server to excute it as part of a query.
This is best illustrated with a demo. Here’s a SQL query that let you draw a shape to find California protected areas (parks, nature reserves and suchlike) that intersect with the polygon that you draw:
select AsGeoJSON(geometry), * from CPAD_2020a_SuperUnits where Intersects(GeomFromGeoJSON(:freedraw), geometry) = 1 and CPAD_2020a_SuperUnits.rowid in ( select rowid from SpatialIndex where f_table_name = 'CPAD_2020a_SuperUnits' and search_frame = GeomFromGeoJSON(:freedraw) )
from SpatialIndex sub-select at the end is a pattern for taking advantage of SpatiaLite spatial indexes.)
The plugin itself is pretty simple: it scans for
freedraw input fields and enhances them with a map interface that writes GeoJSON back to the form field.
You can then use SpatiaLite functions such as
GeomFromGeoJSON() to transform that user input into something that can be used in a query.
Another fun query: let’s add
WHERE PARK_NAME like '%mini%' to the query to filter for just the “mini parks” in San Francisco:
select AsGeoJSON(geometry), * from CPAD_2020a_SuperUnits where PARK_NAME like '%mini%' and Intersects(GeomFromGeoJSON(:freedraw), geometry) = 1 and CPAD_2020a_SuperUnits.rowid in ( select rowid from SpatialIndex where f_table_name = 'CPAD_2020a_SuperUnits' and search_frame = GeomFromGeoJSON(:freedraw) )
This demo uses the user’s shapes as part of a
SELECT query, but it’s also possible to use Datasette’s Writable canned queries to create forms that will save the shapes to a database using an
If you want to try this plugin out note that it currently requires the Datasette 0.54a alpha. This is because it takes advantage of the new
<script type="module"> support I added last week. Update 25th January 2021: Datasette 0.54 has now been released.
Other releases this week
I recorded my talk for FOSDEM—and in doing so I broke my unproductive streak and pushed out releases for a whole bunch of Datasette projects—partly through fixing bugs I spotted while trying to record the talk!
Here’s the full list, with some annotations.
Datasette plugin that authenticates users against GitHub. This is a big release: I finally finished upgrading the plugin to work against Datasette’s new-ish authentication and permissions mechanism.
datasette-leaflet-freedraw: 0.1.4— 2021-01-24
Draw polygons on maps in Datasette. See above.
Datasette plugin that shows a map for any data with latitude/longitude columns. Fixed some bugs related to the display of marker popups that included foreign key references to other tables.
ASGI middleware for protecting against CSRF attacks. I fixed a broken test caused by my use of an undocumented and unstable API in
httpx—details here. I also added a new
always_set_cookie=Trueoption for cases where you always want to ensure a CSRF cookie has been set, see #16.
Datasette plugin that replaces any GeoJSON column values with a Leaflet map. I fixed a long-standing point of concern: this plugin renders a map for every row on a page containing GeoJSON, which can potentially mean hundreds of maps for queries that return may results. The plugin now renders just the first ten maps and provides a grey clickable “Click to show map” widget for anything past the first ten.
Tool for running transformations on columns in a SQLite database. I improved the
Create a SQLite database containing your checkin history from Foursquare Swarm. I fixed a bug caused by new fields showing up in the Swarm JSON output.
Python CLI utility and library for manipulating SQLite databases. I added the
alter=Trueargument to the
.m2m()method, which I needed to fix the bug in