7 months 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.
7 months ago
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!
7 months ago
Does SQLite have GIS capabilities (like PostGIS provides for Postgres), or is this storing the data as traditional database primitives?
7 months ago
Check out Spatialite. https://www.gaia-gis.it/fossil/libspatialite/index
7 months ago
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.
7 months ago
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:
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;7 months ago
DuckDB has put some effort into GIS, so if SQLite does not immediately fit the bill, I would go there.
7 months 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.
7 months ago
Could this be used for reverse geocoding?