Skip to main content

DNV Water Distribution Network Base Demand Assignment Workflow

Overview​

This document describes the complete workflow for assigning base demand values to junctions in a water distribution network model. The workflow begins with spatial joins in QGIS to establish relationships between network components (buildings, parcels, service connections, mains, and water meters), then traces demand from buildings (with assigned building types) through parcels, service connections, mains, and finally to network junctions.

Part 1: Completed Steps - From Buildings to Mains​

Step 0: Spatial Joins to Establish Parcel Relationships​

Objective: Establish spatial relationships between all network components (buildings, service connections, mains, water meters) and parcels, creating a foundation for demand aggregation.

Source Data (from shp_v2/ directory):

  • Buildings (1_BldBuilding_shp/BldBuilding.shp): Polygon layer containing building footprints with building type attributes (BLDGTYPE, BLDGCLASS_)
  • Parcels (2_CadParcels_shp/CadParcels.shp): Polygon layer containing cadastral parcel boundaries with unique parcel IDs (DBLINK)
  • Service Connections (3_WtrServiceConnection_shp/WtrServiceConnection.shp): Line layer representing water service connection pipes
  • Water Mains (6_WtrMain_shp_v2/WtrMain.shp): Line layer representing water distribution mains
  • Water Meters (4_5_water_meter_dnv/water_meters_export.shp): Point layer containing water meter locations with consumption data

Spatial Join Strategy: The workflow used QGIS to perform spatial joins, following a two-phase approach:

  1. Phase 1: Spatial Joins to Parcels (establishing spatial relationships):

    • All layers were spatially joined to the parcels layer to assign a ParcelID (from parcels DBLINK field) to each feature
    • This created a common reference system where all network components could be linked through their parcel associations

  2. Phase 2: Attribute Joins (after spatial relationships established):

    • Once spatial relationships were established, attribute joins were performed using the parcel IDs
    • This allowed buildings, service connections, and meters to be associated with each other through their shared parcel relationships

Spatial Join Operations Performed:

  1. Buildings → Parcels:

    • Method: Polygon-to-polygon spatial join (intersection)
    • Result: D_BuildingWithParcelId.csv - Each building assigned to its containing parcel via DBLINK field

  2. Service Connections → Parcels:

    • Method: Line-to-polygon spatial join (intersection or proximity)
    • Result: B_SCwithParcelId.csv - Each service connection assigned to nearest/intersecting parcel via DBLINK_1 field

  3. Service Connections → Mains:

    • Method: Line-to-line spatial join (proximity/intersection)
    • Result: A_SCwithMainId.csv - Each service connection assigned to nearest main via ASSET_ID_1 field

  4. Water Meters → Parcels:

    • Method: Point-to-polygon spatial join using two approaches:
      • Closest 5m: Nearest neighbor join within 5-meter buffer (used in final analysis)
      • Intersect 1m: Intersection join within 1-meter buffer (alternative method, not used)
    • Result: C_WMwithParcelId_Closest_5m.csv (used) and C_WMwithParcelId_Intersect_1m.csv (alternative)
    • Final Selection: The "closest 5m" method was selected for the final analysis as it provided better coverage and more reliable associations

Key Rationale:

  • Parcels serve as the fundamental spatial unit linking all network components
  • By joining everything to parcels first, the workflow creates a hierarchical relationship: Parcels → Buildings, Service Connections, Meters
  • This parcel-centric approach enables demand aggregation from buildings through parcels to service connections and ultimately to mains

Output Files Created:

  • D_BuildingWithParcelId.csv - Buildings with parcel IDs
  • B_SCwithParcelId.csv - Service connections with parcel IDs
  • A_SCwithMainId.csv - Service connections with main IDs
  • C_WMwithParcelId_Closest_5m.csv - Water meters with parcel IDs (used in analysis)
  • C_WMwithParcelId_Intersect_1m.csv - Alternative water meter to parcel join (not used)

Step 1: Building Type Base Demand Calculation​

Objective: Calculate average demand per building type using real meter data from parcels with single building types.

Prerequisites: Spatial joins from Step 0 must be completed to establish parcel relationships.

Process:

  1. Identify Single-Type Parcels: Filter parcels that contain only buildings of a single BLDGTYPE + BLDGCLASS_ combination

    • Source: D_BuildingWithParcelId table (created in Step 0 via spatial join)
    • Logic: Parcels where COUNT(DISTINCT BLDGTYPE || '|' || BLDGCLASS_) = 1

  2. Associate Meters with Parcels:

    • Used C_WMwithParcelId_Closest_5m.csv (created in Step 0 via spatial join - meters within 5m of parcels)
    • Alternative: C_WMwithParcelId_Intersect_1m.csv (alternative spatial join method, not used)
    • Result: meters_with_single_building_type table

  3. Extract Meter Data:

    • Query DeviceTagData for meters with Units = 'LPS'
    • Filter to meters in single-type parcels
    • Join with building type/class information
    • Result: meter_timeseries_with_bldg_type_class.csv

  4. Calculate Average Demand by Building Type:

    • Aggregate meter data by BLDGTYPE
    • Calculate average demand (LPS) for each building type
    • Result: july_2023_avg_demand_by_bldg_type.csv with 11 building types:
      • CHURCH: 0.024974261 LPS
      • COMMERCIAL BUILDING: 0.04553726 LPS
      • GOVERNMENT HERITAGE BUILDING: 0.005024497 LPS
      • INDUSTRIAL BUILDING: 0.026891404544580184 LPS
      • MIXED USE: 0.058638603867555684 LPS
      • MULTI FAMILY: 0.2143962256022281 LPS
      • OTHER: 0.11843456465664087 LPS
      • RECREATION FACILITY: 0.29122198106779584 LPS
      • SCHOOL: 0.04053746 LPS
      • SINGLE FAMILY: 0.023858310290417913 LPS
      • SKI: 2.1797343286124495 LPS

Files Created:

  • sql/meters_single_building_type.sql
  • sql/get_meter_data.sql
  • meters_with_single_building_type.csv
  • july_2023_avg_demand_by_bldg_type.csv

Step 2: Assign Base Demand to Buildings​

Objective: Add BaseDemand column to buildings table and populate based on building type.

Process:

  1. Add Column: Add BaseDemand NUMERIC column to D_BuildingWithParcelId table
  2. Update Values: Update each building's BaseDemand based on its BLDGTYPE matching the values from Step 1

SQL Script: sql/add_base_demand_column.sql

Result: All buildings in D_BuildingWithParcelId now have a BaseDemand value assigned based on their building type.

Step 3: Aggregate Parcel Demands​

Objective: Calculate total base demand for each parcel by summing all building demands within that parcel.

Process:

  1. Create ParcelsWithDemand Table:
    • Group buildings by DBLINK (parcel ID)
    • Sum BaseDemand for all buildings in each parcel
    • Result: ParcelsWithDemand table with columns:
      • ParcelId (DBLINK)
      • TotalBaseDemand (sum of all building demands)

SQL Script: sql/create_parcels_with_demand.sql

Key Logic:

SELECT DBLINK as ParcelId, SUM(BaseDemand) as TotalBaseDemand
FROM D_BuildingWithParcelId
WHERE DBLINK != '' AND BaseDemand IS NOT NULL
GROUP BY DBLINK

Step 4: Assign Demand to Service Connections​

Objective: Distribute parcel demand equally among all service connections in that parcel.

Process:

  1. Join Service Connections with Parcels:

    • Source: B_SCwithParcelId (service connections with parcel IDs via DBLINK_1)
    • Join with ParcelsWithDemand on parcel ID

  2. Calculate Demand per Service Connection:

    • For each parcel, count the number of service connections
    • Divide parcel's total demand equally: ParcelTotalDemand / ServiceConnectionCount
    • This ensures all service connections in a parcel sum to the parcel's total demand

SQL Script: sql/create_sc_with_base_demand.sql

Result: SCwithBaseDemand table with columns:

  • ServiceConnectionId (ASSET_ID)
  • TotalBaseDemand (divided equally among SCs in parcel)
  • ParcelId (DBLINK_1)
  • ServiceConnectionsInParcel (count)
  • ParcelTotalDemand (original parcel total)

Key Logic:

-- Count SCs per parcel, then divide parcel demand equally
TotalBaseDemand = ParcelTotalDemand / ServiceConnectionCount

Step 5: Aggregate Main Demands​

Objective: Sum base demand from all service connections attached to each water main.

Process:

  1. Join Service Connections with Mains:

    • Source: A_SCwithMainId (service connections with main IDs via ASSET_ID_1)
    • Join with SCwithBaseDemand on service connection ID (ASSET_ID)

  2. Sum Demand per Main:

    • Group by main ID (ASSET_ID_1)
    • Sum TotalBaseDemand from all service connections
    • Count number of service connections per main

SQL Script: sql/create_mains_with_total_base_demand.sql

Result: MainsWithTotalBaseDemand table with columns:

  • MainId (ASSET_ID_1)
  • TotalBaseDemand (sum of all SC demands)
  • ServiceConnectionCount (number of SCs on this main)

Key Logic:

SELECT ASSET_ID_1 as MainId, SUM(TotalBaseDemand) as TotalBaseDemand
FROM A_SCwithMainId scm
JOIN SCwithBaseDemand scd ON scm.ASSET_ID = scd.ServiceConnectionId
GROUP BY ASSET_ID_1

Part 2: Next Steps - From Mains to Junctions​

Step 6: Determine Flow Direction for Each Main​

Objective: Determine the downstream (assignment) node for each main using hydraulic simulation results.

Process:

  1. Access Hydraulic Results:

    • Query LinkHydrs table for ScenarioId = 1
    • Each record contains:
      • Id: Link/Main ID (foreign key to Links table)
      • Flow: Flow rate (positive = FromNode→ToNode, negative = ToNode→FromNode)
      • SimUnixTime: Simulation timestamp
      • ScenarioId: Scenario identifier (use 1)

  2. Get Link Topology:

    • Join LinkHydrs with Links table to get:
      • FromNode: Upstream node ID
      • ToNode: Downstream node ID
    • Note: Links table contains all link types (Pipes, Pumps, Valves)
    • Filter to Pipes only (mains) if needed

  3. Calculate Average Flow Direction:

    • Critical: Some pipes experience flow direction oscillation during simulation (flow alternates between positive and negative values)
    • Using a single timestamp (e.g., the last timestamp) would arbitrarily assign direction based on the final state, which may not represent the dominant flow pattern
    • Solution: Calculate the average flow across all timestamps for each main
    • This identifies the dominant flow direction over the entire simulation period
    • If average flow is positive → dominant direction is FromNode→ToNode
    • If average flow is negative → dominant direction is ToNode→FromNode
    • If average flow is zero → no dominant direction, default to ToNode

  4. Determine Assignment Node:

    • If AvgFlow > 0: Dominant flow is FromNode → ToNode, so assignment node = ToNode
    • If AvgFlow < 0: Dominant flow is ToNode → FromNode, so assignment node = FromNode
    • If AvgFlow = 0: No dominant direction, default to ToNode

Key Logic:

-- Calculate average flow across all timestamps
WITH AverageHydraulicResults AS (
  SELECT Id, AVG(Flow) as AvgFlow
  FROM LinkHydrs
  WHERE ScenarioId = 1
  GROUP BY Id
)
-- Determine downstream/assignment node based on average flow direction
AssignmentNode = CASE
  WHEN AvgFlow > 0 THEN ToNode
  WHEN AvgFlow < 0 THEN FromNode
  ELSE ToNode  -- Zero average flow: default to ToNode
END

Rationale for Average Flow Approach:

  • Problem: Flow direction can oscillate during simulation (especially in pipes with varying demand patterns)
  • Issue with Last Timestamp: Using the final timestamp arbitrarily selects one direction, which may not represent the overall flow pattern
  • Solution: Averaging flow values identifies the dominant direction over the entire simulation period
  • Result: More stable and representative assignment of demands to downstream junctions

Step 7: Create Mains with Assignment Nodes Table​

Objective: Create an intermediate table linking mains to their assignment (downstream) nodes.

Process:

  1. Join Mains with Hydraulic Results:

    • Join MainsWithTotalBaseDemand with LinkHydrs on MainId = LinkId
    • Filter to ScenarioId = 1
    • Join with Links to get FromNode and ToNode

  2. Calculate Assignment Node:

    • Apply flow direction logic from Step 6
    • Create MainsWithAssignmentNodes table with columns:
      • MainId
      • AssignmentNodeId (downstream node)
      • TotalBaseDemand
      • Flow (for verification)
      • FromNode, ToNode (for reference)

SQL Script: sql/create_mains_with_assignment_nodes.sql (to be created)

Key Logic:

SELECT
  m.MainId,
  CASE
    WHEN lh.Flow > 0 THEN l.ToNode
    WHEN lh.Flow < 0 THEN l.FromNode
    ELSE NULL
  END as AssignmentNodeId,
  m.TotalBaseDemand,
  lh.Flow,
  l.FromNode,
  l.ToNode
FROM MainsWithTotalBaseDemand m
JOIN LinkHydrs lh ON m.MainId = lh.Id AND lh.ScenarioId = 1
JOIN Links l ON lh.Id = l.Id

Step 8: Aggregate Junction Demands​

Objective: Sum base demand from all mains assigned to each junction.

Process:

  1. Group by Assignment Node:

    • Group MainsWithAssignmentNodes by AssignmentNodeId
    • Sum TotalBaseDemand for all mains assigned to each junction
    • Count number of mains per junction

  2. Create Final Table:

    • Create JunctionsWithBaseDemand table with columns:
      • JunctionId (node ID)
      • TotalBaseDemand (sum of all main demands)
      • MainCount (number of mains assigned to this junction)

SQL Script: sql/create_junctions_with_base_demand.sql (to be created)

Key Logic:

SELECT
  AssignmentNodeId as JunctionId,
  SUM(TotalBaseDemand) as TotalBaseDemand,
  COUNT(*) as MainCount
FROM MainsWithAssignmentNodes
WHERE AssignmentNodeId IS NOT NULL
GROUP BY AssignmentNodeId

Step 9: Update Junction Base Demand Values​

Objective: Update the Junctions table with calculated base demand values.

Process:

  1. Update Junction Records:

    • Update Junctions table BaseDemand field
    • Join with JunctionsWithBaseDemand on Id = JunctionId
    • Set BaseDemand = TotalBaseDemand

  2. Handle Missing Junctions:

    • Junctions not in JunctionsWithBaseDemand should have BaseDemand = 0 or remain unchanged
    • Verify all expected junctions are accounted for

SQL Script: sql/update_junction_base_demands.sql (to be created)

Key Logic:

UPDATE Junctions j
SET BaseDemand = COALESCE(jbd.TotalBaseDemand, 0)
FROM JunctionsWithBaseDemand jbd
WHERE j.Id = jbd.JunctionId

Data Flow Summary​

Source Shapefiles (shp_v2/)
  ↓ [Step 0: Spatial joins in QGIS - establish parcel relationships]
CSV Files with Parcel IDs:
  - D_BuildingWithParcelId (Buildings → Parcels)
  - B_SCwithParcelId (Service Connections → Parcels)
  - A_SCwithMainId (Service Connections → Mains)
  - C_WMwithParcelId_Closest_5m (Water Meters → Parcels)
  ↓ [Step 1: Calculate avg demand by type from meter data]
Building Type Average Demands
  ↓ [Step 2: Assign BaseDemand to each building]
Buildings (with BaseDemand)
  ↓ [Step 3: Sum by parcel]
ParcelsWithDemand (TotalBaseDemand per parcel)
  ↓ [Step 4: Divide equally among SCs]
SCwithBaseDemand (demand per service connection)
  ↓ [Step 5: Sum by main]
MainsWithTotalBaseDemand (demand per main)
  ↓ [Step 6-7: Determine flow direction, get assignment node]
MainsWithAssignmentNodes (main → downstream junction)
  ↓ [Step 8: Sum by junction]
JunctionsWithBaseDemand (demand per junction)
  ↓ [Step 9: Update Junctions table]
Junctions.BaseDemand (final values)

Database Schema Reference​

Key Tables (from efcorelibraries)​

  • Links: Id, FromNode, ToNode (base class for Pipes, Pumps, Valves)
  • Pipes: Extends Link (these are the mains)
  • LinkHydrs: Id (Link ID), Flow, ScenarioId, SimUnixTime
  • Junctions: Id, BaseDemand (to be updated)

Key Tables (from spatial joins and CSV processing)​

  • D_BuildingWithParcelId: Buildings with DBLINK (from spatial join to parcels), BLDGTYPE, BaseDemand (added in Step 2)
  • B_SCwithParcelId: Service connections with ASSET_ID, DBLINK_1 (from spatial join to parcels)
  • A_SCwithMainId: Service connections with ASSET_ID, ASSET_ID_1 (main ID, from spatial join to mains)
  • C_WMwithParcelId_Closest_5m: Water meters with parcel associations (from spatial join, closest 5m method)
  • ParcelsWithDemand: ParcelId, TotalBaseDemand (calculated in Step 3)
  • SCwithBaseDemand: ServiceConnectionId, TotalBaseDemand, ParcelId (calculated in Step 4)
  • MainsWithTotalBaseDemand: MainId, TotalBaseDemand, ServiceConnectionCount (calculated in Step 5)

Questions Requiring Clarification​

Flow Direction Determination​

  1. Which SimUnixTime should be used?

    • ✅ ANSWERED: Calculate average flow across all timestamps for each main
    • Reasoning: Some pipes oscillate between positive/negative flow. Averaging identifies the dominant direction over the entire simulation period, avoiding arbitrary assignment based on final state.

  2. Zero Flow Handling:

    • ✅ ANSWERED: When average flow = 0, default to ToNode as the downstream node

  3. Flow Direction Consistency:

    • Not a concern - using average flow across all timestamps identifies dominant direction

Junction Assignment​

  1. Multiple Mains to Same Junction:

    • ✅ ANSWERED: Sum demands from all mains assigned to the same junction (handled in aggregation)

  2. Junctions Without Assigned Mains:

    • ✅ ANSWERED: Leave base demand unchanged (don't set to 0)

  3. Main-to-Junction Mapping:

    • ✅ ANSWERED: Only process mains with base demand > 0 that have hydraulic results

Data Validation​

  1. Verification Steps:

    • ✅ ANSWERED: Not required for now

  2. Missing Data Handling:

    • ✅ ANSWERED: Not a concern - tables have default values, only update junctions that receive assignments

SQL Script Execution Order​

Execute the following scripts in order:

  1. sql/add_base_demand_column.sql - Add BaseDemand column to buildings
  2. sql/create_parcels_with_demand.sql - Create ParcelsWithDemand table
  3. sql/create_sc_with_base_demand.sql - Create SCwithBaseDemand table
  4. sql/create_mains_with_total_base_demand.sql - Create MainsWithTotalBaseDemand table
  5. sql/create_mains_with_assignment_nodes.sql - Create MainsWithAssignmentNodes table ✅
  6. sql/create_junctions_with_base_demand.sql - Create JunctionsWithBaseDemand table ✅
  7. sql/update_junction_base_demands.sql - Update Junctions table ✅

Implementation Details​

Step 6-7: Mains with Assignment Nodes​

  • Uses AVG(Flow) aggregation to calculate average flow across all timestamps per main
  • Filters to ScenarioId = 1 and TotalBaseDemand > 0
  • Flow direction logic based on average flow:
    • AvgFlow > 0: AssignmentNode = ToNode (dominant flow FromNode→ToNode)
    • AvgFlow < 0: AssignmentNode = FromNode (dominant flow ToNode→FromNode)
    • AvgFlow = 0: AssignmentNode = ToNode (default, no dominant direction)
  • Rationale: Handles pipes where flow oscillates during simulation by identifying dominant direction

Step 8: Junction Aggregation​

  • Groups mains by AssignmentNodeId
  • Sums TotalBaseDemand for all mains assigned to each junction
  • Counts number of mains per junction

Step 9: Junction Updates​

  • Updates only junctions that have assigned mains
  • Leaves other junctions unchanged (preserves default/existing values)

Monthly Processing Automation​

Monthly Processing Overview​

The workflow needs to be repeated for each month (12 months total) since building type average demands vary by month. A Python automation script has been created to streamline this process.

Automation Script: run_monthly_demand_assignment.py​

Purpose: Automate the complete workflow for multiple months by:

  1. Loading monthly building type averages from CSV
  2. Updating building base demands for the specified month
  3. Re-running the complete aggregation chain (parcels → SCs → mains → junctions)

Key Features:

  • Processes single month or all months
  • Reads monthly averages from Average of Demand by Month and BLDGTYPE.csv
  • Executes SQL scripts in correct sequence
  • Provides progress feedback and error handling
  • Can list available months without processing

Usage Examples:

# List available months
python run_monthly_demand_assignment.py --list-months

# Process a single month
python run_monthly_demand_assignment.py --month January --db-connection "postgresql://user:pass@host/db"

# Process all months
python run_monthly_demand_assignment.py --all-months --db-connection "postgresql://user:pass@host/db"

# Custom paths
python run_monthly_demand_assignment.py --month February \
  --db-connection "postgresql://user:pass@host/db" \
  --sql-dir sql \
  --monthly-data "Average of Demand by Month and BLDGTYPE.csv"

What Changes Per Month:

  • Building type average demands (from monthly CSV)
  • Building BaseDemand values (updated in D_BuildingWithParcelId)
  • All downstream aggregations (parcels, SCs, mains, junctions)

What Stays Constant:

  • Building-to-parcel relationships
  • Service connection-to-parcel relationships
  • Service connection-to-main relationships
  • Main topology and flow directions (from hydraulic simulation)

Note: The script overwrites intermediate tables each month. If you need to preserve monthly results, you may want to modify the script to create month-specific table names (e.g., JunctionsWithBaseDemand_January).

QA and Intermediate Data Export​

QA Overview​

For quality assurance and data verification, additional scripts have been created to:

  1. Split monthly averages into individual CSV files
  2. Generate monthly mains demand CSVs for comparison
  3. Insert junction demands into NodeProperties table with scenario IDs

Script 1: Split Monthly Averages (split_monthly_avg_demand.py)​

Purpose: Split the combined monthly averages CSV into individual month files for easier review and QA.

Output: Creates files in ./monthly_avg_demand/ directory:

  • january_2023_avg_demand_by_bldg_type.csv
  • february_2023_avg_demand_by_bldg_type.csv
  • ... (one per month)

Format: Matches july_2023_avg_demand_by_bldg_type.csv format:

Average of Demand,BLDGTYPE
0.024974261,CHURCH
0.04553726,COMMERCIAL BUILDING
...

Usage:

python split_monthly_avg_demand.py
python split_monthly_avg_demand.py --input "Average of Demand by Month and BLDGTYPE.csv" --output-dir "monthly_avg_demand"

Script 2: Generate Monthly Mains CSVs (generate_monthly_mains_demand.py)​

Purpose: Generate CSV files for mains with total base demand for each month, similar to july_2023_mains_with_total_base_demand.csv. This is a pure CSV-to-CSV transformation tool for QA purposes with NO database dependencies.

Output: Creates files in ./mains_with_monthly_base_demand/ directory:

  • january_2023_mains_with_total_base_demand.csv
  • february_2023_mains_with_total_base_demand.csv
  • ... (one per month)

Format: Matches july_2023_mains_with_total_base_demand.csv format:

MainId,TotalBaseDemand,ServiceConnectionCount
WTRMN00012,0,1
WTRMN00102,0.0954332411616717,5
...

Process (Pure CSV operations, no database):

  1. Reads monthly average demand files from monthly_avg_demand/ directory
  2. Reads buildings from D_BuildingWithParcelId.csv and assigns base demand based on building type
  3. Aggregates by parcel (in-memory)
  4. Reads service connections from B_SCwithParcelId.csv and divides parcel demands equally
  5. Reads main IDs from A_SCwithMainId.csv and sums SC demands by main
  6. Exports to CSV in mains_with_monthly_base_demand/ directory

Usage:

# List available months
python generate_monthly_mains_demand.py --list-months

# Generate for a single month
python generate_monthly_mains_demand.py --month January

# Generate for all months
python generate_monthly_mains_demand.py --all-months

Note: This script does NOT require a database connection. It's designed for QA and verification by processing CSVs directly.

Script 3: Insert Junction Demands from Mains CSVs (insert_junction_demands_from_mains.py)​

Purpose: Read monthly mains CSV files and assign their demands to junctions based on hydraulic simulation flow direction, then insert into the NodeProperties table with scenario-specific IDs.

⚠️ Note: This is the CORRECT script to use. Do NOT use insert_junction_demands_to_node_properties.py (which incorrectly re-runs the full aggregation workflow).

Key Details:

  • Uses EN_BASEDEMAND = 1 (from EN_NodeProperty enum)
  • Each month gets its own ScenarioId (e.g., January=2, February=3, etc.)
  • Uses INSERT ... ON CONFLICT to handle updates
  • CRITICAL: Does NOT re-run the full aggregation workflow; instead reads pre-calculated mains demands from CSV

Process:

  1. Reads mains demands from monthly CSV files (e.g., january_2023_mains_with_total_base_demand.csv)
  2. Queries Links table for topology (FromNode, ToNode) where Link ID matches Main ID
  3. Queries LinkHydrs table (ScenarioId=1) and calculates average flow for each main
  4. Determines downstream (assignment) node based on flow direction:
    • If AvgFlow > 0: Assign to ToNode (flow is FromNode→ToNode)
    • If AvgFlow < 0: Assign to FromNode (flow is ToNode→FromNode)
    • If AvgFlow = 0: Default to ToNode
  5. Sums demands for all mains assigned to each junction
  6. Inserts into NodeProperties table:
    • NodeId: Junction ID
    • PropertyId: 1 (EN_BASEDEMAND)
    • PropertyValue: Calculated base demand (sum of assigned mains)
    • ScenarioId: Month-specific scenario ID

Usage:

# Process all months starting with scenario ID 2
python insert_junction_demands_from_mains.py --all-months --scenario-start 2 --db-connection "postgresql://user:pass@host/db"

# Process a single month with specific scenario ID
python insert_junction_demands_from_mains.py --month January --scenario-id 2 --db-connection "postgresql://user:pass@host/db"

# List available months
python insert_junction_demands_from_mains.py --list-months

Scenario ID Mapping:

  • ScenarioId 1: Hydraulic simulation results (existing, used for flow direction)
  • ScenarioId 2+: Monthly base demand scenarios (one per month)
  • Default starting scenario ID: 2 (can be changed with --scenario-start)

Database Schema:

NodeProperties (
  Id INT PRIMARY KEY (auto-generated),
  NodeId VARCHAR(31) NOT NULL,
  PropertyId INT NOT NULL,        -- 1 = EN_BASEDEMAND
  PropertyValue DOUBLE NOT NULL,
  CreatedAt DATETIME NOT NULL,    -- Set to CURRENT_TIMESTAMP on insert
  ModifiedAt DATETIME,            -- Set to CURRENT_TIMESTAMP on update
  ScenarioId INT                  -- NULL for defaults, non-null for scenarios
)

Important Notes:

  • This script reads mains demands from CSV files generated by generate_monthly_mains_demand.py. Make sure those CSV files exist in the ./mains_with_monthly_base_demand/ directory before running this script.
  • The script properly handles CreatedAt and ModifiedAt timestamps to avoid NULL constraint violations.
  • Skipped mains (due to missing topology or flow data) are written to a report file for post-insert analysis.

Complete Monthly Processing Workflow​

Recommended execution order:

Step 1: Prepare Monthly Data Files​

  1. Split monthly averages (one-time setup):

    python split_monthly_avg_demand.py

    Output: ./monthly_avg_demand/*_avg_demand_by_bldg_type.csv

  2. Generate monthly mains CSVs (CSV-only QA tool, no database required):

    python generate_monthly_mains_demand.py --all-months

    Output: ./mains_with_monthly_base_demand/*_mains_with_total_base_demand.csv

Step 2: Create Monthly Scenarios in Database​

  1. Create scenario records (one-time setup):
    psql -U postgres dnv_10_23_2025 -f sql/create_monthly_scenarios.sql
    This creates 12 scenario records (IDs 2-13) for each month.

Step 3: Insert Junction Demands to Database​

  1. Insert junction demands to NodeProperties:

    python insert_junction_demands_from_mains.py --all-months --scenario-start 2 --db-connection "postgresql://postgres:gq010102@localhost/dnv_10_23_2025"

    This reads mains CSVs, queries Links/LinkHydrs for flow direction, assigns to junctions, and inserts into NodeProperties.

    Output: Skipped mains reports are written to ./skipped_mains_reports/ for post-insert analysis.

Final Output Files​

CSV Files (for QA/verification):

  • ./monthly_avg_demand/*_avg_demand_by_bldg_type.csv - Building type averages per month (11 building types)
  • ./mains_with_monthly_base_demand/*_mains_with_total_base_demand.csv - Mains demands per month (~2800 mains)
  • ./skipped_mains_reports/*_skipped_mains.csv - Mains that were skipped during junction assignment (with reasons: no_topology or no_flow_data)

Database Tables (production data):

  • Scenarios table: 12 new scenario records (IDs 2-13) for monthly demand patterns
  • NodeProperties table: Junction base demands for each scenario (one set per month), with CreatedAt and ModifiedAt timestamps properly set
Last updated on by Jake