How to download building data in a specified region?

[amrirasyidi]

Hi, I'm curious is there a way to download the building data only in a specified region (either bounding box, geometry, or tags) using duckdb?

I'm not very experienced with s3, so I don't know what to add to the example here s3://overturemaps-us-west-2/release/2023-10-19-alpha.0/theme=buildings

[mtravis]

Take a look at this example you could modify that for buildings

You will have install the spatial extension like below. Also good practice to set the region though I'm not sure if it speeds up the query.

INSTALL spatial;
LOAD spatial;
SET s3_region='us-west-2';

[jenningsanderson]

@amrirasyidi, here is an example using DuckDB to access the latest Overture buildings data on Microsoft Azure:

LOAD spatial;
LOAD azure;

-- Access the data on Microsoft Azure in this example
SET azure_storage_connection_string = 'DefaultEndpointsProtocol=https;AccountName=overturemapswestus2;AccountKey=;EndpointSuffix=core.windows.net';
COPY (
     SELECT
         FILTER(names.common, x -> x.language = 'local')[1].value as name,
         height,
         level,
         CAST(sources AS JSON) as sources,
         ST_GeomFromWKB(geometry) as geometry
     FROM read_parquet('azure://release/2023-11-14-alpha.0/theme=buildings/type=building/*', filename=true, hive_partitioning=1)
     WHERE bbox.minX > -122.679404 AND bbox.maxX < -121.978275
       AND bbox.minY > 47.360619 AND bbox.maxY < 47.786336
) TO 'buildings.geojsonseq'
WITH (FORMAT GDAL, DRIVER 'GeoJSONSeq', SRS 'EPSG:4326');

Note that there are a lot of buildings and DuckDB does not yet optimize for the above query, so this will take a long time — but it will work!

[jenningsanderson]

Update — see examples of this at docs.overturemaps.org. Today, the data is optimized for these types of queries.