SQLite can be built with the R*tree module, which supports efficiently looking up all bounding boxes that contain a point: https://www.sqlite.org/rtree.html
PostGIS similarly provides an R*Tree index mode, as well as a heap of functions for doing GIS calculations directly. To do that in SQLite, you'd implement and inject custom functions as appropriate.
There's an option to enable RTree indexes to speed up queries like this one:
SELECT way_id
FROM rtree_way
WHERE max_lon>= 7.851 AND min_lon<= 7.854
AND max_lat>=47.995 AND min_lat<=47.996;
wiredfool•7h ago
SQLite + spatial (and specific metadata tables) is essentially the Geopackage format, which can be considered the modern equivalent of a shapefile.
Which is to say, yes, SQLite has geospatial operations and they’re well supported by the open source gis stack.
juliansimioni•6h ago
This is super cool. As part of the Pelias geocoder(https://pelias.io/) we use both OSM and SQLite heavily. Currently we've written our own pbf2json tool in Golang (https://github.com/pelias/pbf2json). But creating intermediate databases in SQLite could enable more powerful manipulation of OSM data before we eventually import it.
milliams•2d ago
fros1y•2d ago
0cf8612b2e1e•2d ago
https://motherduck.com/blog/getting-started-gis-duckdb/
Scaevolus•2d ago
PostGIS similarly provides an R*Tree index mode, as well as a heap of functions for doing GIS calculations directly. To do that in SQLite, you'd implement and inject custom functions as appropriate.
simonw•7h ago
There's an option to enable RTree indexes to speed up queries like this one:
wiredfool•7h ago
Which is to say, yes, SQLite has geospatial operations and they’re well supported by the open source gis stack.