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

141–150 of 177 posts

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

#141

Post by wenc »

touisteur wrote: post #98
wenc wrote: post #47

Earlier quoted context omitted.

Probably no difference for your use-case (ST_Distance). If you already have data in Postgres, you should continue using Postgis. In my use case, I use DuckDB because of speed at scale. I have 600GBs of lat-longs in Parquet files on disk. If I wanted to use Postgis, I would have to ingest all this data into Postgres first. With DuckDB, I can literally drop into a Jupyter notebook, and do this in under 10 seconds, and…

And now I'm curious whether there's a way to actually index external files (make these queries over 600GB faster) and have this index (or many indices) be persistent. I might have missed that when I looked at the docs...

If the data is in Parquet they are already indexed in a sense. No further indexing necessary.

If they are stored in DuckDB’s native format (which I don’t use), it supports some state of the art indices.

https://duckdb.org/docs/stable/sql/indexes.html

I find Parquet plenty fast though.

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

#143

Post by groggo »

jofer wrote: post #116
groggo wrote: post #64

Earlier quoted context omitted.

> the software implementation is much less trivial Aren't most geospatial tools just doing simple geometry? And therefore need to work on some sort of projection? If you can do the math on the spheroidal model, ok you get better results and its easier to intuit like you said, but it's much more complicated math. Can you actually do that today with tools like QGIS and GDAL?

For what it's worth, you _can't_ use spherical approaches for most data. They're only used for points, in practice. Your spatial data is inherently stored/generated in ways that don't allow spherical approaches as soon as you start working with polygons, let alone things like rasters. Yes, spherical representations of polygon data exist, but the data you import has already been "split" and undoing that is often impos…

A good point. Certainly for raster analysis it doesn't make sense.

But any type of vector data could be modeled on a sphere, right? Points, shapes, lines. And I saw "better" because even the best suited projection will have some small amount of distortion.

Either way, most things use planer geometry so projections are necessary, and you need to have some understanding of how all that works

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

#144

Post by jparishy »

kashifr wrote: post #99
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…

Have you tried https://geobase.app/ they recently also had a post about duckdb integration: https://geobase.app/blog/duckdb-1-1-3

I had not, looks pretty cool but solves the inverse of the problem as I see it. I want a backend agnostic frontend toolset that is a GIS that I can customize to my needs. I don't want to implement the tools myself, that's too low level. I don't want the service to manage, control, or own the data, that's too high level. There's a sweet spot I don't think is being hit yet.

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

#145

Post by ngrilly »

sroerick wrote: post #125
wenc wrote: post #8

I'm a big fan of DuckDB and I do geospatial analysis, mostly around partitioning geographies (into Uber H3 hexagons), calculating Haversine distances, calculating areas of geometries, figuring out which geometry a point falls in, etc. Many of these features have existed in some form or other in geopandas or postgis, so DuckDB's spatial extensions bring nothing new. But what DuckDB as an engine does is it lets me work…

I totally agree with this. DuckDB for me was a huge QoL improvement just working with random datasets. I found it much easier to explore datasets using DuckDB rather than Pandas, Postgres or Databricks. The spatial features were just barely out when I was last doing a lot of heavy geospatial work, but even then they were very nice. An aside, I had a Junior who would just load datasets into PowerBI to explore them for…

> An aside, I had a Junior who would just load datasets into PowerBI to explore them for the first time, and that was actually a shockingly useful workflow.

What was shockingly useful in PowerBI compared to DuckDB?

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

#146

Post by everybodyknows »

wenc wrote: post #8

I'm a big fan of DuckDB and I do geospatial analysis, mostly around partitioning geographies (into Uber H3 hexagons), calculating Haversine distances, calculating areas of geometries, figuring out which geometry a point falls in, etc. Many of these features have existed in some form or other in geopandas or postgis, so DuckDB's spatial extensions bring nothing new. But what DuckDB as an engine does is it lets me work…

Looking just at the Hilbert reference, I'm wondering why there is no function to return, for a given level of precision, the set of segments along the curve containing corresponding to a sub-rectangle of the space. Is this functionality packaged up elsewhere?

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

#147

Post by larodi »

maxxen wrote: post #28
larsiusprime wrote: post #6

“import geopandas” also exists and has for some time. Snark aside, WHAT is special about duckDB? I wish the author had actually shown some practical examples so I could understand their claims better.

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 PostGIS was pioneering, and also teaching everyone get used to. DuckDB was not something that people even knew in GIS world. I'm not even sure whether QGIS connects to DuckDB, perhaps it does for a while, but it sure knows Spatialite for very long and last, but not least - ESRI sure as f*ck still have not heard of DuckDB. This is already half the geospatial world out there.

This whole article is superb biased and its very sad.

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

#148

Post by broner »

jeffbee wrote: post #68
Demiurge wrote: post #55

Earlier quoted context omitted.

Yeah, if you want to work with GeoParquet, and you want to keep your data in that format. I can see how that's easer to use your example. That's not what a lot of geospatial data is in. You might have shapefiles, geopackages, geojsons, who knows? There is a lot of software, from QGIS to ESRI to work with different formats to solve different problems. I don't think GeoParquet, even though it might be the fastest geosp…

Yeah, just an example of a QoL issue with DuckDB: even though it can glob files in other cases, the way it passes parameters to GDAL means that globs are taken literally instead of expanded. So I can't query a directory with thirty million geojson files. This is not a problem in geopandas because ipython, being a full interactive development environment, allows me to produce the glob any way I choose. I think this is…

You can use DuckDB in ipython to solve the globbing issue. Then you don't have to worry about OOMs with geopandas.

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

#149

Post by dmillar »

DuckDB > geopandas, certainly for anything out of core. Though, I recently gave up on importing 70GB worth of large multipolygons (from a csv in hex wkb), and just used a postgis container. In concert with DuckDB's growth, I'd also mark the advent of geoparquet.

The big change, in my view, over the past decade in GIS software, is in compute and storage efficiency across the typical stack. DuckDB has become a part of this, but h/t to the advances from shapely, geopandas, geoparquet, and GDAL. There's a lot of overlap in that venn diagram, and credit should be spread around. QGIS is great, too, though I feel there is market opportunity to apply 90/10 to its massive feature set and move it to the web.

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

#150

Post by dmillar »

code_biologist wrote: post #90
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.

Just an app dev, not a geospatial expert, but reprojection always seemed like something a library should handle under the hood unless one has specific needs. I'm used to the ergonomics / moron-proofing of something like Postgis' `ST_Distance(geography point1, geography point2)` and it gives you the the right answer in meters. You can easily switch to spherical or Cartesian distances if you need distance calculations…

What the "right answer" is will vary widely on application and analyst. That's one reason there are so many coordinate ref systems. If you keep everything in 3857, you'll get answers in ~meters, but whether that's "right" depends on where and how large the distance or geom is and what your precision threshold is. So, really, everyone's needs are necessarily "specific."
Post reply on HN