KNN queries with SpatiaLite#

The latest version of SpatiaLite adds KNN support, which makes it easy to efficiently answer the question “what are the X closest records to this point”.

The USGS earthquakes GeoJSON is a great dataset for experimenting with these features.

Documentation for that is here: https://earthquake.usgs.gov/earthquakes/feed/v1.0/geojson.php

https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_month.geojson currently contains 10,642 features.

To turn that into a SpatiaLite database using the latest version of geojson-to-sqlite:

1
curl 'https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_month.geojson' | \
2
  geojson-to-sqlite usgs.db quakes - --spatial-index

This will create a usgs.db SpatiaLite database with a spatial index.

This should have a knn virtual table already. If one does not exist you can create one like this:

1
sqlite-utils --load-extension=spatialite usgs.db 'CREATE VIRTUAL TABLE knn USING VirtualKNN();'

Open it in Datasette like this:

1
datasette --load-extension=spatialite usgs.db

(Running datasette install datasette-cluster-map first will let you see them on a map.)

Use this SQL query to run KNN searches returning the ten closest earthquakes to a point:

1
SELECT
2
  knn.distance,
3
  quakes.title,
4
  quakes.mag,
5
  quakes.time,
6
  quakes.url,
7
  y(quakes.geometry) as latitude,
8
  x(quakes.geometry) as longitude
9
FROM
10
  knn
11
  join quakes on knn.fid = quakes.rowid
12
WHERE
13
  f_table_name = 'quakes'
14
  AND ref_geometry = MakePoint(cast(:longitude as real), cast(:latitude as real))
15
  AND max_items = 10