Open simonw opened 3 years ago
I have a better recipe for using spatial indexes now on https://simonwillison.net/2021/Jan/24/drawing-shapes-spatialite/
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 will give you back an additional column of GeoJSON. You can copy and paste GeoJSON from this column into the debugging tool at geojson.io to visualize it on a map.
That should promote datasette-leaflet-geojson
instead.
That rivers example in the tutorial would work a lot better with a live demo.
Useful tutorial series from 2012: https://northredoubt.com/n/2012/01/20/spatialite-speed-test/
https://docs.datasette.io/en/0.55/spatialite.html was written before I had tools like geojson-to-sqlite and shapefile-to-sqlite.