4 ms·
Does SQLite have GIS capabilities (like PostGIS provides for Postgres), or is this storing the data as traditional database primitives?
by milliams 1y ago
Does SQLite have GIS capabilities (like PostGIS provides for Postgres), or is this storing the data as traditional database primitives?
- simonw 1y agoFrom the schema it looks like this is storing nodes and lines: https://github.com/osmzoso/pbf2sqlite/blob/main/doc/pbf2sqlite.md https://github.com/osmzoso/pbf2sqlite/blob/main/doc/pbf2sqli... 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 1y agoSQLite + 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.
- fros1y 1y agoCheck out Spatialite. https://www.gaia-gis.it/fossil/libspatialite/index https://www.gaia-gis.it/fossil/libspatialite/index
- 0cf8612b2e1e 1y agoDuckDB has put some effort into GIS, so if SQLite does not immediately fit the bill, I would go there. https://motherduck.com/blog/getting-started-gis-duckdb/ https://motherduck.com/blog/getting-started-gis-duckdb/
- Scaevolus 1y agoSQLite 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 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.