bluegreencity-ki

PostGIS Data Import Guide

Comprehensive guide for importing raster and climate data into the BlueGreenCity-KI PostGIS database.

Version: 2.1 Date: August 2, 2026 Status: Active

Table of Contents

  1. Raster Data Import
  2. Climate Data Import
  3. Climate Data Schema
  4. Archived Input Tables
  5. Tree Inventory Analytics Cache
  6. Troubleshooting

Archived Input Tables

PostGIS is a pipeline output and optional synchronized-cache target. The tree inventory itself is loaded from the configured CSV snapshots in data/input/trees/ (Baum-2010-2025-v6_historisch_prod.csv for the active historical study area and Baum-2026-09-11-v6_aktuell_prod_wie_WFS.csv for the current operational snapshot).

On 2026-07-22, the following obsolete input artifacts were moved without data loss from bgcki to the dedicated bgcki_archive schema:

Previous table Archived table Reason
bgcki.trees_inventory_historical bgcki_archive.trees_inventory_historical Replaced by the consolidated tree CSV
bgcki.trees_inventory_historical_2010_ml bgcki_archive.trees_inventory_historical_2010_ml The 2010 study area now selects the CSV snapshot directly
bgcki.analysis_tree_risk_scores_hannover_ml_training_2010 Not present on 2026-07-22 Old training output had already been removed

The idempotent migration is sql/migrations/2026-07-22_archive_stale_input_tables.sql. It also archives the old training output if it still exists on another installation. No tables are dropped, and no CASCADE operation is used.

Tree Inventory Analytics Cache

The consolidated CSV configured for the active study area remains the single source of truth. Grafana queries a replaceable PostGIS cache so historical analyses do not depend on archived input tables or repeatedly parse the CSV.

Apply the idempotent schema migration:

\i sql/migrations/2026-08-02_create_tree_inventory_analytics.sql

Synchronize the complete CSV in the required Conda environment:

conda run -n bgcki_wesemann --no-capture-output python -m scripts.grafana.sync_tree_inventory_analytics --config config/config.yaml --env-file .env

The synchronizer reads the CSV in configured chunks and replaces bgcki.tree_inventory_observation in one transaction. A failed import rolls back instead of leaving a partially refreshed cache. Header normalization also accepts dotted export variants such as Maßnahmen.festgelegt.

Object Grain and purpose
bgcki.tree_inventory_observation One raw observation per tree and inventory date
bgcki.tree_inventory_latest Latest available observation per tree
bgcki.tree_inventory_vitality_interval Change between consecutive vitality observations; positive change means deterioration
bgcki.tree_inventory_event Planned and completed actions as separate events; event_year is the year before the inventory date

Validated after the initial synchronization on 2026-08-02:

The source file name does not establish an observation year. In particular, the historical snapshot Baum-2010-2025-v6_historisch_prod.csv contains latest per-tree observations through 2025, while the current snapshot Baum-2026-09-11-v6_aktuell_prod_wie_WFS.csv carries 2026 observations collected during the 2026 inventory season.

Raster Data Import

Guide for importing GeoTIFF raster data (air temperature, land use, etc.) into PostGIS.

Prerequisites

1. PostGIS Container Setup

Ensure your PostGIS container is running:

docker ps | grep bgcki-postgis

2. Install PostGIS Tools

Install raster2pgsql in the container (one-time setup):

docker exec bgcki-postgis bash -c "apt-get update && apt-get install -y postgis"

3. Enable PostGIS Raster Extension

Enable raster support in the database (one-time setup):

docker exec bgcki-postgis psql -U bgcki_admin -d bgcki -c "CREATE EXTENSION IF NOT EXISTS postgis_raster;"

Verify:

docker exec bgcki-postgis psql -U bgcki_admin -d bgcki -c "SELECT typname FROM pg_type WHERE typname = 'raster';"
# Should return: raster

Import Methods

Use the Python script for a fully automated workflow:

# Import single raster
python scripts/import_raster_to_postgis.py --raster airtemp_14h --version 2022

# Import all configured rasters
python scripts/import_raster_to_postgis.py --all --version 2022

(Tiling is computed automatically from the raster dimensions; there is no --tile-size flag.)

What it does:

Method 2: Manual Import (Full Control)

For custom workflows or debugging:

Step 1: Copy File to Container

docker cp "path/to/your/raster.tif" bgcki-postgis:/tmp/raster.tif

Step 2: Import with raster2pgsql

docker exec bgcki-postgis bash -c "raster2pgsql -I -C -M -s 25832 -t 100x100 \
  /tmp/raster.tif \
  bgcki.context_raster_name_version \
  | psql -U bgcki_admin -d bgcki"

Flags explained:

Step 3: Register in Data Catalog

docker exec -i bgcki-postgis psql -U bgcki_admin -d bgcki <<EOF
INSERT INTO public.data_catalog 
    (dataset_name, dataset_type, version, schema_name, table_name, 
     description, source, metadata, is_current)
VALUES 
    ('dataset_name', 'raster', '2022', 'bgcki', 'context_raster_name_2022',
     'Description of the raster dataset',
     'Data source name',
     '{"resolution": "10m", "crs": "EPSG:25832"}'::jsonb,
     TRUE);
EOF

Step 4: Clean Up

docker exec bgcki-postgis rm /tmp/raster.tif

Query Raster Data

Basic Queries

-- Get raster metadata
SELECT 
    ST_Width(rast) as width,
    ST_Height(rast) as height,
    ST_NumBands(rast) as bands,
    ST_SRID(rast) as srid
FROM bgcki.context_raster_airtemp_14h_2022
LIMIT 1;

-- Get value at specific point
SELECT ST_Value(rast, 1, ST_SetSRID(ST_MakePoint(9.73, 52.37), 4326)) as value
FROM bgcki.context_raster_airtemp_14h_2022
LIMIT 1;

Extract Values for Trees

-- Get temperature values for all trees
SELECT 
    t.gisid,
    t.art,
    ST_Value(r.rast, 1, t.geom) as temp_14h
FROM bgcki.trees_inventory t
CROSS JOIN LATERAL (
    SELECT rast 
    FROM bgcki.context_raster_airtemp_14h_2022 
    WHERE ST_Intersects(rast, t.geom)
    LIMIT 1
) r
WHERE ST_Value(r.rast, 1, t.geom) IS NOT NULL
LIMIT 10;

Configuration

Raster imports are configured in config/config.yaml:

data_management:
  raster_imports:
    airtemp_14h:
      input_key: "airtemp_raster_14h"
      schema: "bgcki"
      table: "context_raster_airtemp_14h"
      description: "Modeled air temperature at 14h (2 PM) at 2m height"
      source: "Stadt Hannover Klimaanalyse 2022"
      version: "2022"

View Current Datasets

SELECT * FROM public.data_catalog_current;

Climate Data Import

Guide for importing time-series climate data from DWD CDC and Open-Meteo API.

Overview

Two data sources are available for climate data import:

  1. DWD CDC (German Weather Service) - Official gridded climate data (HYRAS, HOSTRADA)
  2. Open-Meteo API - Global historical weather data via REST API

Data Source Comparison

DWD CDC (HYRAS & HOSTRADA)

Official German Weather Service (Deutscher Wetterdienst) climate datasets

HYRAS (Hydrological Regionalized Daily Analysis)

HOSTRADA (Hourly Station-based Raster Data)

Advantages:

Disadvantages:

Best Use Cases:

Open-Meteo API

Free weather API providing historical reanalysis data

Advantages:

Disadvantages:

Best Use Cases:

Recommendation

For BlueGreenCity-KI project (Hannover, Germany):

Primary: DWD CDC (HYRAS/HOSTRADA)

Use DWD data for production analyses:

Secondary: Open-Meteo API

Use Open-Meteo for:

Hybrid Approach

Combine both sources:

  1. Use HYRAS/HOSTRADA for historical baseline (1990-2020)
  2. Use Open-Meteo for recent data and real-time monitoring
  3. Validate Open-Meteo against DWD data for bias correction

Prerequisites

For Both Methods

  1. Python environment: 
    conda activate bgcki_wesemann
  2. PostGIS database running: 
    docker ps | grep bgcki-postgis
  3. Environment configuration: 
    # Ensure .env file exists with database credentials
Copy-Item .env.example .env

Additional Dependencies for DWD Import

Install netCDF processing libraries:

# Via conda (recommended)
conda install -c conda-forge netCDF4 xarray h5netcdf

# Or via pip
pip install netCDF4 xarray h5netcdf

Usage: DWD CDC Import

Script: import_climate_data_dwd.py

List Available Variables

HYRAS:

python scripts/import_climate_data_dwd.py --dataset hyras --list-variables

Available variables:

HOSTRADA:

python scripts/import_climate_data_dwd.py --dataset hostrada --list-variables

Available variables:

Standard Import

Import HYRAS daily precipitation for 2022:

python scripts/import_climate_data_dwd.py --dataset hyras --variable precipitation --year 2022

Import HOSTRADA hourly temperature for July 2022:

python scripts/import_climate_data_dwd.py --dataset hostrada --variable temperature --year 2022 --month 7

Dry Run (Download Only, No Import)

python scripts/import_climate_data_dwd.py --dataset hyras --variable precipitation --year 2022 --dry-run

Force Re-Download

python scripts/import_climate_data_dwd.py --dataset hyras --variable precipitation --year 2022 --force-download

Output Tables

Data is stored in PostGIS schema bgcki with naming pattern:

Table Structure:

CREATE TABLE bgcki.climate_dwd_hyras_precipitation (
    time TIMESTAMP,
    lon FLOAT,
    lat FLOAT,
    value FLOAT,
    PRIMARY KEY (time, lon, lat)
);
CREATE INDEX ON bgcki.climate_dwd_hyras_precipitation(time);
CREATE INDEX ON bgcki.climate_dwd_hyras_precipitation(lon, lat);

Verification

Check imported data:

-- Count records
SELECT COUNT(*) FROM bgcki.climate_dwd_hyras_precipitation;

-- Check time range
SELECT MIN(time), MAX(time) FROM bgcki.climate_dwd_hyras_precipitation;

-- Sample values
SELECT time, lon, lat, value 
FROM bgcki.climate_dwd_hyras_precipitation 
ORDER BY time DESC
LIMIT 10;

-- Statistics
SELECT 
    MIN(value) as min_precip,
    MAX(value) as max_precip,
    AVG(value) as mean_precip
FROM bgcki.climate_dwd_hyras_precipitation;

Usage: Open-Meteo API Import

Script: import_climate_data_openmeteo.py

List Available Variables

python scripts/import_climate_data_openmeteo.py --list-variables

Available variables (selection):

Standard Import

Import temperature and precipitation (2024):

python scripts/import_climate_data_openmeteo.py \
    --variables temperature_2m,precipitation \
    --start-date 2024-01-01 \
    --end-date 2024-12-31 \
    --resolution daily \
    --dataset-name weather

Import hourly data:

python scripts/import_climate_data_openmeteo.py \
    --variables temperature_2m,precipitation,relative_humidity_2m \
    --start-date 2024-10-01 \
    --end-date 2024-10-31 \
    --resolution hourly \
    --dataset-name weather

Dry Run (Fetch Only, No Import)

python scripts/import_climate_data_openmeteo.py \
    --variables temperature_2m \
    --start-date 2024-10-01 \
    --end-date 2024-10-10 \
    --dry-run

Output Tables

Data is stored in PostGIS schema bgcki with naming pattern:

Table Structure:

CREATE TABLE bgcki.climate_openmeteo_weather_hourly (
    time TIMESTAMP,
    latitude FLOAT,
    longitude FLOAT,
    elevation FLOAT,
    temperature_2m FLOAT,
    precipitation FLOAT,
    relative_humidity_2m FLOAT,
    et0_fao_evapotranspiration FLOAT
);
CREATE INDEX ON bgcki.climate_openmeteo_weather_hourly(time);

Verification

-- Count records
SELECT COUNT(*) FROM bgcki.climate_openmeteo_weather_daily;

-- Check time range
SELECT MIN(date), MAX(date) FROM bgcki.climate_openmeteo_weather_daily;

-- Recent values
SELECT date, temperature_2m_max, temperature_2m_min, precipitation
FROM bgcki.climate_openmeteo_weather_daily
ORDER BY date DESC
LIMIT 10;

Climate Data Schema

Schema Structure

Schema Name: bgcki

Purpose: Storage of climate data (precipitation, temperature, evapotranspiration, etc.) for:

Table Naming: All climate tables use the prefix climate_ (e.g., climate_openmeteo_weather_hourly, climate_dwd_hyras_precipitation)

Access: Full access for bgcki_admin

Table Types

1. DWD HYRAS Tables (Daily)

Tables following pattern: bgcki.climate_dwd_hyras_{variable}

Examples:

Structure:

CREATE TABLE bgcki.climate_dwd_hyras_precipitation (
    time TIMESTAMP NOT NULL,
    lon FLOAT NOT NULL,
    lat FLOAT NOT NULL,
    value FLOAT,
    PRIMARY KEY (time, lon, lat)
);
CREATE INDEX idx_climate_dwd_hyras_precipitation_time ON bgcki.climate_dwd_hyras_precipitation(time);
CREATE INDEX idx_climate_dwd_hyras_precipitation_coords ON bgcki.climate_dwd_hyras_precipitation(lon, lat);

2. DWD HOSTRADA Tables (Hourly)

Tables following pattern: bgcki.climate_dwd_hostrada_{variable}

Examples:

Structure: Same as HYRAS but with hourly temporal resolution

3. Open-Meteo Tables

Hourly: bgcki.climate_openmeteo_{dataset}_hourly

CREATE TABLE bgcki.climate_openmeteo_weather_hourly (
    time TIMESTAMP,
    latitude FLOAT,
    longitude FLOAT,
    elevation FLOAT,
    temperature_2m FLOAT,
    precipitation FLOAT,
    relative_humidity_2m FLOAT,
    wind_speed_10m FLOAT,
    shortwave_radiation FLOAT
);
CREATE INDEX idx_climate_openmeteo_weather_hourly_time ON bgcki.climate_openmeteo_weather_hourly(time);

Daily: bgcki.climate_openmeteo_{dataset}_daily

CREATE TABLE bgcki.climate_openmeteo_weather_daily (
    date TIMESTAMP,
    latitude FLOAT,
    longitude FLOAT,
    elevation FLOAT,
    temperature_2m_max FLOAT,
    temperature_2m_min FLOAT,
    precipitation FLOAT,
    et0_fao_evapotranspiration FLOAT
);
CREATE INDEX idx_climate_openmeteo_weather_daily_date ON bgcki.climate_openmeteo_weather_daily(date);

Common Queries

Query ET₀ Time Series

SELECT 
    date,
    et0_fao_evapotranspiration as et0_mm_day,
    ST_AsText(geom) as location
FROM bgcki.climate_openmeteo_weather_daily
WHERE date >= '2024-10-01' AND date <= '2024-10-07'
ORDER BY date;

Calculate Monthly Statistics

SELECT 
    DATE_TRUNC('month', date) as month,
    AVG(et0_fao_evapotranspiration) as avg_et0,
    SUM(precipitation) as total_precip_mm
FROM bgcki.climate_openmeteo_weather_daily
WHERE date >= '2024-01-01'
GROUP BY month
ORDER BY month;

Spatial Analysis with DWD Grids

-- Average precipitation per grid cell for a month
SELECT 
    lon,
    lat,
    AVG(value) as avg_precipitation_mm
FROM bgcki.climate_dwd_hyras_precipitation
WHERE time >= '2022-07-01' AND time < '2022-08-01'
GROUP BY lon, lat
ORDER BY avg_precipitation_mm DESC
LIMIT 10;

Troubleshooting

Raster Import Issues

Error: “raster2pgsql: command not found”

Solution: Install PostGIS tools in container

docker exec bgcki-postgis bash -c "apt-get update && apt-get install -y postgis"

Error: “extension postgis_raster does not exist”

Solution: Enable raster extension

docker exec bgcki-postgis psql -U bgcki_admin -d bgcki -c "CREATE EXTENSION postgis_raster;"

Large Raster Files

Problem: Import takes too long or runs out of memory

Solution: Adjust tile size

# Use larger tiles for faster import (less overhead)
-t 200x200

# Use smaller tiles for better performance when querying
-t 50x50

Climate Data Import Issues

DWD Import: “ModuleNotFoundError: No module named ‘netCDF4’”

Solution: Install netCDF dependencies

conda install -c conda-forge netCDF4 xarray h5netcdf

DWD Import: File Download Fails

Solution: Check DWD server availability and network connection

Open-Meteo: API Rate Limit Exceeded

Solution: Reduce request frequency

Database Connection Errors

Solution: Check PostGIS container and credentials

# Verify container is running
docker ps | grep bgcki-postgis

# Test connection
docker exec bgcki-postgis psql -U bgcki_admin -d bgcki -c "SELECT version();"

# Check .env file has correct credentials
cat .env

Getting Help

  1. Check script --help output for usage information
  2. Review error messages carefully
  3. Verify prerequisites are met
  4. Check PostGIS logs: docker logs bgcki-postgis
  5. Create GitHub issue if problem persists

Last Updated: November 18, 2025
Document Version: 2.0
Maintainer: BlueGreenCity-KI Team