Getting started

Install DuckDB and open its SQL shell. DuckDB reads Parquet directly and automatically loads httpfs for HTTPS access. The later measurement and export steps install their own extensions.

Preview five objects from the national dataset:

SELECT *
FROM read_parquet(
  'https://cityparquet.open3d.city/data/3dbag/building.parquet')
LIMIT 5;

The URL points to a single file on object storage. There is no database server to connect to or import step to complete.

Count buildings across the Netherlands whose maximum roof elevation is more than 100 metres above their ground level:

SELECT COUNT(*) AS buildings_over_100m
FROM read_parquet(
  'https://cityparquet.open3d.city/data/3dbag/building.parquet')
WHERE object_type = 'Building'
  AND b3_h_dak_max - b3_h_maaiveld > 100;

Roof and ground elevations use the same vertical datum, so subtracting them gives height above ground, rather than elevation above sea level. See the 3DBAG height documentation. DuckDB needs only the object type and these two attributes; it does not fetch the 3D geometry for this question. Buildings with missing height values do not satisfy the filter.

Select a 1 km × 1 km window in Delft from the same national file and add the volumes of its valid LoD 2.2 solids. The coordinates are in the dataset’s Dutch national grid (EPSG:7415); the bounding-box filter includes objects overlapping the window, without clipping them at its edge.

INSTALL three_d FROM community;
LOAD three_d;

SELECT ROUND(SUM(ST_3DVolume(solid)), 0) AS total_volume_m3
FROM (
  SELECT ST_3DTryFromWKB(
    geometry_lod2_2, to_json(geometry_properties_lod2_2)) AS solid
  FROM read_parquet(
    'https://cityparquet.open3d.city/data/3dbag/building.parquet')
  WHERE bbox.xmin < 85000 AND bbox.xmax > 84000
    AND bbox.ymin < 447000 AND bbox.ymax > 446000
    AND geometry_lod2_2 IS NOT NULL
)
WHERE solid IS NOT NULL AND ST_3DValidationReport(solid).is_valid;

The result is a total in cubic metres, excluding solids that cannot be decoded or fail validation. CityParquet stores each level of detail in separate columns, so this query reads LoD 2.2 geometry and its companion properties without fetching other LoDs. The spatial filter also allows DuckDB to skip row groups outside the selected area.

Use the cityjson extension to write the same Delft window as a local CityJSONSeq file. Keep both buildings and building parts so the extract includes attributes and geometry:

INSTALL cityjson FROM community;
LOAD cityjson;

COPY (
  SELECT * FROM read_parquet(
    'https://cityparquet.open3d.city/data/3dbag/building.parquet')
  WHERE object_type IN ('Building', 'BuildingPart')
    AND bbox.xmin < 85000 AND bbox.xmax > 84000
    AND bbox.ymin < 447000 AND bbox.ymax > 446000
)
TO 'delft_window.city.jsonl'
  (FORMAT cityjsonseq, crs 'https://www.opengis.net/def/crs/EPSG/0/7415');

This export selects all columns for the matching objects, including their available LoDs, and writes the file in your current directory. For more detail, see Working with 3DBAG at cloud scale — Convert to CityJSONSeq.