Back when I was working with shapefiles, it was the type of things that tended to be far more convenient to process in-process using something like GDAL [1] (which can operate directly on an in memory copy, gzip files, sqlite databases and far more) and query it with GDAL's SQL support, especially when build with Spatialite [2] rather than loading it into a separate database. It'd have been interesting if the author had talked about what's stopping him from that approach given he's clearly aware of GDAL and given that 130M records and a few tens of GB isn't a particularly big GIS dataset.
Last year I found ClickHouse did H3 enrichment much faster than PG or BQ so it has become a go-between for larger workloads with simple enrichment requirements.
The dataset in the post is an example anyone can download. I handle 30B+ records at work.
Very cool to see a walkthrough with actual benchmarks. Not entirely surprised that Parquet shines here. Another big advantage of Parquet over CSV is that you don't have to worry about data integrity. Perhaps less relevant for GIS data, but not having to think about things like string escaping is rather nice.
"It would be great to see data vendors deliver data straight into the Cloud Databases of their customers. It would save a lot of client time that's spent converting and uploading files."
Hear hear! Shameless plug: this is exactly what we enable at prequel.co. If there are any data vendors reading this, or anyone who wants easier access to data from their vendor, we're here to help.
I really love working with parquet and the general arrow ecosystem. The performance and cost ratios you can get out of it are really insane. AWS S3 + parquet + athena is one of the best and cheapest databases I've ever used.
Can we get any of Iceberg/Delta/Hudi that isn't terribly complex to setup? Like configurable completely from Standard SQL via the CREATE EXTERNAL TABLE syntax.
Anything to go the other way? I’d like to use BQ to warehouse and be able to examine but PG to do heavy analytics due to the cost once you really start doing many repeated queries.
I guess I could just dump directly to CSVs and download but BQ is a nice convenient bottomless data bucket.
do you already have an ETL pipeline to BQ? You could use Airbyte[0] for pg to bq. It's pretty heavy if this is your only source, but should work well if you need to query the pg data in bq frequently.
Nice to read this, I had a similar type of assignment 15 years ago, visualize the rollout of the fiber optics network across the city. But we had a lot less data to deal with.
[1] https://gdal.org
[2] https://www.gaia-gis.it/fossil/libspatialite/index