{ "cells": [ { "cell_type": "markdown", "id": "35925fd2-49f3-4d79-aae3-6a7a382262f6", "metadata": {}, "source": [ "# 3. Building the Analytical Foundation: Spatial Join and Aggregation\n", "\n", "## 3.1 The Challenge\n", "\n", "In {doc}`2. Analyzing Building Stock Patterns <203b_analysis>`, we worked with pre-aggregated data — no need to process the full 5 GB dataset. But how did we get from **57 million building footprints** to the analytical dataset used in analysis?\n", "\n", "This chapter documents the first step of the pipeline:\n", "\n", "- **Spatial join** — assigning each footprint to its municipality (Gemeinde)\n", "- **Aggregation** — summing footprints to municipality level (`ags`)\n", "\n", "**Why we document this pipeline:**\n", "\n", "- **Reproducibility** — best practice for open science\n", "- **Showcasing** — how to work with large GeoParquet datasets efficiently using DuckDB\n", "- **Inspiration** — a potential template for your own large-scale analyses\n", "\n", "> **Note:** This chapter is documentation only. The code is provided for full reproducibility but requires the complete dataset (~5 GB). The analysis in Chapters 1–2 uses already aggregated data. In **Section 3.6**, we provide performance considerations based on the hardware used. In **Section 3.7**, we guide you through downloading the full dataset.\n", "\n", "## 3.2 Setting Up the Processing Environment\n", "\n", "We use **DuckDB** — an in-process analytical database that:\n", "- Handles large spatial data efficiently\n", "- Uses spatial indexes (R-tree) for fast lookups\n", "- Reads GeoParquet files directly from disk\n", "- Processes data in parallel\n", "\n", "### Setup\n", "\n", "First, we set up DuckDB with the spatial extension." ] }, { "cell_type": "code", "execution_count": 2, "id": "08f56935-8aad-42ae-92e8-de082fb926e8", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "DuckDB ready with spatial extension.\n" ] } ], "source": [ "# ============================================================\n", "# Setup DuckDB with spatial extension\n", "# ============================================================\n", "import duckdb\n", "from pathlib import Path\n", "\n", "# Define root directory\n", "ROOT = Path.cwd().parent\n", "\n", "# Paths\n", "FOOTPRINTS_DIR = ROOT / \"data\" / \"raw\" / \"3D_building_metrics_germany_2024\"\n", "VG25_GPKG_PATH = ROOT / \"data\" / \"raw\" / \"VG25\" / \"Daten\" / \"DE_VG25.gpkg\"\n", "GEM_LAYER = \"vg25_gem\"\n", "\n", "# Filter: only buildings with function code starting with '31'\n", "BLDG_FUNCTION_PREFIX = \"31\"\n", "\n", "# Connect to DuckDB (in-memory)\n", "con = duckdb.connect(database=\":memory:\")\n", "\n", "# Load spatial extension\n", "con.execute(\"INSTALL spatial;\")\n", "con.execute(\"LOAD spatial;\")\n", "\n", "# Performance settings\n", "con.execute(\"SET memory_limit='8GB';\")\n", "con.execute(\"SET threads TO 8;\")\n", "con.execute(f\"SET temp_directory='{(ROOT / 'tmp_duckdb').as_posix()}';\")\n", "\n", "print(\"DuckDB ready with spatial extension.\")" ] }, { "cell_type": "markdown", "id": "8c5a16d4-c161-4d62-824b-70dc1a2daa14", "metadata": {}, "source": [ "### Load Administrative Boundaries\n", "\n", "We load the VG25 municipality boundaries (Gemeinden) into DuckDB and add a spatial index for fast joins.\n", "\n", "**Data source:** VG25 (Verwaltungsgebiete 1:25 000), © BKG (2025) CC BY 4.0, reference date 31.12.2024. \n", "**Download:** [https://daten.gdz.bkg.bund.de/produkte/vg/vg25_ebenen/aktuell/vg25.utm32s.gpkg.zip](https://daten.gdz.bkg.bund.de/produkte/vg/vg25_ebenen/aktuell/vg25.utm32s.gpkg.zip) \n", "**Quellenvermerk:** © BKG (2025) CC BY 4.0, Datenquellen: [https://sgx.geodatenzentrum.de/web_public/gdz/datenquellen/datenquellen_vg25.pdf](https://sgx.geodatenzentrum.de/web_public/gdz/datenquellen/datenquellen_vg25.pdf) \n", "**Terms of use:** [http://sg.geodatenzentrum.de/web_public/nutzungsbedingungen.pdf](http://sg.geodatenzentrum.de/web_public/nutzungsbedingungen.pdf)\n", "\n", "> **Note:** The data provided in this repository has reference date **31.12.2024**. If you download the dataset via the link above, the reference date may be newer.\n" ] }, { "cell_type": "code", "execution_count": 3, "id": "3154c7b1-1feb-4812-8efa-f1c4b408c575", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Loaded 10,981 Gemeinden with spatial index.\n" ] } ], "source": [ "# ============================================================\n", "# Load administrative boundaries (Gemeinden)\n", "# ============================================================\n", "con.execute(f\"\"\"\n", " CREATE OR REPLACE TEMP TABLE gemeinden AS\n", " SELECT\n", " AGS,\n", " GEN,\n", " geom AS geom_t\n", " FROM st_read('{VG25_GPKG_PATH.as_posix()}', layer='{GEM_LAYER}')\n", "\"\"\")\n", "\n", "# Add spatial index for faster joins\n", "con.execute(\"CREATE INDEX gem_geom_idx ON gemeinden USING RTREE (geom_t);\")\n", "\n", "n_gemeinden = con.execute(\"SELECT COUNT(*) FROM gemeinden\").fetchone()[0]\n", "print(f\"Loaded {n_gemeinden:,} Gemeinden with spatial index.\")" ] }, { "cell_type": "markdown", "id": "4a696a83-ecbc-4147-aa10-4a73f7ffdfe6", "metadata": {}, "source": [ "### Add State Codes\n", "\n", "We add state codes to prevent cross-state assignments during the spatial join." ] }, { "cell_type": "code", "execution_count": 4, "id": "72c66af3-857e-40ea-83c1-7fdf0e4635f9", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Boundaries ready with state_code and spatial index.\n" ] } ], "source": [ "# ============================================================\n", "# Add state codes via AGS lookup\n", "# ============================================================\n", "con.execute(\"\"\"\n", " CREATE OR REPLACE TEMP TABLE ags_lookup (\n", " ags_prefix VARCHAR,\n", " short_id VARCHAR,\n", " bundesland VARCHAR\n", " )\n", "\"\"\")\n", "\n", "con.execute(\"\"\"\n", " INSERT INTO ags_lookup VALUES\n", " ('01', 'sh', 'Schleswig-Holstein'),\n", " ('02', 'hh', 'Hamburg'),\n", " ('03', 'ni', 'Niedersachsen'),\n", " ('04', 'hb', 'Bremen'),\n", " ('05', 'nw', 'Nordrhein-Westfalen'),\n", " ('06', 'he', 'Hessen'),\n", " ('07', 'rp', 'Rheinland-Pfalz'),\n", " ('08', 'bw', 'Baden-Württemberg'),\n", " ('09', 'by', 'Bayern'),\n", " ('10', 'sl', 'Saarland'),\n", " ('11', 'be', 'Berlin'),\n", " ('12', 'bb', 'Brandenburg'),\n", " ('13', 'mv', 'Mecklenburg-Vorpommern'),\n", " ('14', 'sn', 'Sachsen'),\n", " ('15', 'st', 'Sachsen-Anhalt'),\n", " ('16', 'th', 'Thüringen')\n", "\"\"\")\n", "\n", "# Add state_code to gemeinden\n", "con.execute(\"\"\"\n", " CREATE OR REPLACE TEMP TABLE gemeinden AS\n", " SELECT\n", " g.AGS,\n", " g.GEN,\n", " g.geom_t,\n", " l.short_id AS state_code\n", " FROM gemeinden g\n", " LEFT JOIN ags_lookup l\n", " ON substr(g.AGS, 1, 2) = l.ags_prefix\n", "\"\"\")\n", "\n", "# Re-add spatial index\n", "con.execute(\"CREATE INDEX gem_geom_idx ON gemeinden USING RTREE (geom_t);\")\n", "\n", "print(\"Boundaries ready with state_code and spatial index.\")" ] }, { "cell_type": "markdown", "id": "1453b3b6-a251-442a-b8b7-bb1bc3fdae7f", "metadata": {}, "source": [ "## 3.3 Spatial Join (Exact Match Only)\n", "\n", "We assign each building footprint to its municipality using a **centroid-in-polygon** join, restricted to the same federal state.\n", "\n", "**How it works:**\n", "\n", "1. We already loaded the municipal geometries from the **VG25 dataset** (BKG) into DuckDB (table `gemeinden`), with state codes added.\n", "2. We now iterate over the **16 GeoParquet partition files** (one per federal state).\n", "3. For each partition:\n", " - We compute the **centroid** of each building footprint.\n", " - We join against the `gemeinden` table, restricted to the **same federal state** (`state_code` match).\n", " - We use `ST_Within()` to check if the centroid falls inside a municipality polygon.\n", "4. Results are stored in a DuckDB table called `footprints_with_ags` — one row per building footprint, with the assigned municipality code (`ags_vg25`) and name (`gemeinde_name`).\n", "\n", "This approach is efficient because:\n", "- The spatial index on `gemeinden` speeds up the join.\n", "- The state-code restriction reduces the number of candidate polygons per footprint.\n", "- GeoParquet files are read directly from disk — no need to load everything into memory.\n", "We assign each building footprint to its municipality using a **centroid-in-polygon** join, restricted to the same federal state.\n", "\n", "**Why no fallback?**\n", "\n", "A fallback join (e.g., nearest-neighbor within a search radius) could catch footprints near borders. However, for the scope of this analysis, we accept a small number of unmatched footprints. This keeps the pipeline simple and transparent.\n", "\n", "### Step 1: Exact Join (Centroid-in-Polygon)\n", "\n", "We compute the centroid of each building footprint and check which municipality polygon contains it — within the same federal state." ] }, { "cell_type": "code", "execution_count": 5, "id": "632861fc-a5a3-4a37-87fd-565aae8c2279", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[1/16] bb_3d_building_metrics_2024 ... 2,356,964 assigned, 24 unmatched, 11.8s\n", "[2/16] be_3d_building_metrics_2024 ... 531,235 assigned, 3 unmatched, 7.4s\n", "[3/16] bw_3d_building_metrics_2024 ... 5,943,879 assigned, 14 unmatched, 22.5s\n", "[4/16] by_3d_building_metrics_2024 ... 9,154,228 assigned, 24 unmatched, 126.8s\n", "[5/16] hb_3d_building_metrics_2024 ... 271,076 assigned, 5 unmatched, 8.8s\n", "[6/16] he_3d_building_metrics_2024 ... 4,913,434 assigned, 24 unmatched, 35.9s\n", "[7/16] hh_3d_building_metrics_2024 ... 373,961 assigned, 26 unmatched, 16.8s\n", "[8/16] mv_3d_building_metrics_2024 ... 1,148,730 assigned, 8 unmatched, 4.1s\n", "[9/16] ni_3d_building_metrics_2024 ... 5,960,119 assigned, 20 unmatched, 81.1s\n", "[10/16] nw_3d_building_metrics_2024 ... 9,852,577 assigned, 27 unmatched, 378.4s\n", "[11/16] rp_3d_building_metrics_2024 ... 2,985,816 assigned, 1 unmatched, 40.5s\n", "[12/16] sh_3d_building_metrics_2024 ... 1,912,878 assigned, 25 unmatched, 67.3s\n", "[13/16] sl_3d_building_metrics_2024 ... 703,726 assigned, 11 unmatched, 17.0s\n", "[14/16] sn_3d_building_metrics_2024 ... 2,133,702 assigned, 0 unmatched, 89.8s\n", "[15/16] st_3d_building_metrics_2024 ... 1,747,083 assigned, 15 unmatched, 56.1s\n", "[16/16] th_3d_building_metrics_2024 ... 2,141,430 assigned, 28 unmatched, 69.0s\n", "\n", "============================================================\n", "Spatial join complete — Exact match results\n", "============================================================\n", "Total footprints processed: 52,131,093\n", "Assigned (exact match): 52,130,838 (99.999511%)\n", "Unmatched: 255 (0.000489%)\n" ] } ], "source": [ "# ============================================================\n", "# Step 1: Exact centroid-in-polygon join (all 16 states)\n", "# ============================================================\n", "# This code is provided for full reproducibility.\n", "# It requires the complete dataset (~5 GB).\n", "\n", "import time\n", "\n", "# Get all partition files\n", "partition_files = sorted(FOOTPRINTS_DIR.glob(\"*.parquet\"))\n", "\n", "# Track unmatched footprints\n", "unmatched_count = 0\n", "total_count = 0\n", "\n", "# Flag: first partition creates the table\n", "first_partition = True\n", "\n", "for i, pf in enumerate(partition_files, 1):\n", " t0 = time.time()\n", " \n", " state_name = pf.stem\n", " short_id = state_name.split(\"_\")[0]\n", " \n", " print(f\"[{i}/{len(partition_files)}] {state_name} ...\", end=\" \", flush=True)\n", "\n", " sql = f\"\"\"\n", " SELECT\n", " f.bldg_gmlid,\n", " f.bldg_function,\n", " f.bldg_volume,\n", " f.roof_area,\n", " f.footprint_area,\n", " g.AGS AS ags_vg25,\n", " g.GEN AS gemeinde_name,\n", " '{state_name}' AS source_partition,\n", " '{short_id}' AS state_code\n", " FROM (\n", " SELECT\n", " bldg_gmlid,\n", " bldg_function,\n", " bldg_volume,\n", " roof_area,\n", " footprint_area,\n", " ST_Centroid(geometry) AS centroid\n", " FROM read_parquet('{pf.as_posix()}')\n", " WHERE bldg_function LIKE '{BLDG_FUNCTION_PREFIX}%'\n", " ) f\n", " LEFT JOIN gemeinden g\n", " ON g.state_code = '{short_id}'\n", " AND ST_Within(f.centroid, g.geom_t)\n", " \"\"\"\n", "\n", " if first_partition:\n", " con.execute(f\"CREATE TABLE footprints_with_ags AS {sql}\")\n", " first_partition = False\n", " else:\n", " con.execute(f\"INSERT INTO footprints_with_ags {sql}\")\n", "\n", " # Count unmatched for this partition\n", " n_unmatched = con.execute(f\"\"\"\n", " SELECT COUNT(*) FROM footprints_with_ags\n", " WHERE source_partition = '{state_name}' AND ags_vg25 IS NULL\n", " \"\"\").fetchone()[0]\n", "\n", " n_total = con.execute(f\"\"\"\n", " SELECT COUNT(*) FROM footprints_with_ags\n", " WHERE source_partition = '{state_name}'\n", " \"\"\").fetchone()[0]\n", "\n", " unmatched_count += n_unmatched\n", " total_count += n_total\n", "\n", " n_assigned = n_total - n_unmatched\n", "\n", " print(f\"{n_assigned:,} assigned, {n_unmatched:,} unmatched, {time.time()-t0:.1f}s\")\n", "\n", "# Final summary\n", "print(f\"\\n{'='*60}\")\n", "print(f\"Spatial join complete — Exact match results\")\n", "print(f\"{'='*60}\")\n", "print(f\"Total footprints processed: {total_count:>12,}\")\n", "print(f\"Assigned (exact match): {total_count - unmatched_count:>12,} ({(total_count - unmatched_count)/total_count*100:.6f}%)\")\n", "print(f\"Unmatched: {unmatched_count:>12,} ({unmatched_count/total_count*100:.6f}%)\")" ] }, { "cell_type": "markdown", "id": "9e6451be-e69f-4115-858b-6c006560834d", "metadata": {}, "source": [ "### Results from the Full Run\n", "\n", "| Metric | Value |\n", "|--------|-------|\n", "| Total footprints processed | 52,131,093 |\n", "| Assigned (exact match) | 52,130,838 (99.9995%) |\n", "| Unmatched | 255 (0.0005%) |\n", "\n", "> **Note:** 255 buildings were not assigned to any municipality, as their centroid could not be matched to a geometry within the same federal state. This can occur in border regions. Overall, this affects only 0.0005% of buildings — for the scope of this analysis, we leave these unmatched. If higher completeness is required, a fallback join (e.g., nearest-neighbor within a search radius of 25 meters) could be applied.\n", "\n", "## 3.4 Aggregating to Municipality Level\n", "\n", "Now that each building footprint has been assigned to a municipality (via the spatial join to VG25 geometries), we can aggregate the footprints to municipality level (`ags`).\n", "\n", "This produces the analytical dataset used in Chapter 2 — one row per municipality with aggregated building statistics.\n", "\n", "We compute for each municipality:\n", "\n", "| Column | Description |\n", "|--------|-------------|\n", "| `n_buildings` | Number of buildings |\n", "| `total_volume_m3` | Sum of building volumes |\n", "| `avg_volume_m3` | Average building volume |\n", "| `total_roof_area_m2` | Sum of roof areas |\n", "| `avg_roof_area_m2` | Average roof area |\n", "| `total_footprint_m2` | Sum of footprint areas |\n", "| `avg_footprint_m2` | Average footprint area |\n", "\n", "The aggregation is performed by grouping the joined footprints by `ags_vg25` (the municipality code from the VG25 join) and `gemeinde_name`." ] }, { "cell_type": "code", "execution_count": 6, "id": "4a100175-a039-422b-b143-af9aa9ae094c", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Aggregating by AGS...\n", "Aggregated: 10,925 municipalities\n", "Total buildings: 52,130,838\n", "Total volume: 46,991,275,341 m³\n", "Total roof area: 6,938,484,974 m²\n", "Total footprint area: 6,068,971,900 m²\n" ] } ], "source": [ "# ============================================================\n", "# Aggregate building stats by AGS\n", "# ============================================================\n", "# This code is provided for full reproducibility.\n", "# It requires the footprints_with_ags table from Section 3.3.\n", "\n", "print(\"Aggregating by AGS...\")\n", "\n", "agg_df = con.execute(\"\"\"\n", " SELECT\n", " ags_vg25 AS ags,\n", " gemeinde_name,\n", " COUNT(*) AS n_buildings,\n", " SUM(bldg_volume) AS total_volume_m3,\n", " ROUND(AVG(bldg_volume), 1) AS avg_volume_m3,\n", " SUM(roof_area) AS total_roof_area_m2,\n", " ROUND(AVG(roof_area), 1) AS avg_roof_area_m2,\n", " SUM(footprint_area) AS total_footprint_m2,\n", " ROUND(AVG(footprint_area), 1) AS avg_footprint_m2\n", " FROM footprints_with_ags\n", " WHERE ags_vg25 IS NOT NULL\n", " GROUP BY ags_vg25, gemeinde_name\n", " ORDER BY ags_vg25\n", "\"\"\").df()\n", "\n", "print(f\"Aggregated: {len(agg_df):,} municipalities\")\n", "print(f\"Total buildings: {agg_df['n_buildings'].sum():,}\")\n", "print(f\"Total volume: {agg_df['total_volume_m3'].sum():,.0f} m³\")\n", "print(f\"Total roof area: {agg_df['total_roof_area_m2'].sum():,.0f} m²\")\n", "print(f\"Total footprint area: {agg_df['total_footprint_m2'].sum():,.0f} m²\")" ] }, { "cell_type": "markdown", "id": "c8df469f-f753-4aa6-91bc-9911b5b1f5b8", "metadata": {}, "source": [ "### Results from the Full Run\n", "\n", "| Metric | Value |\n", "|--------|-------|\n", "| Municipalities | 10,925 |\n", "| Total buildings | 52,130,838 |\n", "| Total volume | 46,991,275,341 m³ |\n", "| Total roof area | 6,938,484,974 m² |\n", "| Total footprint area | 6,068,971,900 m² |\n", "\n", "> **Note:** The municipality-level dataset contains one row per municipality (Gemeinde) with aggregated building statistics. This is the input for Chapter 2, where we join it with RegioStaR classifications and aggregate to VWG level for analysis.\n", "\n", "### Save the Municipality-Level Dataset\n", "\n", "We save this aggregated data to disk. This is the {doc}`input for 2. Analyzing Building Stock Patterns <203b_analysis>`." ] }, { "cell_type": "code", "execution_count": 7, "id": "3fc63608-2426-4385-a800-9c9a86e0723c", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Saved to data\\processed\n" ] } ], "source": [ "# ============================================================\n", "# Save to disk (CSV + Parquet)\n", "# ============================================================\n", "# This code is provided for full reproducibility.\n", "\n", "OUTPUT_DIR = ROOT / \"data\" / \"processed\"\n", "OUTPUT_DIR.mkdir(parents=True, exist_ok=True)\n", "\n", "# Save as CSV (primary format – accessible)\n", "agg_df.to_csv(OUTPUT_DIR / \"3d_building_metrics_germany_2024_stats_by_municipality.csv\", index=False)\n", "\n", "# Save as Parquet (optional – for performance)\n", "agg_df.to_parquet(OUTPUT_DIR / \"3d_building_metrics_germany_2024_stats_by_municipality.parquet\", index=False)\n", "\n", "print(f\"Saved to {OUTPUT_DIR.relative_to(ROOT)}\")" ] }, { "cell_type": "markdown", "id": "5a4bd34d-f24b-4595-883e-111dd4a3837f", "metadata": {}, "source": [ "## 3.6 Performance Considerations\n", "\n", "The spatial join was executed on a **Lenovo ThinkStation P8** workstation with the following specifications:\n", "\n", "| Component | Specification |\n", "|-----------|---------------|\n", "| **Processor** | AMD Ryzen Threadripper PRO 7975WX (32 cores, 64 threads) |\n", "| **RAM** | 512 GB |\n", "| **Storage** | NVMe SSD |\n", "| **OS** | Windows 11 Pro |\n", "\n", "**Processing time on this machine:** ~15–25 minutes for the full spatial join (all 16 states).\n", "\n", "**Resource requirements:**\n", "\n", "| Resource | Minimum | Recommended |\n", "|----------|---------|-------------|\n", "| **Disk space** | ~5 GB for raw data + ~1 GB for intermediate files | ~10 GB free |\n", "| **RAM** | 8 GB | 16–32 GB |\n", "| **Processing time** | 1–2 hours (laptop) | 15–25 minutes (workstation) |\n", "| **Software** | DuckDB with spatial extension, Python 3.9+ | — |\n", "\n", "> **Note:** The processing time depends heavily on hardware. On a standard laptop with 16 GB RAM, expect **1–2 hours** for the full join. On a high-end workstation (like the one used here), it takes **15–25 minutes**.\n", "\n", "**Tips for working with large datasets:**\n", "\n", "- Use **GeoParquet** format — columnar storage for efficient queries\n", "- Use **spatial indexes** (R-tree) for fast joins\n", "- **Partition** data by state or region for parallel processing\n", "- **Filter early** — apply the building function filter before the join\n", "- **Adjust DuckDB memory limits** — set `memory_limit` based on your available RAM\n", " \n", "## 3.7 Accessing the Full Dataset\n", "\n", "The complete dataset is available via ioerDATA:\n", "\n", "> Münzinger, Markus, 2026, \"3D Building Metrics Germany 2024\", \n", "> [https://doi.org/10.71830/9CBBWV](https://doi.org/10.71830/9CBBWV), ioerDATA, V1\n", "\n", "### Download Options\n", "\n", "**Option 1: Web Interface**\n", "\n", "1. Go to [Münzinger (2026)](https://doi.org/10.71830/9CBBWV)\n", "2. Click the \"Download\" button\n", "3. Select individual files or download all\n", "\n", "**Option 2: API Access**\n", "\n", "```{code-block} python\n", "import requests\n", "\n", "# Get dataset metadata\n", "url = \"https://data.ioer.de/api/datasets/9CBBWV\"\n", "response = requests.get(url)\n", "metadata = response.json()\n", "\n", "# Download a specific file\n", "file_url = \"https://data.ioer.de/api/access/datafile/XXXXX\"\n", "response = requests.get(file_url)\n", "with open(\"sl_3d_building_metrics_2024.parquet\", \"wb\") as f:\n", " f.write(response.content)\n", "```\n", "\n", "### Where to Place the Files\n", "\n", "After downloading, place the files in:\n", "\n", "```\n", "data/raw/3D_building_metrics_germany_2024/\n", "├── bb_3d_building_metrics_2024.parquet\n", "├── be_3d_building_metrics_2024.parquet\n", "├── ...\n", "└── th_3d_building_metrics_2024.parquet\n", "```\n", "\n", "### Additional Data Files\n", "\n", "You also need:\n", "\n", "| File | Source | Purpose |\n", "|------|--------|---------|\n", "| `DE_VG25.gpkg` | BKG (GeoBasis-DE) | Administrative boundaries |\n", "\n", "---\n", "\n", "## Summary\n", "\n", "In this chapter, we documented:\n", "\n", "- ✅ The spatial join challenge (57M footprints → 10,925 municipalities)\n", "- ✅ Our exact-match approach (centroid-in-polygon, state-restricted)\n", "- ✅ The aggregation to municipality level (`ags`)\n", "- ✅ Saving the municipality-level dataset for Chapter 2\n", "\n", "**Key results:**\n", "\n", "| Step | Result |\n", "|------|--------|\n", "| Footprints processed | 52,131,093 |\n", "| Assigned (exact match) | 52,130,838 (99.9995%) |\n", "| Unmatched | 255 (0.0005%) |\n", "| Municipalities | 10,925 |\n", "\n", "**Key message:** The pipeline transforms raw footprints into comparable municipality-level building statistics. The next steps — adding spatial typologies and analyzing patterns — are covered in Chapter 2.\n", "\n", "---\n", "\n", "## Citation\n", "\n", "If you use this dataset or the pipeline in your work, please cite:\n", "\n", "```{code-block} bibtex\n", "@book{muenzinger2026footprints,\n", " title={From Footprints to Building Stock Insights},\n", " author={Münzinger, Markus and Behnisch, Martin},\n", " year={2026},\n", " publisher={IOER}\n", "}\n", "\n", "@dataset{muenzinger2026dataset,\n", " title={3D Building Metrics Germany 2024},\n", " author={Münzinger, Markus},\n", " year={2026},\n", " publisher={ioerDATA},\n", " doi={10.71830/9CBBWV}\n", "}\n", "```" ] } ], "metadata": { "kernelspec": { "display_name": "Python (3d-building-pareto-analysis)", "language": "python", "name": "3d-building-pareto-analysis" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.12.13" } }, "nbformat": 4, "nbformat_minor": 5 }