[HN Gopher] Pbf2sqlite: Reading OpenStreetMap into a SQLite Data...
       ___________________________________________________________________
        
       Pbf2sqlite: Reading OpenStreetMap into a SQLite Database
        
       Author : amadeuspagel
       Score  : 49 points
       Date   : 2025-07-01 09:17 UTC (2 days ago)
        
 (HTM) web link (github.com)
 (TXT) w3m dump (github.com)
        
       | milliams wrote:
       | Does SQLite have GIS capabilities (like PostGIS provides for
       | Postgres), or is this storing the data as traditional database
       | primitives?
        
         | fros1y wrote:
         | Check out Spatialite. https://www.gaia-
         | gis.it/fossil/libspatialite/index
        
         | 0cf8612b2e1e wrote:
         | 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/
        
         | Scaevolus wrote:
         | 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.
        
         | simonw wrote:
         | 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 wrote:
         | 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 wrote:
       | 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.
        
       ___________________________________________________________________
       (page generated 2025-07-03 23:01 UTC)