[German abstract; English version below]
Die SGC Klimadatenplattform der Stadt Konstanz bündelt Klima‑, Umwelt‑ und mobilitätsbezogene Zeitreihen in einer einheitlichen, rechtskonformen Infrastruktur. Sensorströme, amtliche Datensätze und betriebliche Kennzahlen werden zusammengeführt, strukturiert und über APIs sowie Dashboards bereitgestellt, um interne Abläufe, evidenzbasierte Planungen und Transparenz gegenüber der Öffentlichkeit zu unterstützen. Typische Anwendungsfälle umfassen etwa die Einsatzplanung des Winterdienstes, strategische Mobilitätsanalysen, Klimaanpassungsmaßnahmen sowie die Veröffentlichung auf Portalen wie stadtdaten.konstanz.digital und offenedaten-konstanz.de.
Dieses Repository enthält die technischen Integrationen für die Datenplattform, inklusive wiederverwendbarer Module und Automatisierungen. Beispiele sind Anbindungen für Wetterstationen (DWD und eigene Stationen), Verkehrs- und Fahrradzählungen, Luftqualitätsmessungen, Pegelstände, Solar‑ und Ladeinfrastruktur sowie interne Betriebskennzahlen. Die Integrationen erfassen, bereinigen und speichern die Daten als Zeitreihen und stellen sie zur weiteren Nutzung in der Plattform zur Verfügung. Weitere Hintergrundinformationen zum Projekt finden Sie auf der Projektseite: Smart Green City Klimadatenplattform.
The SGC Klimadatenplattform is the City of Konstanz’s data platform focused on climate, environment, and mobility-related time series. It consolidates sensor streams, official datasets, and operational metrics into a unified, policy-compliant infrastructure to support internal workflows, evidence-based planning, and public transparency.
Practically, the platform ingests and structures diverse sources (e.g., weather stations, traffic counters, air quality monitors, hydrology levels, renewable assets, and operational dashboards), persists them in time series databases, and exposes them via APIs and dashboards. This enables use cases such as winter service planning, strategic mobility analysis, climate adaptation measures, and publication on public portals (e.g., stadtdaten.konstanz.digital and offenedaten-konstanz.de), while enforcing appropriate access controls.
This project is sponsored by the Smart City grant program in Germany. This grant program mandates that all software components—whether deployed as-is, modified, or written anew—are open source.
Our data platform is based on a managed instance of Hypertegrity's Urban Data Space Platform (UDSP). We use the following features and components:
- Keycloak to manage identities and access of internal and external participants.
- Data is compartmentalized in various "data spaces", each with their own access rules.
- The data lives in Timescale/Postgres databases.
- Node-RED for low-code data integrations and automation.
- Grafana for internal dashboards, monitoring, and alerting.
- Some FIWARE components (Stellio, Quantumleap) to provide (partially) NGSI-LD compliant APIs.
- PostgREST to expose RESTful APIs for the Postgres databases directly. (This is a custom addition to the UDSP stack.)
- APISIX to enforce access control on the public API endpoints.
We are also experimenting with potential modifications to this stack:
- Replace Node-RED (development + deployment) with Python notebooks (development) and an orchestrator like Airflow or Dagster (deployment).
- Cloud-based development environments (currently GitHub Codespaces, later an open source alternative like Coder or Eclipse Che).
- Complement the Postgres databases with S3-based data storage such as Delta Lake.
- Infisical for secret management.
We integrate multiple data sources into our data platform:
- Car traffic counts from the Federal Highway and Transport Research Institute (BASt) in
./integrations/bast/. - Extreme weather alarms, weather observations, and weather forecast data from the National Meteorological Service (DWD) in
./integrations/dwd/. - Bicycle traffic counts from Eco-Counter monitors in
./integrations/ecocounter/. - Usage metrics from our internal dashboard solution in
./integrations/internal-reporting/. - Pedestrian traffic counts from LASE PeCo monitors in
./integrations/lasepeco/. - Air quality data from the Baden-Württemberg State Institute for the Environment (LUBW) in
./integrations/lubw/. - Solar power production capacity and e-charging availability data from the national "Marktstammdatenregister" (MaStR) in
./integrations/marktstammdatenregister/. - Bike-hire and free-floating scooter availability from MobiData BW in
./integrations/mobidata/. - Water level data from Pegelonline in
./integrations/pegelonline/. - Weather observation data from our own weather stations in
./integrations/sgc-weather/. - Parking facility utilization data in
./integrations/skn-gis-parkdaten/.
If applicable, data sources are filtered for our region. We generally record historic values to obtain time series data.
Some of these integrations are implemented and deployed as Node-RED flows. We generally use one UDSP data space per data source and one productive Node-RED instance per data space. Integrations which are based on Node-RED have their Node-RED project tracked as separate Git repositories. These Node-RED projects are then linked into the respective integrations folder in this repository using Git submodules (e.g., ./integrations/dwd/nodered). Additional documentation on how we manage the different Node-RED code repositories on multiple Node-RED instances is available in ./documentation/nodered-projects.md.
Some other integrations are implemented as IPython notebooks (./integrations/*/*.ipynb). Reusable parts of the integration are maintained as separate modules, either in the integrations directory (./integrations/*/*.py) if they are related to a single data source, or in the ./utils/ directory if they are applicable to multiple data sources (e.g., code for UDSP uploads in ./utils/udsp).
Some of the data sources are updated irregularly or at very long intervals. E.g., BASt traffic count data updates once a year. For such integrations, we avoid the technical overhead of automation and run the respective notebooks manually and on demand.
We're experimenting with Dagster and Airflow to schedule the automatic execution of IPython/notebook-based integrations. The intended execution schedules are defined in the jobs.py file in the respective integration directory, e.g., ./integrations/sgc-weather/jobs.py to mirror and persist all LoRaWAN messages from our TTI instance to S3 object storage. The Dagster glue code in ./utils/dagster/ turns all these job definitions into a Dagster code location which we publish as Docker image on the GitHub Container Registry (ghcr.io).
A separate repository, sgc-kn/k8s-sandbox, documents our actual deployment of Dagster and the Dagster code location on Kubernetes using ArgoCD.
Note: the integrations depend on various secrets like S3 bucket IDs and keys, UDSP access credentials, and data source API keys. We manage these secrets in Infisical and sync them to the Kubernetes deployment using the external secrets operator. Without those secrets, the integrations will inevitably fail.
We found it very challenging to set up Python development environments on our corporate IT infrastructure (outbound firewall, no admin rights). To avoid punching big holes into warranted defenses, we resorted to running all development‑related tasks outside the corporate network, either in the cloud or on unmanaged Linux devices with unrestricted root access. This repository supports devenv, devcontainers, and GitHub Codespaces to get you started quickly and reproducibly.
Note: the first step in any new development environment is to load the secrets from Infisical. Run just dotenv for that.
Copyright © 2024–2025 Stadt Konstanz
We release this code under the EUPL-1.2 license. See the LICENSE file for details. Contributions to this project will be licensed under the same terms.