11 ms·

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

491 points by dbreunig 1y ago

feverzsj 1y ago
If you are doing lots of spatial index queries, it's actually much slower than SpatiaLite. Because DuckDB uses column-wise storage.
isuckatcoding 1y ago
I was expecting example queries but all I got was how to install a package :(
fithisux 1y ago
I agree
jeffbee 1y ago
Ehh I tried to do some spatial stuff but there just wasn't enough there, or I could not figure out how to use it. Loading spatial information into ipython and fiddling with it is well-traveled and it doesn't seem to me that SQL is an inherently lower hurdle for the user.
wodenokoto 1y ago
I’m not sure I agree that “install geospatial” is a game changer in simplicity compared to “pip install geopandas”. They are both one line.
- maxxen 1y ago
  I think a big part is that duckdbs spatial extension doesnt have any transitive dependencies (except libc). It statically packages the standard suite of foss gis tools (including a whole database of coordinate systems) for multiple platforms (including WASM) and provides a unified SQL interface to it all. (Disclaimer, I work on duckdb-spatial @duckdblabs)
  - lillecarl 1y ago
    "Accessibility" is too often dismissed, yes you CAN do things with things, but getting people to do it is a craft and art. This is often where open-ish-core differs from the enterprise version of something too
  - jessekv 1y ago
    I tried out duckdb-spatial for a hobby project and my takeaway is the main thing it offers over geopandas is performance in batch processing. If I can reduce my spatial analysis to SQL and optimize that SQL a little bit, duckdb will happily saturate my CPUs and memory to process things fast in a way that a Python script struggles to do. Yes, the static linking is nice. It helps with the problem of reproducible environments in Python. But that's not a game changer IMO.
WD-42 1y ago
Is it that much simpler than ‘load extension postgis’? I know geos and gdal have always kinda been a pain, but I feel like docker has abstracted it all away anyway. ‘docker pull postgis’ is pretty easy, granted I’m not familiar with what else duckdb offers.
- dbreunig 1y ago
  Yes. The difference between provisioning a server and running 'install spatial' in a CLI is night and day. Docker has been a big improvement (when I was first learning PostGIS, the amount of time I had to hunt for proj directories or compile software just to install the plugin was a major hurdle), but it's many steps away from: ``` $ duckdb D install spatial; ```
  - frainfreeze 1y ago
    I mean I like duckdb but this feels like you're pushing for it. On my system postgis comes from apt install, and it's one command to activate the "plugin". Is the night and day part not having to run random sh script from the internet to install software on my system?
    - tomnipotent 1y ago
      DuckDB doesn't require a running server. I run duckdb in a terminal, query 10,000 CSV or parquet files and run SQL on them while joining to data hosted in sqlite, a separate duckdb file using its native format, or even Postgres.
    - dbreunig 1y ago
      That’s great! The difference is you’re familiar and know how to do that Getting started from 0 with geo can be difficult for those unfamiliar. DuckDB packages everything into one line with one dependency.
      - _boffin_ 1y ago
        https://postgresapp.com https://postgresapp.com
larsiusprime 1y ago
“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.
- joshvm 1y ago
  I haven't used duckDB but the real comparison is presumably postgis? Which is also absent from the discussion, but I think what the author alludes to. I have no major qualm with pandas and geopandas. However I use it when it's the only practical solution, not because I enjoy using it as a library. It sounds like pandas (or similar) vs a database?
  - stevage 1y ago
    Yeah, PostGIS is readily available, and postgres is much more widely used than DuckDB. Either I don't understand OP's argument for why this is so important or I just don't buy it. If you're using JavaScript you install Turf. The concept that you can readily install spatial libraries is hardly earth shattering.
- dbreunig 1y ago
  Author here: what's special is that you can go from 0 to spatial data incredibly quickly, in the data generalist tool you're already using. It makes the audience of people working with geospatial data much bigger. (Geopandas is great, too.)
  - dopidopHN 1y ago
    I’m very familiar with Postgres and spinning one with postgis seems easy enough. Do I get more with duckdb? Most of the time I store locations and compute distance to them. Would that being faster to implement with duckdb
    - wenc 1y ago
      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 the results come back in a flash: (no need to ingest any data ahead of time) import duckdb duckdb.query("INSTALL spatial; LOAD spatial;") duckdb.query("select ST_DISTANCE(ST_POINT(lng1, lat1), ST_POINT(lng2, lat2)) dist from '/mydir/*.parquet'")
twelvechairs 1y ago
DuckDB is a great thing for geospatial but most important of the past decade? There's so many tools in different categories it wouldnt come near top for me. Some might be QGIS, postGIS (still the standard), ArcGIS online (still the standard), JS mapping tools like mapbox (i prefer deckgl), new data types like COG, geopackage and geoparquet, photogrammetry tools, 3d tiles, core libraries like gdal and now pdal, shapely, etc.
- deleted 1y ago
  [deleted]
- dbreunig 1y ago
  Most of those tools came out circa ~2000. Yeah, I feel old.
  - twelvechairs 1y ago
    It clearly says "most important of the past 10 years" not "most important that has been invented in the past 10 years". Even taking your definition that would narrow down the list like half maybe and you should probably know that
wenc 1y ago
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 directly on parquet/geoparquet files at scale (vectorized and parallelized) on my local desktop. It beats geopandas in that respect. It's a quality of life improvement to say the least. DuckDB also has an extension architecture that admits more exotic geospatial features like Hilbert curves, Uber H3 support. https://duckdb.org/docs/stable/extensions/spatial/functions.html#st_hilbert https://duckdb.org/docs/stable/extensions/spatial/functions.... https://duckdb.org/community_extensions/extensions/h3.html https://duckdb.org/community_extensions/extensions/h3.html
- wodenokoto 1y ago
  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.
  - deleted 1y ago
    [deleted]
  - code_biologist 1y ago
    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 to go faster. `ST_Area(geography geog)` and it gives you the size of your shape in square meters wherever on the planet.
fidotron 1y ago
Honestly, I think it's actually https://www.uber.com/en-CA/blog/h3/ https://www.uber.com/en-CA/blog/h3/
- jsemrau 1y ago
  Makes one wonder if the YOLO algorithm would work better with hexagons. "Hexagons were an important choice because people in a city are often in motion, and hexagons minimize the quantization error introduced when users move through a city. Hexagons also allow us to approximate radiuses easily, such as in this example using Elasticsearch." [Edit]Maybe https://www.researchgate.net/publication/372766828_YOLOv8_for_Defect_Inspection_of_Hexagonal_Directed_Self-Assembly_Patterns_A_Data-Centric_Approach https://www.researchgate.net/publication/372766828_YOLOv8_fo...
- sroerick 1y ago
  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
bingaweek 1y ago
We need a "come on" clause for these absurd headlines. Come on.
- deleted 1y ago
  [deleted]
yoyopa 1y ago
[dead]
badmonster 1y ago
How might embedding spatial capabilities directly into general-purpose data tools like DuckDB reshape who participates in geospatial analysis—and what kinds of problems they choose to solve?
jparishy 1y ago
I work on geospatial apps and the software I think I am most excited about is https://felt.com/ 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 stuff is and it's a lot of training to get people to up to speed on coordinate systems, projections, transformations, etc. I would replace a lot of my custom built mapping tools with Felt if it were possible, so I could focus on our core geospatial processes and not the code to display and play with it in the browser, which is almost as big if not bigger in terms of LOC to maintain. As mentioned by another commenter, this DuckDB DX as described is basically the same as PostGIS too.
- dbreunig 1y ago
  Author here: the beauty of DuckDB spatial is that the projections and CRS options are hidden until you need them. For 90% of geospatial data usage people don't and shouldn't need to know about projections or CRS. Yes, there are so many great tools to handle the complexity for the capital-G Geospatial work. I love Felt too! Sam and team have built a great platform. But lots of times a map isn't needed; an analyst just needs it as a column. PostGIS is also excellent! But having to start up a database server to work with data doesn't lend itself to casual usage. The beauty of DuckDB is that it's there in a moment and in reach for data generalists.
  - jparishy 1y ago
    I think we're mostly making the same point about complexity, ya. To me, I think it's mostly a frontend problem stopping the spread of mapping in consumer apps. Backend geo is easy tbh. There is so much good, free tooling. Mapping frontend is hell and there is no good off the shelf solution I've seen. Some too low level, some too high level. I think we need a GIS-lite that is embeddable to hide the complexity and let app developers focus on their value add, and not paying the tax of having frontend developers fix endless issues with maps they don't understand. edit: to clarify, I think there's a relationship between getting mapping valued by leadership such that the geo work can be even be done by analysts, and having more mapping tools exist in frontend apps such that those leaders see them and understand why geo matters. it needs to be more than just markers on the map, with broad exposure. hence my focus on frontend web. sorry if that felt disjointed
patja 1y ago
SQL Server has geospatial capabilities without any extensions or add-ons. I've been happily using geospatial datatypes on the free Express version for years, probably well over a decade.
cyanydeez 1y ago
No. QGIS is. Good god.
- dbreunig 1y ago
  Author here. QGIS is amazing. It's really great. It also came out in 2002, so I think the headline is safe.
  - cyanydeez 1y ago
    Nope, it's constantly being improved and still wins the decade. Do you disqualify it because it existed? Re-read your headline, it's definitely not qualifying what you think it's qualifying.
WD-42 1y ago
Uhoh, another pushover-licensed database. I wonder when it will begin it’s own redis saga.
Demiurge 1y ago
> Prior to this, getting up and running from a cold-start might’ve required installing or even compiling severall OSS packages, carefully noting path locations, standing up a specialized database… Enough work that a data generalist might not have bothered, or their IT department might not have supported it. I've been able to "CREATE EXTENSION postgis;" for more than a decade. There have been spatial extensions for PG, MySQL, Oracle, MS SQL Server, and SQLite for a long time. DuckDB doesn't make any material difference in how easy it is to install.
- wenc 1y ago
  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 Jupyter or a Python REPL and duckdb.query("from '*.parquet'") Correct me if I'm wrong, but I don't think that's possible with Postgis. (even pg_parquet requires copying? [1]) [1] https://www.crunchydata.com/blog/pg_parquet-an-extension-to-connect-postgres-and-parquet https://www.crunchydata.com/blog/pg_parquet-an-extension-to-...
  - edoceo 1y ago
    Not wrong. Load to PG, then query. Duck UVP is like bringing 8 common tools/features under one tent.
  - Demiurge 1y ago
    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 geospatial vector data format right now, is that common, and the article did not claim that either. So, given an average user trying to answer some GIS question, some ETL is pretty much a given, on average. And given that, installing PostGIS and installing DuckDB, both require some ETL, and learning some query and analytics language. DuckDB might be an improvement, but it's certainly not as much of a leap as quote is making it out to be.
jandrewrogers 1y ago
I think geospatial analytics is important (because of course I would), but to be frank geospatial software has been stagnant for a long time. Every new thing is just a fresh spin on the same stagnant things we already have. This more or less says exactly this? For geospatial analysis, the most important thing that could happen in software would be no longer treating it, either explicitly or implicitly, as having anything to do with cartography. Many use cases are not remotely map-driven but the tools require users to force everything through the lens of map-making.
- azinman2 1y ago
  Can you give some examples?
  - jandrewrogers 1y ago
    Of the stagnation? I’ve been doing geospatial analytics for over 20 years and shockingly little has changed, both in features and capability. Given the amount of time that has passed and the vastly expanded scope of the geospatial data models people are working with today, I think most people would expect more to have changed.
    - azinman2 1y ago
      No I meant to this: “Many use cases are not remotely map-driven but the tools require users to force everything through the lens of map-making.”
- dbreunig 1y ago
  I was struck by this as people suggest alternatives that refute the headline (QGIS, PostGIS, GDAL, etc): nearly every one emerged in the early 2000s. Strongly agree with your sentiment around maps: most people can’t read them, they color the entire workflow and make it more complex, and (imo) lead to a general undervaluing of the geospatial field. Getting the data into columns means it’s usable by every department.
willtemperley 1y ago
I have some concerns regarding licensing of DuckDB and GEOS which DuckDB spatial depends on. The former is MIT licensed and the latter LGPL 2.1. This leads to some complex situations where some builds would contravene LGPL 2.1 e.g static linking with a closed source application.
kriro 1y ago
That's a pretty grandiose statement and frankly the kind of advertisement I'm not a fan of at all. If you want to import something and work with it GeoPandas exists. If you want something integrated with a SQL database, PostGIS exists. On the application side of the spectrum, GRASS GIS, QGIS etc. say hi. They are being used in the agriculture industry and by government agencies (at least I know that's the case in Germany and Brazil).
oreilles 1y ago
Chiming in to promote a similar project, a geospatial extension for Polars [1] I'm working on. It's not stable yet (abeit pretty close to), but is already pretty feature complete (it uses GEOS and PROJ as a backend, so has parity with GeoPandas). [1] https://github.com/oreilles/polars-st/ https://github.com/oreilles/polars-st/
aynyc 1y ago
How big are the data sets? I've been trying to get duckdb to work in our company on financial transactions and reporting data. The dataset is around 500GB CSV in S3 and duckdb chokes on it.
- snake_doc 1y ago
  Are you querying from an EC2 instance close to the S3 data? Are the CSVs partitioned into separate files? Does the machine have 500GB of memory? It’s not always duckdb fault when there can be a clear I/O bottleneck…
  - aynyc 1y ago
    No, the EC2 instance doesn't have 500GB of data. Does DuckDB require that? I actually downloaded the data from S3 to local EBS and still choked.
    - broner 1y ago
      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-memory-vs-persistent-database https://duckdb.org/docs/stable/clients/cli/overview.html#in-...
      - aynyc 1y ago
        Wait, do you insert the data from S3 into duckdb? I was just doing select from file.
        fastasucan 1y ago
        Maybe its your terminal that chockes because it tries to display to much data? 500GB should be no problem.
        broner 1y ago
        Nope, just reading from S3. Check this out: https://duckdb.org/2024/07/09/memory-management.html https://duckdb.org/2024/07/09/memory-management.html
perrygeo 1y ago
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.
- mpalmer 1y ago
  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.
  - perrygeo 1y ago
    Mainly a point of clarification - I don't think DuckDB represents anything new in geospatial. It represents a new paradigm for data management and data architecture. Understanding the difference isn't optional. DuckDB handles floating point numbers too - is DuckDB the most important thing in floating point data? Of course not, the data types and operators haven't changed. The underlying compute environment has. That's where the innovation is. I'd simply appreciate if technical writers took two seconds to make this vital distinction - being precise isn't hard and I don't know what we gain by publishing intentionally muddled technical articles.
serjester 1y ago
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 https://github.com/Filimoa/polars-h3
- mettamage 1y ago
  Is it possible with polars to store data more efficiently than a database though? I work with polars, but I haven't delved too deep into the performance characteristics versus postgres or anything like that.
fifilura 1y ago
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.
quasarj 1y ago
I've been out of the loop on DuckDB, where can I get a real overview of what the excitement is about? It just looks like a new sqlite from the CLI...
- deleted 1y ago
  [deleted]
- Groxx 1y ago
  It's essentially a column-oriented sqlite, so it's much faster for some kinds of queries. There's a big market for that category of queries, and nobody wants to run their own hadoop cluster to do single-machine-scale stuff.
elchief 1y ago
I wish to god they would allow 2 connections. One read-only for my BI tool, and one read-write for dbt/sqlmesh
- kianN 1y ago
  I think using a pyarrow dataset as an intermediary would allow for a zero copy read/write from one connection into a second connection.
vincnetas 1y ago
DBeaver (db client) has built in support for displaying geo data. In my case postgis results. I see that duckdb spatial functions are almost identical to postgis ones. https://dbeaver.com/docs/dbeaver/Working-with-Spatial-GIS-data/ https://dbeaver.com/docs/dbeaver/Working-with-Spatial-GIS-da...
dmillar 1y ago
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.

491 points by dbreunig 1y ago

If you are doing lots of spatial index queries, it's actually much slower than SpatiaLite. Because DuckDB uses column-wise storage.

isuckatcoding 1y ago

I was expecting example queries but all I got was how to install a package :(

fithisux 1y ago

I agree

jeffbee 1y ago

Ehh I tried to do some spatial stuff but there just wasn't enough there, or I could not figure out how to use it. Loading spatial information into ipython and fiddling with it is well-traveled and it doesn't seem to me that SQL is an inherently lower hurdle for the user.

wodenokoto 1y ago

I’m not sure I agree that “install geospatial” is a game changer in simplicity compared to “pip install geopandas”. They are both one line.

maxxen 1y ago
I think a big part is that duckdbs spatial extension doesnt have any transitive dependencies (except libc). It statically packages the standard suite of foss gis tools (including a whole database of coordinate systems) for multiple platforms (including WASM) and provides a unified SQL interface to it all. (Disclaimer, I work on duckdb-spatial @duckdblabs)
- lillecarl 1y ago
  "Accessibility" is too often dismissed, yes you CAN do things with things, but getting people to do it is a craft and art. This is often where open-ish-core differs from the enterprise version of something too
- jessekv 1y ago
  I tried out duckdb-spatial for a hobby project and my takeaway is the main thing it offers over geopandas is performance in batch processing. If I can reduce my spatial analysis to SQL and optimize that SQL a little bit, duckdb will happily saturate my CPUs and memory to process things fast in a way that a Python script struggles to do. Yes, the static linking is nice. It helps with the problem of reproducible environments in Python. But that's not a game changer IMO.

WD-42 1y ago

Is it that much simpler than ‘load extension postgis’? I know geos and gdal have always kinda been a pain, but I feel like docker has abstracted it all away anyway. ‘docker pull postgis’ is pretty easy, granted I’m not familiar with what else duckdb offers.

dbreunig 1y ago
Yes. The difference between provisioning a server and running 'install spatial' in a CLI is night and day. Docker has been a big improvement (when I was first learning PostGIS, the amount of time I had to hunt for proj directories or compile software just to install the plugin was a major hurdle), but it's many steps away from: ``` $ duckdb D install spatial; ```
- frainfreeze 1y ago
  I mean I like duckdb but this feels like you're pushing for it. On my system postgis comes from apt install, and it's one command to activate the "plugin". Is the night and day part not having to run random sh script from the internet to install software on my system?
  - tomnipotent 1y ago
    DuckDB doesn't require a running server. I run duckdb in a terminal, query 10,000 CSV or parquet files and run SQL on them while joining to data hosted in sqlite, a separate duckdb file using its native format, or even Postgres.
  - dbreunig 1y ago
    That’s great! The difference is you’re familiar and know how to do that Getting started from 0 with geo can be difficult for those unfamiliar. DuckDB packages everything into one line with one dependency.
    - _boffin_ 1y ago
      https://postgresapp.com https://postgresapp.com

larsiusprime 1y ago

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

joshvm 1y ago
I haven't used duckDB but the real comparison is presumably postgis? Which is also absent from the discussion, but I think what the author alludes to. I have no major qualm with pandas and geopandas. However I use it when it's the only practical solution, not because I enjoy using it as a library. It sounds like pandas (or similar) vs a database?
- stevage 1y ago
  Yeah, PostGIS is readily available, and postgres is much more widely used than DuckDB. Either I don't understand OP's argument for why this is so important or I just don't buy it. If you're using JavaScript you install Turf. The concept that you can readily install spatial libraries is hardly earth shattering.
dbreunig 1y ago
Author here: what's special is that you can go from 0 to spatial data incredibly quickly, in the data generalist tool you're already using. It makes the audience of people working with geospatial data much bigger. (Geopandas is great, too.)
- dopidopHN 1y ago
  I’m very familiar with Postgres and spinning one with postgis seems easy enough. Do I get more with duckdb? Most of the time I store locations and compute distance to them. Would that being faster to implement with duckdb
  - wenc 1y ago
    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 the results come back in a flash: (no need to ingest any data ahead of time) import duckdb duckdb.query("INSTALL spatial; LOAD spatial;") duckdb.query("select ST_DISTANCE(ST_POINT(lng1, lat1), ST_POINT(lng2, lat2)) dist from '/mydir/*.parquet'")

twelvechairs 1y ago

DuckDB is a great thing for geospatial but most important of the past decade? There's so many tools in different categories it wouldnt come near top for me. Some might be QGIS, postGIS (still the standard), ArcGIS online (still the standard), JS mapping tools like mapbox (i prefer deckgl), new data types like COG, geopackage and geoparquet, photogrammetry tools, 3d tiles, core libraries like gdal and now pdal, shapely, etc.

deleted 1y ago
[deleted]
dbreunig 1y ago
Most of those tools came out circa ~2000. Yeah, I feel old.
- twelvechairs 1y ago
  It clearly says "most important of the past 10 years" not "most important that has been invented in the past 10 years". Even taking your definition that would narrow down the list like half maybe and you should probably know that

wenc 1y ago

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 directly on parquet/geoparquet files at scale (vectorized and parallelized) on my local desktop. It beats geopandas in that respect. It's a quality of life improvement to say the least. DuckDB also has an extension architecture that admits more exotic geospatial features like Hilbert curves, Uber H3 support. https://duckdb.org/docs/stable/extensions/spatial/functions.html#st_hilbert https://duckdb.org/docs/stable/extensions/spatial/functions.... https://duckdb.org/community_extensions/extensions/h3.html https://duckdb.org/community_extensions/extensions/h3.html

wodenokoto 1y ago
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.
- deleted 1y ago
  [deleted]
- code_biologist 1y ago
  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 to go faster. `ST_Area(geography geog)` and it gives you the size of your shape in square meters wherever on the planet.

fidotron 1y ago

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

jsemrau 1y ago
Makes one wonder if the YOLO algorithm would work better with hexagons. "Hexagons were an important choice because people in a city are often in motion, and hexagons minimize the quantization error introduced when users move through a city. Hexagons also allow us to approximate radiuses easily, such as in this example using Elasticsearch." [Edit]Maybe https://www.researchgate.net/publication/372766828_YOLOv8_for_Defect_Inspection_of_Hexagonal_Directed_Self-Assembly_Patterns_A_Data-Centric_Approach https://www.researchgate.net/publication/372766828_YOLOv8_fo...
sroerick 1y ago
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

bingaweek 1y ago

We need a "come on" clause for these absurd headlines. Come on.

deleted 1y ago
[deleted]

yoyopa 1y ago

[dead]

badmonster 1y ago

How might embedding spatial capabilities directly into general-purpose data tools like DuckDB reshape who participates in geospatial analysis—and what kinds of problems they choose to solve?

jparishy 1y ago

I work on geospatial apps and the software I think I am most excited about is https://felt.com/ 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 stuff is and it's a lot of training to get people to up to speed on coordinate systems, projections, transformations, etc. I would replace a lot of my custom built mapping tools with Felt if it were possible, so I could focus on our core geospatial processes and not the code to display and play with it in the browser, which is almost as big if not bigger in terms of LOC to maintain. As mentioned by another commenter, this DuckDB DX as described is basically the same as PostGIS too.

dbreunig 1y ago
Author here: the beauty of DuckDB spatial is that the projections and CRS options are hidden until you need them. For 90% of geospatial data usage people don't and shouldn't need to know about projections or CRS. Yes, there are so many great tools to handle the complexity for the capital-G Geospatial work. I love Felt too! Sam and team have built a great platform. But lots of times a map isn't needed; an analyst just needs it as a column. PostGIS is also excellent! But having to start up a database server to work with data doesn't lend itself to casual usage. The beauty of DuckDB is that it's there in a moment and in reach for data generalists.
- jparishy 1y ago
  I think we're mostly making the same point about complexity, ya. To me, I think it's mostly a frontend problem stopping the spread of mapping in consumer apps. Backend geo is easy tbh. There is so much good, free tooling. Mapping frontend is hell and there is no good off the shelf solution I've seen. Some too low level, some too high level. I think we need a GIS-lite that is embeddable to hide the complexity and let app developers focus on their value add, and not paying the tax of having frontend developers fix endless issues with maps they don't understand. edit: to clarify, I think there's a relationship between getting mapping valued by leadership such that the geo work can be even be done by analysts, and having more mapping tools exist in frontend apps such that those leaders see them and understand why geo matters. it needs to be more than just markers on the map, with broad exposure. hence my focus on frontend web. sorry if that felt disjointed

patja 1y ago

SQL Server has geospatial capabilities without any extensions or add-ons. I've been happily using geospatial datatypes on the free Express version for years, probably well over a decade.

cyanydeez 1y ago

No. QGIS is. Good god.

dbreunig 1y ago
Author here. QGIS is amazing. It's really great. It also came out in 2002, so I think the headline is safe.
- cyanydeez 1y ago
  Nope, it's constantly being improved and still wins the decade. Do you disqualify it because it existed? Re-read your headline, it's definitely not qualifying what you think it's qualifying.

WD-42 1y ago

Uhoh, another pushover-licensed database. I wonder when it will begin it’s own redis saga.

Demiurge 1y ago

> Prior to this, getting up and running from a cold-start might’ve required installing or even compiling severall OSS packages, carefully noting path locations, standing up a specialized database… Enough work that a data generalist might not have bothered, or their IT department might not have supported it. I've been able to "CREATE EXTENSION postgis;" for more than a decade. There have been spatial extensions for PG, MySQL, Oracle, MS SQL Server, and SQLite for a long time. DuckDB doesn't make any material difference in how easy it is to install.

wenc 1y ago
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 Jupyter or a Python REPL and duckdb.query("from '*.parquet'") Correct me if I'm wrong, but I don't think that's possible with Postgis. (even pg_parquet requires copying? [1]) [1] https://www.crunchydata.com/blog/pg_parquet-an-extension-to-connect-postgres-and-parquet https://www.crunchydata.com/blog/pg_parquet-an-extension-to-...
- edoceo 1y ago
  Not wrong. Load to PG, then query. Duck UVP is like bringing 8 common tools/features under one tent.
- Demiurge 1y ago
  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 geospatial vector data format right now, is that common, and the article did not claim that either. So, given an average user trying to answer some GIS question, some ETL is pretty much a given, on average. And given that, installing PostGIS and installing DuckDB, both require some ETL, and learning some query and analytics language. DuckDB might be an improvement, but it's certainly not as much of a leap as quote is making it out to be.

jandrewrogers 1y ago

I think geospatial analytics is important (because of course I would), but to be frank geospatial software has been stagnant for a long time. Every new thing is just a fresh spin on the same stagnant things we already have. This more or less says exactly this? For geospatial analysis, the most important thing that could happen in software would be no longer treating it, either explicitly or implicitly, as having anything to do with cartography. Many use cases are not remotely map-driven but the tools require users to force everything through the lens of map-making.

azinman2 1y ago
Can you give some examples?
- jandrewrogers 1y ago
  Of the stagnation? I’ve been doing geospatial analytics for over 20 years and shockingly little has changed, both in features and capability. Given the amount of time that has passed and the vastly expanded scope of the geospatial data models people are working with today, I think most people would expect more to have changed.
  - azinman2 1y ago
    No I meant to this: “Many use cases are not remotely map-driven but the tools require users to force everything through the lens of map-making.”
dbreunig 1y ago
I was struck by this as people suggest alternatives that refute the headline (QGIS, PostGIS, GDAL, etc): nearly every one emerged in the early 2000s. Strongly agree with your sentiment around maps: most people can’t read them, they color the entire workflow and make it more complex, and (imo) lead to a general undervaluing of the geospatial field. Getting the data into columns means it’s usable by every department.

willtemperley 1y ago

I have some concerns regarding licensing of DuckDB and GEOS which DuckDB spatial depends on. The former is MIT licensed and the latter LGPL 2.1. This leads to some complex situations where some builds would contravene LGPL 2.1 e.g static linking with a closed source application.

kriro 1y ago

That's a pretty grandiose statement and frankly the kind of advertisement I'm not a fan of at all. If you want to import something and work with it GeoPandas exists. If you want something integrated with a SQL database, PostGIS exists. On the application side of the spectrum, GRASS GIS, QGIS etc. say hi. They are being used in the agriculture industry and by government agencies (at least I know that's the case in Germany and Brazil).

oreilles 1y ago

Chiming in to promote a similar project, a geospatial extension for Polars [1] I'm working on. It's not stable yet (abeit pretty close to), but is already pretty feature complete (it uses GEOS and PROJ as a backend, so has parity with GeoPandas). [1] https://github.com/oreilles/polars-st/ https://github.com/oreilles/polars-st/

aynyc 1y ago

How big are the data sets? I've been trying to get duckdb to work in our company on financial transactions and reporting data. The dataset is around 500GB CSV in S3 and duckdb chokes on it.

snake_doc 1y ago
Are you querying from an EC2 instance close to the S3 data? Are the CSVs partitioned into separate files? Does the machine have 500GB of memory? It’s not always duckdb fault when there can be a clear I/O bottleneck…
- aynyc 1y ago
  No, the EC2 instance doesn't have 500GB of data. Does DuckDB require that? I actually downloaded the data from S3 to local EBS and still choked.
  - broner 1y ago
    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-memory-vs-persistent-database https://duckdb.org/docs/stable/clients/cli/overview.html#in-...
    - aynyc 1y ago
      Wait, do you insert the data from S3 into duckdb? I was just doing select from file.
      - fastasucan 1y ago
        Maybe its your terminal that chockes because it tries to display to much data? 500GB should be no problem.
      - broner 1y ago
        Nope, just reading from S3. Check this out: https://duckdb.org/2024/07/09/memory-management.html https://duckdb.org/2024/07/09/memory-management.html

perrygeo 1y ago

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.

mpalmer 1y ago
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.
- perrygeo 1y ago
  Mainly a point of clarification - I don't think DuckDB represents anything new in geospatial. It represents a new paradigm for data management and data architecture. Understanding the difference isn't optional. DuckDB handles floating point numbers too - is DuckDB the most important thing in floating point data? Of course not, the data types and operators haven't changed. The underlying compute environment has. That's where the innovation is. I'd simply appreciate if technical writers took two seconds to make this vital distinction - being precise isn't hard and I don't know what we gain by publishing intentionally muddled technical articles.

serjester 1y ago

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 https://github.com/Filimoa/polars-h3

mettamage 1y ago
Is it possible with polars to store data more efficiently than a database though? I work with polars, but I haven't delved too deep into the performance characteristics versus postgres or anything like that.

fifilura 1y ago

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.

quasarj 1y ago

I've been out of the loop on DuckDB, where can I get a real overview of what the excitement is about? It just looks like a new sqlite from the CLI...

deleted 1y ago
[deleted]
Groxx 1y ago
It's essentially a column-oriented sqlite, so it's much faster for some kinds of queries. There's a big market for that category of queries, and nobody wants to run their own hadoop cluster to do single-machine-scale stuff.

elchief 1y ago

I wish to god they would allow 2 connections. One read-only for my BI tool, and one read-write for dbt/sqlmesh

kianN 1y ago
I think using a pyarrow dataset as an intermediary would allow for a zero copy read/write from one connection into a second connection.

vincnetas 1y ago

DBeaver (db client) has built in support for displaying geo data. In my case postgis results. I see that duckdb spatial functions are almost identical to postgis ones. https://dbeaver.com/docs/dbeaver/Working-with-Spatial-GIS-data/ https://dbeaver.com/docs/dbeaver/Working-with-Spatial-GIS-da...

dmillar 1y ago

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.