Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

DuckDB is probably the most important geospatial software of the last decade

dbreunig.com

Post reply on HN
Print view View original

161–170 of 177 posts

Re: DuckDB is probably the most important geospatial software of the last decade

#161

Post by [deleted] »

8note wrote: post #155
wenc wrote: post #84

Earlier quoted context omitted.

I don't quite agree. SQL queries are one area where correctness matters a lot, because downstream applications rely on them to be correct. If you get a query wrong, especially in an ETL process, you can generate a lots of garbage data for a very long time (I'm speaking from lived experience). It might take a long time to correct (via backfill) and sometimes the original data might no longer be available. I use LLMs t…

this suggests that i should expect that dara coming out of SQL is likely to be wrong, since lots of people writing it dont have the deep knowledge, and its very hard to verify correctness of the results since there is limited to no ground truth. if i already expect it to be making at least some of a mess, why not have AI as part of the setup?

[deleted]

Re: DuckDB is probably the most important geospatial software of the last decade

#162

Post by [deleted] »

vasco wrote: post #95
wenc wrote: post #84

Earlier quoted context omitted.

I don't quite agree. SQL queries are one area where correctness matters a lot, because downstream applications rely on them to be correct. If you get a query wrong, especially in an ETL process, you can generate a lots of garbage data for a very long time (I'm speaking from lived experience). It might take a long time to correct (via backfill) and sometimes the original data might no longer be available. I use LLMs t…

Testing SQL for correctness is besides the point of how the SQL was generated. It's important of course to review and test anything. My point is SQL writing is particularly good already with current state of the art of assistants for it to be worth it to spend much time hand crafting complex queries.

[deleted]

Re: DuckDB is probably the most important geospatial software of the last decade

#163

Post by wodenokoto »

wenc wrote: post #137
wodenokoto wrote: post #85

Earlier quoted context omitted.

Why do you use haver-sine over geodesic or reprojection? I’ve been doing the reprojection thing, projecting coordinates to a “local” CRS, for previous projects mainly because that’s what geopandas recommend and is built around, but I am reaching a stage where I’d like to calculate distance for objects all over the globe, and I’m genuinely interested to learn what’s a good choice here.

Reprojection is accurate locally but inaccurate at scale. Geodesics are the most accurate (Vincenty etc) but are computationally heavy. Haversine is a nice middle ground.

But is it actually inaccurate at scale?

I get that drawing a projection is inaccurate at scale, but if I do all my calculation as meters north/south and east/west of 0,0 on the equator, won’t all my distance calculations be correct?

Like 5.000.000 east and 0 m north is 5.000km from the origin. I cannot see how that could ever become inaccurate as I move further away.

Where is the inaccuracy introduced? When I reproject back to coordinates on a globe?

Re: DuckDB is probably the most important geospatial software of the last decade

#164

Post by maxxen »

larodi wrote: post #147
maxxen wrote: post #28

Earlier quoted context omitted.

I replied to another comment, but I think a big part is that duckdbs spatial extension provides a SQL interface to a whole suite of standard foss gis packages by statically bundling everything (including inlining the default PROJ database of coordinate projection systems into the binary) and providing it for multiple platforms (including WASM). I.E there are no transitive dependencies except libc. Yes, DuckDB does a…

> a big part is that duckdbs spatial extension provides a SQL interface to a whole suite of standard foss gis packages by statically bundling everything (including inlining the default PROJ database of coordinate projection systems into the binary) and providing it for multiple platforms (including WASM). I.E there are no transitive dependencies except libc. and for the last twenty, not ten years, this is what PostGI…

Actually, they do https://www.esri.com/arcgis-blog/products/arcgis-pro/announc...

Re: DuckDB is probably the most important geospatial software of the last decade

#165

Post by aynyc »

wenc wrote: post #160
aynyc wrote: post #158

Earlier quoted context omitted.

Wait, so I create a new file for every message?

Typically small data is batched. While theoretically you could, I wouldn't create 1 file per row (there would be too many files and your filesystem would struggle). But maybe you can batch 1 day's worth of data (or whatever partitioning works for your data) and write to 1 parquet file? For example, my data is usually batched by yearwk (year + week no), so my directory structure looks like this: /data/yearwk=202501/00…

I was really surprised duckdb choked on 500GB. That's maybe a week's worth of data.

The partitioning of partquet files might be an issue as not all data are neatly partitioned by date. We have trades with different execution dates, clearance dates and other date values that we need query on.

Re: DuckDB is probably the most important geospatial software of the last decade

#166

Post by wenc »

aynyc wrote: post #165
wenc wrote: post #160

Earlier quoted context omitted.

Typically small data is batched. While theoretically you could, I wouldn't create 1 file per row (there would be too many files and your filesystem would struggle). But maybe you can batch 1 day's worth of data (or whatever partitioning works for your data) and write to 1 parquet file? For example, my data is usually batched by yearwk (year + week no), so my directory structure looks like this: /data/yearwk=202501/00…

I was really surprised duckdb choked on 500GB. That's maybe a week's worth of data. The partitioning of partquet files might be an issue as not all data are neatly partitioned by date. We have trades with different execution dates, clearance dates and other date values that we need query on.

It doesn’t usually choke on 500 gb of data. I query 600 gb (equivalent to a few TBs of CSVs?) of parquets daily. It’s not the size of the data. It’s the type of data.

If date partitioning doesn’t work, just find another chunking key. The key is to get it into parquet format. CSV is just hugely inefficient.

Or spin up a larger compute instance with more memory. I have 256gb on mine.

I tried running an Apache Spark job (8 machine cluster) on a data lake of 300 Gb of TSVs once. This was a distributed cluster. There was one join in it. It timed out after 8 hours. I realized why — Spark had to do many full table scans of the TSVs and it was just so inefficient. CSV formats are ok for straight up reads, but any time you have to do analytics operations like aggregate or join them at scale, you’re in for a world of pain.

DuckDB has better CSV handling than Spark but a large dataset in a poor format will stymie any engine.

Re: DuckDB is probably the most important geospatial software of the last decade

#167

Post by larodi »

maxxen wrote: post #164
larodi wrote: post #147

Earlier quoted context omitted.

> a big part is that duckdbs spatial extension provides a SQL interface to a whole suite of standard foss gis packages by statically bundling everything (including inlining the default PROJ database of coordinate projection systems into the binary) and providing it for multiple platforms (including WASM). I.E there are no transitive dependencies except libc. and for the last twenty, not ten years, this is what PostGI…

Actually, they do https://www.esri.com/arcgis-blog/products/arcgis-pro/announc...

no surprise I missed it, 3.4 was only released in 25.11.2024 г. which is like yesterday in ESRI terms, given price and speed of adoption of new versions (note: many .gov still use the 10.x/11.x branch).

which does not change my original statement that nobody cared about DuckDB for very long, while the whole server-side processing idea is largely based on PostGIS.

Re: DuckDB is probably the most important geospatial software of the last decade

#168

Post by fastasucan »

jparishy wrote: post #14

I work on geospatial apps and the software I think I am most excited about is https://felt.com/ . I want to see them expand their tooling such that maps and data source authentication/authorization was controllable by the developer, to enable tenant isolation with proprietary data access. They could really disrupt how geospatial tech gets integrated into consumer apps. This article doesn't acknowledge how niche this…

>As mentioned by another commenter, this DuckDB DX as described is basically the same as PostGIS too.

No, its not.

Re: DuckDB is probably the most important geospatial software of the last decade

#169

Post by fastasucan »

aynyc wrote: post #159
broner wrote: post #151

Earlier quoted context omitted.

Works fine for me on TB+ datasets. Maybe you were doing in-memory rather than persistent database and running out of RAM? https://duckdb.org/docs/stable/clients/cli/overview.html#in-...

Wait, do you insert the data from S3 into duckdb? I was just doing select from file.

Maybe its your terminal that chockes because it tries to display to much data? 500GB should be no problem.

Re: DuckDB is probably the most important geospatial software of the last decade

#170

Post by broner »

aynyc wrote: post #159
broner wrote: post #151

Earlier quoted context omitted.

Works fine for me on TB+ datasets. Maybe you were doing in-memory rather than persistent database and running out of RAM? https://duckdb.org/docs/stable/clients/cli/overview.html#in-...

Wait, do you insert the data from S3 into duckdb? I was just doing select from file.

Nope, just reading from S3. Check this out: https://duckdb.org/2024/07/09/memory-management.html
Post reply on HN