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

71–80 of 177 posts

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

#71

Post by Demiurge »

groggo wrote: post #64
jandrewrogers wrote: post #49

Earlier quoted context omitted.

> it's a lot of training to get people to up to speed on coordinate systems, projections, transformations, etc This can mostly be avoided entirely with a proper spheroidal reference system, computational geometry implementation, and indexing. Most uses of geospatial analytics are not cartographic in nature. The map is at best a presentation layer, it is not the data model, and some don’t use a map at all. Forcing peo…

> 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?

This is not really a problem, unless you’re trying to simulate some 3D space orbits, physics. The crossover from geo INFORMATION systems to geo simulation systems is a bit rough, but the projections and calculations on projected cartesian space are enough for many typical questions, like distance, area, routing. However, even topology support starts getting specialized, and the use cases are more niche. I think it’s asking a bit too much from a database/storage layer to do efficient calculations outside of those supported by GEOS. At this point, you might want to import the relevant data into higher level applications.

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

#72

Post by [deleted] »

Demiurge wrote: post #45
dbreunig wrote: post #22

Earlier quoted context omitted.

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; ```

What do you mean by "provisioning a server"? That's a strange requirement. You can install Postgis on a macbook in one command, or actually on all 3 major OS's in one command: "brew install postgis", "apt-get install postgresql-postgis, and "choco install postgis-9.3". Does DuckDB not require a "server" or a "computer"? What does Docker have to do with anything? This is a very confusing train of thought.

[deleted]

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

#73

Post by azinman2 »

jandrewrogers wrote: post #58
azinman2 wrote: post #57

Earlier quoted context omitted.

Can you give some examples?

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.

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

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

#74

Post by jandrewrogers »

groggo wrote: post #64
jandrewrogers wrote: post #49

Earlier quoted context omitted.

> it's a lot of training to get people to up to speed on coordinate systems, projections, transformations, etc This can mostly be avoided entirely with a proper spheroidal reference system, computational geometry implementation, and indexing. Most uses of geospatial analytics are not cartographic in nature. The map is at best a presentation layer, it is not the data model, and some don’t use a map at all. Forcing peo…

> 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?

Many do use simple geometry. This causes endless headaches for people who are not cartographers, they don’t expect that. The good geospatial tools usually support spheroidal models but it is not the default, you have to know to explicitly make sure it uses that (many people assume that is the default).

An additional issue is that the spheroidal implementations have undergone very little optimization, perhaps because they are not the defaults. So when people figure out how to turn them on, performance is suddenly terrible. Now you have people that believe spheroidal implementations are terribly slow, when in reality they just used a pathologically slow implementation. Really good performance-engineered spheroidal implementations are much faster than people assume based on the performance of open source implementations.

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

#75

Post by jandrewrogers »

Demiurge wrote: post #71
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?

This is not really a problem, unless you’re trying to simulate some 3D space orbits, physics. The crossover from geo INFORMATION systems to geo simulation systems is a bit rough, but the projections and calculations on projected cartesian space are enough for many typical questions, like distance, area, routing. However, even topology support starts getting specialized, and the use cases are more niche. I think it’s…

Speaking for myself, I was not referring to any kind of simulation systems. This is a standard requirement of many operational geospatial data models, and there are a lot of these in industry. Anything that works from a projection is a non-starter, this causes demonstrable issues for geospatial analysis at scale or if any kind of precision is required. Efficient calculation just means efficient code, there is nothing preventing this from existing in open source beyond people writing it. Yes, you may be able to get away with it if your data model is small, both geographically and data size, but that does not describe every company.

It is entirely possible to do this in databases. That is how it is actually done. The limitations of GEOS are not the limitations of software, it is not a particularly sophisticated implementation (even PostGIS doesn’t use it for the important parts last I checked). To some extent you are affirming that there is a lack of ambition in this part of the market in open source.

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

#76

Post by vasco »

wenc wrote: post #47
dopidopHN wrote: post #40

Earlier quoted context omitted.

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

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…

I haven't yet understood this pattern (and I tried using duckdb). Unless you're only ever going to query those files once or twice in your life, importing them into postgres shouldn't be that long and then you can do the same or more than with DuckDB.

Also as a side note, is everyone just using DuckDB in memory? Because as soon as you want some multiple session stuff I'd assume you'd use DuckDB on top of a local database, so again I don't see the point but I'm sure I'm missing something.

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

#77

Post by vasco »

wenc wrote: post #46
tmpz22 wrote: post #26

Earlier quoted context omitted.

I've been researching DuckDB - while it has many technical merits I think the main argument will be ease of use. It has a lot of the operational advantages of sqlite paired with strong extensibility and good succinct documentation. Folks who have been doing DevOps work are exasperated with crummy SaaS vendors or antiquated OSS options that have a high setup cost. DuckDB is just a mature project that offers an alterna…

How is the adoption among DevOps folks? I'm still getting feedback that many devs are not too comfortable with reading and writing SQL. They learned simple SELECT statements in school, but get confused by JOINs and GROUP BYs.

There's no point in learning any much deeper SQL anymore, AI assistants have largely solved SQL querying. Just ask for what you want with natural language.

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

#79

Post by ffsm8 »

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…

> fundamental problem with the SQL pattern.

SQL is a DSL and yes, all Domain Specific Languages will only enable what the engine parsing the DSL supports.

But all SQL database I'm aware of let you write custom extensions, which are exactly that: they extend the base functionality of the database with new paradigms. I.e postgis enabling geospatial in postgres or the extensions that enable fuzzy-matching/searching.

And as SQL is pretty much a turing-complete DSL, there is very little you can't do with it, even if the syntax might not agree with everyone

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

#80

Post by usr1106 »

vasco wrote: post #77
wenc wrote: post #46

Earlier quoted context omitted.

How is the adoption among DevOps folks? I'm still getting feedback that many devs are not too comfortable with reading and writing SQL. They learned simple SELECT statements in school, but get confused by JOINs and GROUP BYs.

There's no point in learning any much deeper SQL anymore, AI assistants have largely solved SQL querying. Just ask for what you want with natural language.

While AI assistants can give ideas how to address something I'd strongly contradict the statement "there is no point in learning". You should understand the AI suggestion because it could also be very bad or working incorrectly. (If it hallunicated, you'll note the syntax error, that does indeed not require learning...)
Post reply on HN