Pbf2sqlite: Reading OpenStreetMap into a SQLite Database (github.com)

6 comments

[ 0.20 ms ] story [ 27.7 ms ] thread

Does SQLite have GIS capabilities (like PostGIS provides for Postgres), or is this storing the data as traditional database primitives?

simonw 1y 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;

wiredfool 1y 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 1y 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.

tjridesbikes 1y 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!

teruakohatu 1y ago ↗

Could this be used for reverse geocoding?