6 ms·
I recently discovered DuckDB's Read_OSM() function [1], which lets you query OSM PBF files directly. For example, it's simple to count the cafes in North Ameri
by wcedmisten 2y ago
I recently discovered DuckDB's Read_OSM() function [1], which lets you query OSM PBF files directly.
For example, it's simple to count the cafes in North America in under 30s:
SELECT COUNT(*) FROM st_readOSM('/home/wcedmisten/Downloads/north-america-latest.osm.pbf') WHERE tags['amenity'] = ['cafe'];
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 57150 │
└──────────────┘
Run Time (s): real 24.643 user 379.067204 sys 3.696217
Unfortunately, I discovered there are still some bugs [2] that need to be ironed out, but it seems very promising for doing high performance queries with minimal effort.
[1]: https://duckdb.org/docs/extensions/spatial.html#st_readosm--read-compressed-osm-data https://duckdb.org/docs/extensions/spatial.html#st_readosm--...
[2]: https://github.com/duckdb/duckdb_spatial/issues/349 https://github.com/duckdb/duckdb_spatial/issues/349
- winrid 2y agoThat's cool, but not what I would call high performance. If you do these often you would want an index, and should only take single digit ms.
- wild_egg 2y agoNot near a computer to try this out but I'd be surprised if you couldn't get a huge speed up by selecting the whole file into a real table first and querying against that. DuckDB should be able to better vectorize operations then
- wcedmisten 2y agoThe reason I call it high performance is that it avoids the hours/days of processing (for the planet file)[1] that would be required for pulling the data out of PBF and indexing it. And you'd also need RAM at least the size of the planet to even get that level of speed. You could certainly amortize this cost for repeated queries, but for one-off queries I haven't seen anything faster. [1]: https://wiki.openstreetmap.org/wiki/Osm2pgsql/benchmarks https://wiki.openstreetmap.org/wiki/Osm2pgsql/benchmarks
- winrid 2y agoYou wouldn't need hundreds of gigs of ram to answer this query in 5ms. You'd need a few mb or so to answer this query, after initial indexing is done of course.
- ericjmorey 2y agoHow long do you think it would take to index it?
- winrid 2y agoDepends on the machine :) hours maybe?
- arp242 2y agoSo for a one-off query DuckDB is tons faster, and easier.
- winrid 2y agoyeah, but it's not high performance, which was my original point. I spend a lot of time optimizing stuff developers thought was "high performance" and they're scanning a 100gb+ dataset on every page load.
- arp242 2y agoIf the alternative takes hours, then ~30 seconds seems high performance to me.
- deleted 2y ago[deleted]
- winrid 2y agoDuckDB is indeed great for one off stuff. But calling 30s high performance for quantifying 60k unique values like in this case is misleading at best. I try not to mislead people. :)
- wenc 2y agoFascinating 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) https://planet.openstreetmap.org/pbf/ https://planet.openstreetmap.org/pbf/ http://download.geofabrik.de/ http://download.geofabrik.de/
- wcedmisten 2y agoThose are the most popular sources, and I've used both! 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.
- wenc 2y agoSo it's GeoFabrik is the same data, but regionalized. This sounds like what I need. I already use DuckDB, so this is great. I appreciate your taking the time to share this tidbit! It's a game changer in what I do (geospatial).
- wenc 2y agoI 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.
- jtarchie 2y agoI did try DuckDB at first. Not for tags, but the relationships between the nodes, ways, and relations. It was slow. Really fast to important, however!