Notebook
09
EngineeringModelled
Spatial Database
41-table PostGIS schema · 5 schemas · GeoAlchemy2 ORM · ISO 19115 metadata · Full DDL
41
across 5 schemas
Total tables
PostGIS
PostgreSQL + spatial
Database
EPSG:4326
WGS84 geographic
Coordinate system
ISO 19115
per table
Metadata standard
Database Schema (41 tables, 5 schemas)
surveyGeodetic and survey control data
- ·cors_stations
- ·benchmarks
- ·gcp_points
- ·tide_gauges
- ·lidar_blocks
- ·orthophoto_tiles
- ·dem_tiles
basemapBase map layers: streets, buildings, parcels
- ·streets
- ·buildings
- ·parcels
- ·admin_boundaries
- ·land_use_existing
- ·hydrology
- ·contours
- ·natural_features
- ·utility_lines
planningPlanning, zoning, and statutory layers
- ·zoning_framework
- ·heritage_overlay
- ·flood_zones
- ·setback_lines
- ·planning_areas
- ·growth_reserve
- ·green_network
- ·transport_corridors
- ·utility_corridors
- ·permit_applications
infrastructureInfrastructure assets: water, power, roads
- ·water_supply_network
- ·swro_facilities
- ·pump_stations
- ·reservoirs
- ·power_transmission
- ·substations
- ·solar_farms
- ·road_network
- ·bridges_culverts
- ·pipeline_route
managementOperational and management data
- ·permit_applications
- ·enforcement_actions
- ·property_transactions
- ·valuations
- ·maintenance_records
GeoAlchemy2 ORM Models
Python ORM models allow notebooks to read and write spatial data without raw SQL. Six key tables are modelled:
# Connect and query — example usage
from sqlalchemy.orm import Session
from geoalchemy2.functions import ST_Within
# All buildings within planning boundary
with Session(engine) as session:
tall = session.query(Building)
.filter(Building.height_m > 20)
.all()
# Upload GeoDataFrame to PostGIS
gdf.to_postgis('buildings', engine,
schema='basemap', if_exists='replace')
ISO 19115 Metadata Per Table
A helper function add_iso19115_columns() adds standard metadata columns to every table:
| Column | Type | Purpose |
|---|---|---|
| md_identifier | UUID | Unique record identifier |
| md_title | TEXT | Dataset title |
| md_abstract | TEXT | Description |
| md_language | CHAR(3) | ISO 639 language code |
| md_topic_category | TEXT | ISO 19115 topic |
| md_date_created | DATE | Record creation |
| md_date_modified | DATE | Last modification |
| md_lineage | TEXT | Data provenance |
| md_resolution | FLOAT | Spatial resolution (m) |
| md_access_constraints | TEXT | Use limitations |
Library: GeoAlchemy2 + SQLAlchemy
GeoAlchemy2 v0.14 ORM layer enables Python-native PostGIS interaction. Six key ORM classes: CorsStation, Street, Building,WaterSupplyNetwork, ZoningFramework, PermitApplication. Set DATABASE_URL environment variable to deploy all 41 tables viaBase.metadata.create_all(engine).