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.
This is AWESOME! I'm doing some volunteer work to make a map of cycling routes in my city, and using OSM data to annotate features. Having a SQLite db of all the Way tags would make my work a lot easier! Thanks!
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.
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.
This is AWESOME! I'm doing some volunteer work to make a map of cycling routes in my city, and using OSM data to annotate features. Having a SQLite db of all the Way tags would make my work a lot easier! Thanks!
Does SQLite have GIS capabilities (like PostGIS provides for Postgres), or is this storing the data as traditional database primitives?
Check out Spatialite. https://www.gaia-gis.it/fossil/libspatialite/index
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.
From the schema it looks like this is storing nodes and lines: https://github.com/osmzoso/pbf2sqlite/blob/main/doc/pbf2sqli...
There's an option to enable RTree indexes to speed up queries like this one:
DuckDB 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/
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.
Could this be used for reverse geocoding?