sections: spatialite:making-use-of-a-spatial-index
This data as json
| id | page | ref | title | content | breadcrumbs | references |
|---|---|---|---|---|---|---|
| spatialite:making-use-of-a-spatial-index | spatialite | making-use-of-a-spatial-index | Making use of a spatial index | SpatiaLite spatial indexes are R*Trees. They allow you to run efficient bounding box queries using a sub-select, with a similar pattern to that used for Searches using custom SQL . In the above example, the resulting index will be called idx_museums_point_geom . This takes the form of a SQLite virtual table. You can inspect its contents using the following query: select * from idx_museums_point_geom limit 10; Here's a live example: timezones-api.datasette.io/timezones/idx_timezones_Geometry pkid xmin xmax ymin ymax 1 -8.601725578308105 -2.4930307865142822 4.162120819091797 10.74019718170166 2 -3.2607860565185547 1.27329421043396 4.539252281188965 11.174856185913086 3 32.997581481933594 47.98238754272461 3.3974475860595703 14.894054412841797 4 -8.66890811920166 11.997337341308594 18.9681453704834 37.296207427978516 5 36.43336486816406 43.300174713134766 12.354820251464844 18.070993423461914 You can now construct efficient bounding box queries that will make use of the index like this: select * from museums where museums.rowid in ( SELECT pkid FROM idx_museums_point_geom -- left-hand-edge of point > left-hand-edge of bbox (minx) where xmin > :bbox_minx -- right-hand-edge of point < right-hand-edge of bbox (maxx) and xmax < :bbox_maxx -- bottom-edge of point > bottom-edge of bbox (miny) and ymin > :bbox_miny -- top-edge of point < top-edge of bbox (maxy) and ymax < :bbox_maxy ); Spatial indexes can be created against polygon columns as well as point columns, in which case they will represent the minimum bounding rectangle of that polygon. This is useful for accelerating within queries, as seen in the Timezones API example. | ["SpatiaLite"] | [{"href": "https://timezones-api.datasette.io/timezones/idx_timezones_Geometry", "label": "timezones-api.datasette.io/timezones/idx_timezones_Geometry"}] |