Optimizing Large-Scale OpenStreetMap Data with SQLite

Re:Optimizing Large-Scale OpenStreetMap Data with SQLite by wenc on Saturday July 06, 2024 @05:10PM (#40893356)

I was able to find all 10 Whole Foods in the City of Chicago in 22.6s with DuckDB. It's amazing! (there are tons more Whole Foods in the metro area, but it found the exact 10 in the city)

        SELECT 
            tags['addr:city'][1] city,
            tags['addr:state'][1] state,
            tags['brand'][1] brand,
            *, 
        FROM st_readosm('us-latest.osm.pbf')
        WHERE 1=1
        and city = 'Chicago'
        and state = 'IL'
        and brand = 'Whole Foods Market'

I'm sure there are ways to make this faster (partitioning, indexing, COPY TO native format, etc.) but querying a 9.8GB compressed raw format file with data (in key-value fields stored as strings) for the entire United States at this speed is pretty impressive to me.

[ Parent | on HN ]

Re:Optimizing Large-Scale OpenStreetMap Data with SQLite by Raphaellll on Monday July 08, 2024 @10:03AM (#40905535)

1-2 seconds in Overpass which also uses OSM:

https://overpass-turbo.eu/s/1NTy

[ Parent | on HN ]

Re:Optimizing Large-Scale OpenStreetMap Data with SQLite by jtarchie on Monday July 08, 2024 @11:20AM (#40906200)

That's great. I was impressed with DuckDB's speed with the OSM data. I'm doing it with all the data, but I intended to have: - a single file that can be queried - a single file that can be easily compressed

When I index the entire United States, admittedly with a subset of tags, I can search in milliseconds. I'll take it.

[ Parent | on HN ]