Optimizing Large-Scale OpenStreetMap Data with SQLite

Re:Optimizing Large-Scale OpenStreetMap Data with SQLite by winrid on Saturday July 06, 2024 @06:26PM (#40893827)

You 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.

[ Parent | on HN ]

Re:Optimizing Large-Scale OpenStreetMap Data with SQLite by ericjmorey on Saturday July 06, 2024 @06:43PM (#40893905)

How long do you think it would take to index it?

[ Parent | on HN ]

Re:Optimizing Large-Scale OpenStreetMap Data with SQLite by winrid on Saturday July 06, 2024 @07:01PM (#40893987)

Depends on the machine :) hours maybe?

[ Parent | on HN ]

Re:Optimizing Large-Scale OpenStreetMap Data with SQLite by arp242 on Sunday July 07, 2024 @01:11AM (#40895404)

So for a one-off query DuckDB is tons faster, and easier.

[ Parent | on HN ]

Re:Optimizing Large-Scale OpenStreetMap Data with SQLite by winrid on Sunday July 07, 2024 @02:41PM (#40899539)

yeah, 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.

[ Parent | on HN ]

Re:Optimizing Large-Scale OpenStreetMap Data with SQLite by arp242 on Monday July 08, 2024 @07:02AM (#40904298)

If the alternative takes hours, then ~30 seconds seems high performance to me.

[ Parent | on HN ]

Re:Optimizing Large-Scale OpenStreetMap Data with SQLite by winrid on Monday July 08, 2024 @01:19PM (#40907291)

DuckDB 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. :)

[ Parent | on HN ]

Re:Optimizing Large-Scale OpenStreetMap Data with SQLite by jtarchie on Monday July 08, 2024 @12:45PM (#40906966)

I can help answer this a bit.

Processing the data with code and SQLite takes about 4 hours for the United States and Canada. If I can figure out how to parallelize some of the work, it could be faster.

The benefit of using SQLite is its low memory footprint. I can have this 40GB file (the uncompressed version) and use a 256MB container instance. It shows how valuable SQLite is in terms of resources. It takes up disk space, but I followed up with the zstd compression (from the post).

Was this more work and should've I have used Postgres and osm2pql, yes. Was it fun, well yeah, because I have another feature, which I have not written about yet, which I highly value.

[ Parent | on HN ]