Swiss Rooftop Explorer + SwissTLM ETL pipeline

A web application for exploring Swiss rooftops and addresses and a processing pipeline for

• converting Swiss addresses to parquet files
• converting some SwissTLM features to parquet files (buildings, bridges, roads). This is also used for Topoprint.

See it live in action

Read all about it in this blog-post.

Features

• Fast web map for approximately 3.2 million roof polygons in Switzerland, using [PMTiles][]
• 3D bridge models generated from Swiss transportation network data (TLM)
• Identification and processing of buildings that might be missing in the original dataset
• Visualization of bridges with customizable parameters (arches, piers, deck width)
• End-to-end data processing pipeline from a FileGeodatabase to PMTiles via [Geoparquet][]
• Swiss addresses stored in a compact parquet file for quick retrieval
• Client-side querying of addresses using [DuckDB-WASM][]
• No server component needed other than a web or object server, making it easy to run locally
• Bookmarking is possible, for example in Bern: https://ping13.net/swiss-rooftop-explorer/?zip=3011 (though it may take a couple of seconds until the app zooms to the area of interest)

Prerequisites

• Python 3.11+
• Node.js
• Make
• DuckDB
• Tippecanoe (for PMTiles generation)
• uv

Installation

1. Clone the repository:

git clone https://github.com/ping13/swiss-rooftop-explorer

2. Install dependencies using uv sync. In case of macOS, I needed to make sure that GDAL and arrow binaries are built against Homebrew's version (which I use).

make align_with_homebrew

(Details see this issue)

3. Install frontend dependencies:

cd web
npm install

Data Processing Pipeline

The project processes Swiss geographic data through several stages:

1. Data Download

make download

Downloads and extracts:

• Swiss address database (SQLite format)
• SwissBUILDINGS3D 3.0 data (FileGDB format)
• SwissTLM3D data for road and railway bridges

2. Address Data Processing

make create_addresses

Converts Swiss address data from SQLite to two Parquet formats:

• Full dataset (addresses_full.parquet)
• Minimal dataset (addresses.parquet)

3. Building Data Processing

make create_buildings

Processes building data through multiple stages:

• Converts FileGDB to Parquet
• Processes 3D geometries
• Creates 2D projections

4. Bridge and Additional Building Processing

make create_bridges

Processes bridge data through multiple stages:

• Extracts bridge geometries from SwissTLM3D data
• Creates parametrized 3D bridge models with arches, piers, and customizable deck width
• Stores bridge parameters in a SQLite database for customization
• Exports bridges to GeoParquet format with compressed 3D models

make display_bridge_from_clipboard

Visualizes a bridge in 3D from GeoJSON data in the QGIS project copied to clipboard (useful for testing)

5. Map Tile Generation

make pmtiles

Generates PMTiles for web visualization:

• buildings.pmtiles: Complete dataset including buildings, missing buildings, and bridges
• roofs-small.pmtiles: Smaller test dataset

6. Road & Railway Line Extraction

make roads
make railways

Creates GeoParquet datasets via scripts/create_routes_3d.py containing the full 3D alignment for every TLM road and railway segment. Each record stores a WKB blob, inferred lane width, and flattened 2D length; downstream publishing is handled by make publish_routes.

Development

Start the development server:

make devserver

Build for production:

make build_web

Test production build:

make test_web

Publishing & Sharing

Use the Makefile targets to push generated datasets and the web build to the public host:

• make publish_buildings uploads parquet outputs for building footprints and centers.
• make publish_bridges syncs parametrized bridge assets (parquet + GeoJSON).
• make publish_routes publishes the road and railway GeoParquet produced by create_routes_3d.py.
• make publish_web deploys the Vite production build via rsync.

Project Structure

├── scripts/                   # Data processing scripts
│   ├── addresses_sqlite2pq.py # Convert address data from SQLite to Parquet
│   ├── swissbuildings3D_gdb2pq.py # Convert building data from GDB to Parquet
│   ├── swissbuildings3D_process.py # Process 3D building geometries
│   ├── bridge_creation.py    # Core functionality for creating 3D bridge models
│   ├── create_bridges_3d.py  # Process bridge data and create 3D models
│   ├── create_bridges_kml.py # Export bridges with parameters to KML/GeoJSON
│   ├── create_addl_buildings_from_roof.py # Identify missing buildings from roof data
│   ├── create_center_points.py # Create center points for buildings
│   ├── create_routes_3d.py   # Convert TLM roads/railways into GeoParquet with width metadata
│   ├── create_bridge_db.sh   # Seed the SQLite DB for bridge parameter overrides
│   ├── create_topoprint_script_for_bridges.py # Generate helper scripts for TopoPrint exports
│   └── pq2pmtiles.sh          # Convert Parquet to PMTiles format
├── web/                       # Web application
│   ├── src/
│   ├── public/
│   └── package.json
├── Makefile                   # Build automation
├── assets/                   # Data assets
│   └── bridge_parameters.db  # SQLite database for bridge customization
└── pyproject.toml             # Python project configuration

Technology Stack

• Frontend:
  • Vanilla JavaScript
  • Vite for build tooling
  • DuckDB-WASM for client-side querying
  • MapLibre GL for map visualization
• Data Storage:
  • Parquet format for efficient data storage
  • PMTiles for map visualization
• Data Processing:
  • Python with GeoPandas, PyArrow
  • DuckDB for SQL operations
  • GDAL/OGR for geographic data processing
  • PyVista and Trimesh for 3D modeling and visualization
  • Shapely for geometric operations
• Build System: Make