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

121–130 of 177 posts

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

#121

Post by wenc »

indeyets wrote: post #91
wenc wrote: post #83

Earlier quoted context omitted.

> importing them into postgres shouldn't be that long and then you can do the same or more than with DuckDB. Usually new data is generated regularly and would require creating a separate ETL process to ingest into Postgres. With DuckDB, no ETL is needed. New Parquet files are just read off the disk. > Also as a side note, is everyone just using DuckDB in memory? DuckDB is generally used as a single-user, and yes in-m…

> Usually new data is generated regularly This part was not obvious. In a lot of cases geodata is mostly stable and reads/searches dominate over appends. And that’s why we keep this in DB (usually postgis, yes). So DuckDB is optimised for very different use case and it is not always obvious when it’s mentioned

This is the more trivial use case (static data, heavy reads) and DuckDB is absolutely optimized for this use case.

DuckDB also provides a vectorized, parallelized engine. When I run a query all of my 32 cores light up on htop.

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

#122

Post by perrygeo »

Why? The article is light on details. Yes, having spatial analysis combined with SQL is awesome and very natural. There's nothing special about 2D geometries that makes them significantly different from floats and strings in an RDBMS perspective - geometry is just another column type, albeit with some special operators and indexes. We've been doing it with PostGIS, Spatialite, etc for two decades at this point.

What DuckDB brings to the table is cloud-native formats. This is standard geospatial functionality attached to an object store instead of a disk. As such, it doesn't require running a database process - data is always "at rest" and available over HTTP. I'm not downplaying the accomplishment, it's really convenient. But know that this is a repackaging of existing tech to work efficiently within a cloud IO environment. If anything, the major innovation of DuckDB is in data management not geospatial per se.

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

#123

Post by sroerick »

fidotron wrote: post #9

Honestly, I think it's actually https://www.uber.com/en-CA/blog/h3/

Could you elaborate on this? I experimented with h3 a bit for queries but I have never used it in production. Obviously, it's very effective for Uber but I wonder if you have any other experience with it

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

#124

Post by mpalmer »

perrygeo wrote: post #122

Why? The article is light on details. Yes, having spatial analysis combined with SQL is awesome and very natural. There's nothing special about 2D geometries that makes them significantly different from floats and strings in an RDBMS perspective - geometry is just another column type, albeit with some special operators and indexes. We've been doing it with PostGIS, Spatialite, etc for two decades at this point. What…

The argument seemed pretty clear to me; more accessible tooling means more users and contributors. But as an apparent SME I could see how you'd feel clickbaited by the title.

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

#125

Post by sroerick »

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 the first time, and that was actually a shockingly useful workflow.

pandas is very nice and was my bread and butter for a long time, but I frequently ran into memory issues and problems at scale with pandas, which I would never hit with polars or duckdb. I'm not sure if this holds true today as I know there's been updates, but it was certainly a problem then. Using geopandas ran into the same issues.

Just using GDAL and other libraries out of the box is frankly not a great experience. If you have a QGIS (another wonderful tool) workflow, it's frustrating to be dropping into Jupyter notebooks to do translations, but that seemed to be the best option.

In general, it just feels like geospatial analysis is about 10 years behind regular data analysis. Shapefiles are common because of ESRI dominance, but frankly not a great format. PostGIS is great, geopandas is great, but there's a lot more things in the data ecosystem than just Postgres and pandas. PowerBI barely had geospatial support a couple years ago. I think PowerBI Shapemaps exclusively used TopoJSON?

All of this is to say, DuckDB geospatial is very cool and helpful.

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

#126

Post by sroerick »

Demiurge wrote: post #55
wenc wrote: post #48

Earlier quoted context omitted.

That requires data to already be in Postgres, otherwise you have to ETL data into it first. DuckDB on the other hand works with data as-is (Parquet, TSV, sqlite, postgres... whether on disk, S3, etc.) with requiring an ETL step (though if the data isn't already in a columnar format, things are gonna be slow... but it will still work). I work with Parquet data directly with no ETL step. I can literally drop into Jupyt…

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…

From the DuckDB geospatial docs:

SELECT * FROM './path/to/some/shapefile/dataset.shp';

COPY table TO 'some/file/path/filename.geojson' WITH (FORMAT gdal, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES');

This seems extremely helpful to me.

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

#127

Post by serjester »

I think this is part of a broader trend of geospatial data just becoming easier to work with. DuckDB is great for quick ad hoc stuff, but I find polars to be easier to maintain. Personally, I'm really excited for polars to (eventually) add true geospatial support. In the meantime, creating a custom h3 plugin only took a couple days and it simplified massive parts of our old geo pandas / duckdb code. The faster we can completely get rid of geo pandas, the better.

[1] https://github.com/Filimoa/polars-h3

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

#128

Post by serjester »

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.

Ask anyone that's just starting out with geo pandas about their experience, and I'd be shocked if anyone calls it intuitive and straightforward. To geopandas credit, I think they just inherited many of Pandas' faults (why does a user need to understand indexes to do basic operations, no multi core support, very poor type hinting, etc).

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

#129

Post by Demiurge »

sroerick wrote: post #126
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…

From the DuckDB geospatial docs: SELECT * FROM './path/to/some/shapefile/dataset.shp'; COPY table TO 'some/file/path/filename.geojson' WITH (FORMAT gdal, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES'); This seems extremely helpful to me.

And ogr2ogr is also very helpful:

ogr2ogr -f "PostgreSQL" -t_srs "EPSG:2274" PG:"host=host user=user dbname=database password=trustno1 schemas=schema" shapefile.shp -nln new_name_for_table

I'll give DuckDB a shot and compare to postgis. Eventually, I think the issue for me would be that I use all the vector outputs via apps connecting to the same database.

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

#130

Post by fifilura »

I have been using Trino/AWS Athena for some geospatial work.

It's API is not very well covered, some parts are still missing, but it must just be a matter of time.

Where it shines is when you need to do an O(n2) or O(nm) type of calculation. Then those 100s of free CPU cores really come in handy! And the end result is pretty often a dollar for CPU-days worth of computation.

Example of O(nm) calculation are things like finding the closest road segment inside a tile (or more likely a tile and it's surrounding tiles), for each point in a list.

Post reply on HN