Fascinating use of DuckDB!
Can I ask where you get official OSM PBF data from? (I found these two links, but not sure what data these contain)
Can I ask where you get official OSM PBF data from? (I found these two links, but not sure what data these contain)
The first one is the official OpenStreetMap data, which contains the "planet file" - i.e. all the data for the entire world. But because OSM has so much stuff in it, the planet file is a whopping 76 GB, which can take a long time to process for most tasks. I also recommend using the torrent file for faster download speeds.
As a result of the planet's size, the German company Geofabrik provides unofficial "extracts" of the data, which are filtered down to a specific region. E.g. all the data in a particular continent, country, or U.S. state. If you click on the "Sub Region" link it will show countries, and if you click on those it will show states.
I appreciate your taking the time to share this tidbit! It's a game changer in what I do (geospatial).
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'
When I index the entire United States, admittedly with a subset of tags, I can search in milliseconds. I'll take it.