Skip to main content

NetworkManager Coordinates Conversion Design

The convert_db_coordinates.py script is a Python utility designed to convert geographic coordinates from one Coordinate Reference System (CRS) to another within a PostgreSQL database. This tool is specifically tailored for water network modeling applications, handling coordinate data across multiple database tables.

Purpose​

The script performs coordinate system transformations for water network infrastructure data, converting from local/projected coordinate systems (like UTM zones) to the global WGS84 coordinate system (EPSG:4326). This is essential for:

  • Integrating local network data with global mapping systems
  • Ensuring compatibility with web-based mapping applications
  • Standardizing coordinate data across different regions and projects

Target Database Tables​

The script processes coordinate data in three main tables:

  1. LinkGeoms - Contains JSON arrays of coordinate pairs representing link geometries

    • Converts coordinate pairs in the Coords JSON field
    • Calculates and updates centroid coordinates (XCoordCenter, YCoordCenter)

  2. LinkVertices - Contains individual vertex coordinates for network links

    • Converts XCoord and YCoord columns

  3. Nodes - Contains node/intersection coordinates

    • Converts XCoord and YCoord columns

Configuration​

Database Connection​

DB_HOST = "localhost"
DB_PORT = 5432
DB_NAME = "iitd_8_11_2025"  # Configurable database name
DB_USER = "postgres"
DB_PASSWORD = "xxxxxxxxxx"

Coordinate Systems​

  • Input CRS: EPSG:32643 (UTM Zone 43N) - Configurable
  • Output CRS: EPSG:4326 (WGS84) - Standard global coordinate system

Geographic Bounds​

The script includes bounds validation to ensure converted coordinates fall within expected geographic ranges:

  • Top Left: (49.501385, -123.618216) - Latitude, Longitude
  • Bottom Right: (48.958044, -121.157745) - Latitude, Longitude

Key Features​

1. Batch Processing​

  • Processes records in configurable batches (default: 1000 records)
  • Reduces memory usage and improves performance for large datasets
  • Provides progress feedback during processing

2. Coordinate Validation​

  • Validates output coordinates against expected geographic bounds
  • Interactive prompts for handling out-of-bounds coordinates
  • Option to skip validation for known coordinate ranges

3. Error Handling​

  • Comprehensive error handling for coordinate transformation failures
  • Database transaction management with rollback capabilities
  • Graceful handling of malformed coordinate data

4. Centroid Calculation​

  • Automatically calculates centroids for link geometries
  • Uses numpy for efficient mathematical operations
  • Handles edge cases with empty or invalid coordinate arrays

Core Functions​

convert_coordinate_pair(x, y, transformer, output_bounds)​

  • Converts individual coordinate pairs using pyproj Transformer
  • Validates output coordinates against bounds
  • Returns transformed coordinates or error indicators

calculate_centroid(coords)​

  • Calculates geometric centroid of coordinate arrays
  • Uses numpy for efficient computation
  • Handles various coordinate array formats
  • Processes LinkGeoms table with JSON coordinate arrays
  • Converts coordinate pairs and calculates centroids
  • Updates both coordinate arrays and centroid fields
  • Processes LinkVertices table
  • Converts individual vertex coordinates
  • Updates XCoord and YCoord columns

convert_nodes(cursor, transformer, output_bounds)​

  • Processes Nodes table
  • Converts node coordinates
  • Updates XCoord and YCoord columns

Dependencies​

  • psycopg2: PostgreSQL database connectivity
  • pyproj: Coordinate system transformations
  • numpy: Mathematical operations for centroid calculations
  • json: JSON data parsing and serialization

Usage​

  1. Configure the script:

    • Set database connection parameters
    • Configure input and output coordinate systems
    • Define geographic bounds for validation

  2. Run the script:

    python convert_db_coordinates.py

  3. Monitor progress:

    • Script provides real-time feedback on processing status
    • Batch commit confirmations
    • Error reporting for problematic records

Safety Features​

  • Transaction Management: All changes are wrapped in database transactions
  • Rollback Capability: Automatic rollback on errors or user cancellation
  • Validation Checks: Coordinate bounds validation with user interaction
  • Error Recovery: Graceful handling of database connection issues

Output​

The script provides detailed console output including:

  • Processing progress for each table
  • Batch commit confirmations
  • Error messages for problematic records
  • Final summary of updated record counts
  • Database connection status

Applications​

This script is particularly useful for:

  • Water utility network modeling
  • Infrastructure asset management systems
  • Geographic Information System (GIS) data integration
  • Web-based mapping applications
  • Cross-regional data standardization

Notes​

  • The script is designed for PostgreSQL databases with specific table schemas
  • Coordinate validation can be disabled for known coordinate ranges
  • Batch processing can be adjusted based on available memory and performance requirements
  • The script maintains data integrity through transaction management
Last updated on by Jake