bluegreencity-ki

OSM Highway Data Update

Overview

This guide describes the procedure for refreshing OpenStreetMap (OSM) highway data for the pipeline. OSM highway data is used for street type risk assessment in the drought risk scoring system.

Since the 2026-04 refactor, the same central loader is used in both places:

• inside the pipeline during street_type
• inside scripts/import_osm_highways_to_postgis.py

The script is therefore now a thin manual wrapper around the pipeline loader, not a separate import implementation.

Recommended Frequency: Annually (October)
Task Type: Data Maintenance

Prerequisites

One-Time Setup (First Import Only)

1. Create .env file (if not already present):

   Copy-Item .env.example .env
   

2. Update credentials in .env (if needed):
   • Set PostGIS connection details (host, port, user, password)
   • For remote access, use the private hostname or VPN address defined for your environment
3. Verify Python environment:

   conda activate bgcki_wesemann
   

4. Verify PostGIS container is running:

   docker ps | grep bgcki-postgis
   

Import Procedure

Standard Import (Recommended)

Uses bounding box from tree inventory data:

python scripts/import_osm_highways_to_postgis.py

Import with Custom Bounding Box

Specify a custom geographic area:

python scripts/import_osm_highways_to_postgis.py --bbox "9.5,52.3,10.0,52.5"

Format: "west,south,east,north" (WGS84 coordinates)

Dry Run Mode

Download data without importing to PostGIS (for testing):

python scripts/import_osm_highways_to_postgis.py --dry-run

Force Refresh

Ignore cached data and force a fresh download:

python scripts/import_osm_highways_to_postgis.py --force-refresh

Caching Mechanism

The central loader uses intelligent caching to avoid unnecessary downloads:

• Cache validity: 365 days (configurable in config.yaml → inputs.roads.refresh_days)
• Cache location: data/cache/osm/
• Cache format: GeoPackage (GPKG)
• Behavior: First use a fresh GeoPackage cache, otherwise a fresh PostGIS cache, otherwise fetch from OSM and refresh both caches

When running the script or the pipeline again within the cache validity period, the loader automatically uses the existing cache.

Verification

After a refresh, verify the data in PostGIS:

Check Import Count

-- Total number of imported roads
SELECT COUNT(*) FROM bgcki.context_osm_highways;

Review Highway Types

-- Distribution of highway types
SELECT highway, COUNT(*) 
FROM bgcki.context_osm_highways 
GROUP BY highway 
ORDER BY COUNT(*) DESC;

Verify Geometry

-- Geometry information
SELECT 
    COUNT(*) as total_roads,
    ST_GeometryType(geom) as geom_type,
    ST_SRID(geom) as srid
FROM bgcki.context_osm_highways
GROUP BY ST_GeometryType(geom), ST_SRID(geom);

Expected results:

• Geometry type: ST_LineString
• SRID: 25832 (ETRS89 / UTM zone 32N)

Configuration

OSM import settings are configured in config/config.yaml:

inputs:
  roads:
    source: "osm"
    allowed: ["motorway", "trunk", "primary", "secondary", "tertiary", "residential"]
    buffer_m: 200.0
    refresh_days: 365  # Cache validity in days
    timeout_s: 180     # OSM API timeout

Configuration Parameters

Parameter	Type	Default	Description
source	String	osm	Data source (OpenStreetMap)
allowed	List	See above	Highway types to import
buffer_m	Float	200.0	Buffer distance for proximity analysis
refresh_days	Integer	365	Cache validity period
timeout_s	Integer	180	API request timeout

Troubleshooting

Error: “Module ‘osmnx’ not found”

Solution:

conda install -c conda-forge osmnx

Error: “Could not connect to PostGIS”

Possible causes and solutions:

1. Container not running:

   docker ps
   docker compose up -d
   

2. Incorrect credentials:
   • Verify .env settings (host, port, username, password)
   • For local access: use the local database hostname
   • For remote access: use the private hostname or VPN address defined for your environment
3. Network issues:
   • Check the private-network or VPN connection
   • Verify firewall settings

Error: “OSM fetch timeout”

Solutions:

1. Increase timeout in config:

   inputs:
     roads:
       timeout_s: 300  # Increase from default 180
   

2. Use smaller bounding box:

   python scripts/import_osm_highways_to_postgis.py --bbox "9.7,52.35,9.8,52.4"
   

3. Check OSM API status:
   • Visit: https://wiki.openstreetmap.org/wiki/Overpass_API#Public_Overpass_API_instances
   • Verify service availability

Error: “Geometry transformation failed”

Solution:

Verify that PostGIS has the correct spatial reference system:

-- Check if EPSG:25832 is available
SELECT * FROM spatial_ref_sys WHERE srid = 25832;

If missing, the PostGIS installation may need to be updated.

File Locations

File/Directory	Purpose
scripts/import_osm_highways_to_postgis.py	Import script
config/config.yaml	Configuration (see inputs.roads)
.env	Database credentials (not in Git!)
data/cache/osm/	Cached OSM data (GPKG format)
bgcki.context_osm_highways	PostGIS table (target)

Data Flow

OpenStreetMap API
    ↓
osmnx library (Python)
    ↓
Local cache (GPKG) → 365 days validity
    ↓
PostGIS database (bgcki.context_osm_highways)
    ↓
Street type criterion (risk scoring)

Best Practices

1. Annual Updates: Schedule updates yearly to capture new roads and changes
2. October Timing: Align with tree inventory updates for consistency
3. Backup First: Create a database backup before major updates (see PostGIS Backup Guide)
4. Verify After Import: Always run verification queries to ensure data quality
5. Cache Management: Monitor cache directory size and clean old files periodically
6. Documentation: Log update dates and any issues encountered

Integration with Risk Scoring

The imported OSM highway data is used by the street type criterion in the drought risk scoring system:

• Purpose: Assess pollution load and heat stress based on road proximity
• Method: Trees near major roads receive higher risk scores
• Buffer: Configurable buffer distance (default: 200m)
• Highway Types: Different road types have different impact weights

For more information, see:

• street_type criterion documentation
• Risk Scoring Guide

Related Documentation

• PostGIS Installation Guide - Initial database setup
• PostGIS and QGIS User Guide - Database access and visualization
• PostGIS Backup Guide - Backup procedures before updates
• Architecture Documentation - System overview

Change Log

Date	Version	Changes
2025-10-18	1.0	Initial documentation for OSM highway data maintenance procedure

Script Usage Examples

Example 1: Standard Annual Update

# Activate environment
conda activate bgcki_wesemann

# Run standard import (uses cached data if valid)
python scripts/import_osm_highways_to_postgis.py

# Verify import
docker exec -it bgcki-postgis psql -U bgcki_admin -d bgcki -c "SELECT COUNT(*) FROM bgcki.context_osm_highways;"

Example 2: Force Fresh Download

# Force a fresh download (ignore cache)
python scripts/import_osm_highways_to_postgis.py --force-refresh

# This is useful when:
# - OSM data quality has improved
# - Major road network changes occurred
# - Cache is corrupted

Example 3: Import for Specific District

# Import data for a specific district using custom bounding box
python scripts/import_osm_highways_to_postgis.py --bbox "9.7,52.35,9.85,52.42"

# Hanover city center example coordinates:
# West: 9.7, South: 52.35, East: 9.85, North: 52.42

Support

For issues or questions:

1. Check the troubleshooting section above
2. Review the script’s inline documentation
3. Consult the PostGIS User Guide
4. Contact the project team or create a GitHub issue