Drawing shapes on a map to query a SpatiaLite database (and other weeknotes)

24th January 2021

This week I built a Datasette plugin that lets you query a database by drawing shapes on a map!

datasette-leaflet-freedraw

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.

datasette-leaflet-freedraw is my new plugin which brings the excellent FreeDraw Leaflet module to Datasette.

It’s a pure-JavaScript plugin which looks for Datasette SQL parameters called :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.

A screenshot showing the plugin with a shape drawn around San Francisco

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)
  )

This uses the California Protected Areas Database by GreenInfo Network, discussed here previously.

Try the SQL query here.

(That 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)
  )

Here are the mini parks for San Francisco and for Oakland.

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 INSERT or UPDATE query.

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-auth-github: 0.13—2021-01-24
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-cluster-map: 0.16—2021-01-23
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-csrf: 0.8—2021-01-23
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=True option for cases where you always want to ensure a CSRF cookie has been set, see #16.
datasette-leaflet-geojson: 0.7—2021-01-20
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.
datasette: 0.54a0—2021-01-19
Lots of stuff in this one, see the release notes. I pushed out an alpha so I could start using the JavaScript modules stuff in datasette-leaflet-freedraw.
sqlite-transform: 0.3.1—2021-01-18
Tool for running transformations on columns in a SQLite database. I improved the --help.
swarm-to-sqlite: 0.3.3—2021-01-18
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.
sqlite-utils: 3.3—2021-01-18
Python CLI utility and library for manipulating SQLite databases. I added the alter=True argument to the .m2m() method, which I needed to fix the bug in swarm-to-sqlite.

TIL this week

Posted 24th January 2021 at 6:50 am · Follow me on Mastodon or Twitter or subscribe to my newsletter

Simon Willison’s Weblog