Pbf2sqlite: Reading OpenStreetMap into a SQLite Database
62 points by amadeuspagel 5 days ago | 9 comments- juliansimioni 2 days agoThis 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.
- tjridesbikes 2 days agoThis 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!
- milliams 5 days agoDoes SQLite have GIS capabilities (like PostGIS provides for Postgres), or is this storing the data as traditional database primitives?
- fros1y 5 days agoCheck out Spatialite. https://www.gaia-gis.it/fossil/libspatialite/index
- Scaevolus 4 days 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
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 2 days agoFrom 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; - 0cf8612b2e1e 4 days agoDuckDB has put some effort into GIS, so if SQLite does not immediately fit the bill, I would go there.
- wiredfool 2 days 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 5 days ago
- teruakohatu 2 days agoCould this be used for reverse geocoding?