DuckDB

DuckDB 是一款分析工具，允許您查詢遠端檔案並僅下載您所需的資料。

安裝

在本地安裝 DuckDB。您將需要擴充套件功能來處理雲端的空間資料。使用 DuckDB CLI，請執行以下操作：

1. INSTALL SPATIAL; 來安裝 duckdb_spatial 擴充套件。
2. INSTALL httpfs; 或 INSTALL azure; 來從 Amazon S3 (httpfs) 或 Microsoft Azure Blob Storage (azure) 讀取資料。

範例查詢

山脈

此查詢從 Overture 地點資料集中選擇山脈類別的 POI，並將其輸出到 GeoJSON 中。

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

COPY(
    SELECT
       id,
       names.primary as primary_name,
       bbox.xmin as x,
       bbox.ymin as y,
       ST_GeomFromWKB(geometry) as geometry,
       categories.primary as main_category,
       sources[1].dataset AS primary_source,
       confidence
    FROM read_parquet('s3://overturemaps-us-west-2/release/2024-07-22.0/theme=places/type=*/*', filename=true, hive_partitioning=1)
    WHERE main_category = 'mountain' AND confidence > .90
    ORDER BY confidence DESC
) TO 'overture_places_mountains_gt90.geojson'
WITH (FORMAT GDAL, DRIVER 'GeoJSON');

提示

To write the data to a shapefile, replace the last two lines with:

    ) TO 'overture_places_mountains_gt90.shp'
WITH (FORMAT GDAL, DRIVER 'ESRI Shapefile');

在底特律的建築

此查詢從 Overture 建築資料集中抓底特律地區的建築幾何形狀和選定屬性。

LOAD spatial;
LOAD azure;
SET azure_storage_connection_string = 'DefaultEndpointsProtocol=https;AccountName=overturemapswestus2;AccountKey=;EndpointSuffix=core.windows.net';

SELECT
  id,
  names.primary as primary_name,
  height,
  ST_GeomFromWKB(geometry) as geometry
FROM read_parquet('azure://release/2024-07-22.0/theme=buildings/type=*/*', filename=true, hive_partitioning=1)
WHERE primary_name IS NOT NULL
AND bbox.xmin > -84.36
AND bbox.xmax < -82.42
AND bbox.ymin > 41.71
AND bbox.ymax < 43.33;

行政區輪廓

此查詢從 divisions 資料集中抓賓夕法尼亞州的縣的幾何形狀，並將其輸出到 GeoJSON 中。

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

SELECT 
    id,
    division_id,
    names.primary,
    ST_GeomFromWKB(geometry) as geometry
    FROM
        read_parquet('s3://overturemaps-us-west-2/release/2024-07-22.0/theme=divisions/type=division_area/*', hive_partitioning=1)
    WHERE
        subtype = 'county'
        AND country = 'US'
        AND region = 'US-PA'