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

151–160 of 177 posts

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

#151

Post by broner »

aynyc wrote: post #117
snake_doc wrote: post #109

Earlier quoted context omitted.

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…

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.

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

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

#152

Post by jofer »

groggo wrote: post #143
jofer wrote: post #116

Earlier quoted context omitted.

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

You can model polygons on a sphere, but the issue is that the data you're starting with is already in a cartesian representation. You actually can't easily convert between the two for complex geometries in cases where they cross the antimeridian/poles. So trying to do anything other than points is difficult in practice, unless you're natively generating data from scratch in a spherical representation, which is rate.

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

#153

Post by wenc »

aynyc wrote: post #119
nojito wrote: post #111

Earlier quoted context omitted.

CSV are a poor format to access from S3. Should convert them to parquet then access and analytics becomes cheap and fast.

I agree. That's how our data is produced. We constantly generate real time data into CSV. As far as I can tell, I can't append to parquet file.

Parquet files are already built for append only. Just add a new file.

This is a new paradigm for folks who aren’t in big data — the conventional approach usually involves doing a row INSERT. In big data, appending simply means adding a new file - the database engine will immediately recognize its presence. This is why “select * from ‘*.parquet’” will always operate on the latest dataset.

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

#154

Post by wenc »

aynyc wrote: post #107

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.

CSV is a pretty bad format any engine will choke on it. It basically requires a full table scan to get at any data.

You need to convert it into Parquet or some columnar format that lets engines do predicate pushdowns and fast scans. Each parquet file stores statistics about the data it contains so engines can quickly decide if it’s worth reading the file or skipping it altogether.

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

#155

Post by 8note »

wenc wrote: post #84
vasco wrote: post #77

Earlier quoted context omitted.

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.

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?

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

#156

Post by febed »

dbreunig wrote: post #17
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…

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…

Last I checked DuckDB spatial didn’t support handling projections. It couldn’t load the CRS from a .prj file. This makes it useless for serious geospatial stuff.

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

#157

Post by datadrivenangel »

ngrilly wrote: post #145
sroerick wrote: post #125

Earlier quoted context omitted.

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?

Graphics. Good BI tools are very effectively exploratory data analysis tools.

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

#158

Post by aynyc »

wenc wrote: post #153
aynyc wrote: post #119

Earlier quoted context omitted.

I agree. That's how our data is produced. We constantly generate real time data into CSV. As far as I can tell, I can't append to parquet file.

Parquet files are already built for append only. Just add a new file. This is a new paradigm for folks who aren’t in big data — the conventional approach usually involves doing a row INSERT. In big data, appending simply means adding a new file - the database engine will immediately recognize its presence. This is why “select * from ‘*.parquet’” will always operate on the latest dataset.

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

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

#159

Post by aynyc »

broner wrote: post #151
aynyc wrote: post #117

Earlier quoted context omitted.

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.

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.

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

#160

Post by wenc »

aynyc wrote: post #158
wenc wrote: post #153

Earlier quoted context omitted.

Parquet files are already built for append only. Just add a new file. This is a new paradigm for folks who aren’t in big data — the conventional approach usually involves doing a row INSERT. In big data, appending simply means adding a new file - the database engine will immediately recognize its presence. This is why “select * from ‘*.parquet’” will always operate on the latest dataset.

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/000.parquet
  /data/yearwk=202502/000.parquet
This is also called the Hive directory structure. When I query, I just do:

  select * from '/data/**/*.parquet';
This is a paradigm shift from standard database thinking for handling truly big data. It's append-only by file.

500GB in CSVs doesn't sound that big though. I'm guessing when you convert to Parquet (a 1-liner in DuckDB, below) it might end up being 50GBs or so.

  COPY (FROM '/data/*.csv') TO 'my.parquet' (FORMAT PARQUET);
Post reply on HN