Mastodawn

Simon Willison Jul 27, 2023

I used DuckDB to extract data from the released parquet files, then loaded that into SQLite so I could use it with @datasette

Here's a demo I built with just the data from the places city for the city of Half Moon Bay - 931 listings in total: https://hmb-overture-demo.vercel.app/hmb/places

hmb: places: 931 rows

Brad Larsen Jul 27, 2023

@simon @datasette I enjoy using duckdb!

@simon @datasette great write-up, thanks.

What's your impression as a resident of Half Moon Bay?

Is the data correct? How fresh is it? How does it compare with OSM?

@simon @datasette umm, is there actually a restaurant / cinema on the water in the middle of the bay?

@simon @datasette in fairness I guess the do give it a low confidence score, so could exclude all that fall below a certain threshold

@freyfogle @datasette it looks like that's the Mavericks surf competition, so yeah that's the right spot

@simon @datasette sure, but why is it labeled as a resturant?

@freyfogle @datasette yeah those categories are very clearly off!

@freyfogle @datasette I haven't compared to OSM yet - my initial spot check for my favourite places all looked correct to me

Ian Dees Jul 27, 2023

@simon Thanks for sharing out the Parquet querying parts of this!

Ian Dees Jul 27, 2023

@simon I mean all of it is great, but getting data of Parquet is more difficult than it should be, so I appreciate that part especially :)

Eugene Alvin Villar 🇵🇭Jul 27, 2023

@simon kinda sucks that you need to download the whole thing. Maybe using the Athena or Azure routes would allow faster selects than DuckDB?

Simon Willison Jul 27, 2023

@seav You don't have to download the whole thing for a bunch of operations - but the "find places within this bounding box" thing does seem to be too much for the remote HTTP mechanism to handle quickly

A problem I have is that I don't have good instincts yet for figuring out if a query is likely to work well over remote Parquet or not

jwass2000 Jul 27, 2023

@simon @seav This is awesome! There are some tricks we can use to structure the parquet files that allow more efficient bounding box queries using remote predicate pushdown. It’s all pretty new for spatial parquet data. We’ll probably look at this for future releases along with easier country/region partitioning.

@jwass2000 @[email protected] That would be fantastic - I'm very new to Parquet/DuckDB myself so any extra documentation from Oversight illustrating the kinds of queries you can run against it without downloading GBs of data would be really useful

Ben Hur Jul 28, 2023

@simon Thank you for this! I also downloaded the entire dataset and was able to extract data for the Philippines (bounding box) using the approach you shared. 👍

Joost Schouppe Jul 28, 2023

@benhur07b @simon do you take requests? :)

Eugene Alvin Villar 🇵🇭Jul 28, 2023

@benhur07b my question is, why are there dots all over the seas?