Can I use DuckDB to access the GeoParquet building footprint data?

[cholmes]

Hey there, I'm trying to figure out how to access data on PC, specifically the building footprints GeoParquet. I'd like to add it to this little command-line tool I'm working on, see opengeos/open-buildings#27

DuckDB does most of my heavy lifting, and they just came out with azure support, see https://duckdb.org/docs/extensions/azure I've spent ~10 minutes trying to figure out how to get my 'connection string', but don't feel any closer. I've never quite gotten the hang of azure and credentials - not sure how to even do the basics, and not sure if I need something more for planetary computer, but I think I have access?

So help is much appreciated. And ideally I'd have an easy route for users of the CLI to query microsoft buildings, perhaps they could add a connection string as an argument, or maybe there's a route that doesn't need the connection string.

thanks!

[TomAugspurger]

Sure, it's a bit confusing. I'll download DuckDB and give it at shot, but in the meantime, you need

1. The storage account (from the STAC metadata, msft:storage_account)
2. The storage container (from the STAC metadata, msft:container)
3. The short-lived read+list SAS token (kind of like a signed URL from S3 I think), which you get from our SAS API.

Putting that together:

In [4]: import requests

In [5]: collection = requests.get("https://planetarycomputer.microsoft.com/api/stac/v1/collections/ms-buildings").json()

In [6]: credential = requests.get(f"https://planetarycomputer.microsoft.com/api/sas/v1/token/{collection['msft:storage_account']}/{collection['msft:container']}").json()["token"]

In [8]: connection_string = f"https://{collection['msft:storage_account']}.blob.core.windows.net/{collection['msft:container']}?{credential}"

but I think I have access?

Just to be clear, everyone has access through that SAS API. Anyone can generate those short-lived SAS tokens (providing your Planetary Computer credentials to SAS API will give you longer access).

And ideally I'd have an easy route for users of the CLI to query microsoft buildings, perhaps they could add a connection string as an argument,

I'm happy to work with others to figure out what this should look like. We've made some choices in the STAC metadata that make things work especially smoothly for the Python / fsspec side of things. Happy to add additional metadata for others.

[cholmes]

That's too bad. Thanks a ton for investigating. It's a brand new extension, so they probably haven't gotten to all the different options. I'll try to ask what they support and if they could do the SAS tokens

And yeah, makes sense that you need '*.parquet', for my queries I usually do some sort of * matching.

[TomAugspurger]

Here's a single file working from Python:

import planetary_computer
import duckdb

token = planetary_computer.sas.get_token("bingmlbuildings", "footprints").token
df = duckdb.query(f"""
select *
from read_parquet('https://bingmlbuildings.blob.core.windows.net/footprints/delta/2023-04-25/RegionName%3DAbyei/quadkey%3D122321003/part-00440-2a6245e4-3990-4c55-a4ce-682dacd87f8b.c000.snappy.parquet?{token}')
limit 10
"""
).df()
print(df)

Since you can access single files from Blob Storage over HTTP, this works. But things like globbing don't work.

[TomAugspurger]

duckdb/duckdb_azure#8 is the upstream issue. Something with curl.

[cholmes]

Cool. I'll sound in the upstream issue to say that it'd help out for us too.

Glad to see the blob storage access working, I'll try that out. Does azure support s3 compatibility? If so then one way forward is to use the httpfs module. I know with GCP you can do that, but not sure if Azure.

I'm in no rush on this one, so hopefully DuckDB fixes it. It seems like a more friendly route if they get it working.

[TomAugspurger]

Does azure support s3 compatibility?

Nope, just the native Azure APIs and HTTP :)

[cholmes]

Just to be clear, everyone has access through that SAS API. Anyone can generate those short-lived SAS tokens (providing your Planetary Computer credentials to SAS API will give you longer access).

Ah, great. Good to know - I'm never quite sure if I'm just doing it totally wrong or if my account doesn't have the right access. Good to know it'll always be the former ;)

I'm happy to work with others to figure out what this should look like. We've made some choices in the STAC metadata that make things work especially smoothly for the Python / fsspec side of things. Happy to add additional metadata for others.

Cool. I've got a blog post mostly drafted that goes deep into my findings to make things work better. Since your work was the jumping off point most all is completely compatible, with some tweaks on the client side. You've got hive partitions on the quadkey which I don't, which could be interesting. I did country_iso instead of RegionName, but I think both should work. Right now the user has to supply the country_iso, so they'd need to supply the regionname, to get fast queries. But I'm hoping to try out automatically calculating country_iso by quadkey, see opengeos/open-buildings#29 and if that works then it seems like it'd be easy to add a regionname column too. Your level 9 quadkeys seem more reasonable for that, I did 12 as I was mostly thinking about using it within a parquet file when I made it.