Create CMHC-neighbourhood crosswalk loader #59

New Issue

lmiranda · 2026-01-16T15:49:56Z

lmiranda commented

2026-01-16 15:49:56 +00:00

Overview

Build CMHC zone to neighbourhood bridge with area overlap weights using PostGIS.

File to Create

portfolio_app/toronto/loaders/cmhc_crosswalk.py

def build_cmhc_neighbourhood_crosswalk(
    session: Session | None = None,
) -> int:
    """Calculate area overlap weights between CMHC zones and neighbourhoods.
    
    Uses PostGIS ST_Intersection and ST_Area to compute weights.
    Returns count of bridge records created.
    """

Algorithm

Query all CMHC zones with geometry from dim_cmhc_zone
Query all neighbourhoods with geometry from dim_neighbourhood
For each overlapping pair, calculate:
- Intersection area using ST_Intersection(zone.geometry, neighbourhood.geometry)
- Weight = intersection_area / zone_total_area
Insert into bridge_cmhc_neighbourhood with weights

SQL Example

SELECT 
    z.zone_code,
    n.neighbourhood_id,
    ST_Area(ST_Intersection(z.geometry, n.geometry)) / ST_Area(z.geometry) as weight
FROM dim_cmhc_zone z
JOIN dim_neighbourhood n 
    ON ST_Intersects(z.geometry, n.geometry)
WHERE ST_Area(ST_Intersection(z.geometry, n.geometry)) > 0;

Acceptance Criteria

Uses PostGIS functions for accurate geometric calculations
Weights sum to approximately 1.0 for each CMHC zone
Handles edge cases (no overlap, complete containment)
Idempotent (clears existing crosswalk before rebuilding)
Import test passes
Linter passes

Labels

Type/Feature, Priority/Low, Component/Backend, Component/Database, Tech/Python, Tech/PostgreSQL

## Overview Build CMHC zone to neighbourhood bridge with area overlap weights using PostGIS. ## File to Create `portfolio_app/toronto/loaders/cmhc_crosswalk.py` ```python def build_cmhc_neighbourhood_crosswalk( session: Session | None = None, ) -> int: """Calculate area overlap weights between CMHC zones and neighbourhoods. Uses PostGIS ST_Intersection and ST_Area to compute weights. Returns count of bridge records created. """ ``` ## Algorithm 1. Query all CMHC zones with geometry from `dim_cmhc_zone` 2. Query all neighbourhoods with geometry from `dim_neighbourhood` 3. For each overlapping pair, calculate: - Intersection area using `ST_Intersection(zone.geometry, neighbourhood.geometry)` - Weight = intersection_area / zone_total_area 4. Insert into `bridge_cmhc_neighbourhood` with weights ## SQL Example ```sql SELECT z.zone_code, n.neighbourhood_id, ST_Area(ST_Intersection(z.geometry, n.geometry)) / ST_Area(z.geometry) as weight FROM dim_cmhc_zone z JOIN dim_neighbourhood n ON ST_Intersects(z.geometry, n.geometry) WHERE ST_Area(ST_Intersection(z.geometry, n.geometry)) > 0; ``` ## Acceptance Criteria - [ ] Uses PostGIS functions for accurate geometric calculations - [ ] Weights sum to approximately 1.0 for each CMHC zone - [ ] Handles edge cases (no overlap, complete containment) - [ ] Idempotent (clears existing crosswalk before rebuilding) - [ ] Import test passes - [ ] Linter passes ## Labels `Type/Feature`, `Priority/Low`, `Component/Backend`, `Component/Database`, `Tech/Python`, `Tech/PostgreSQL`

lmiranda commented

2026-01-16 16:03:36 +00:00

Implementation complete:

Created loaders/cmhc_crosswalk.py with:
- build_cmhc_neighbourhood_crosswalk() - Uses PostGIS ST_Intersection/ST_Area for accurate geometric calculations
- get_neighbourhood_weights_for_zone() - Query weights for a zone
- disaggregate_zone_value() - Helper to disaggregate zone values to neighbourhoods
Idempotent (clears before rebuilding)
Updated loaders/__init__.py with exports
Import test passed

Implementation complete: - Created `loaders/cmhc_crosswalk.py` with: - `build_cmhc_neighbourhood_crosswalk()` - Uses PostGIS ST_Intersection/ST_Area for accurate geometric calculations - `get_neighbourhood_weights_for_zone()` - Query weights for a zone - `disaggregate_zone_value()` - Helper to disaggregate zone values to neighbourhoods - Idempotent (clears before rebuilding) - Updated `loaders/__init__.py` with exports - Import test passed

lmiranda closed this issue

2026-01-16 16:03:39 +00:00

lmiranda added this to the Launch: Host, Bio and Toronto House Market Analysis project 2026-01-16 16:06:20 +00:00

lmiranda self-assigned this 2026-01-16 16:06:30 +00:00

lmiranda moved this to Done in Launch: Host, Bio and Toronto House Market Analysis on 2026-01-16 16:06:54 +00:00

lmiranda referenced this issue from a commit

2026-01-16 16:55:12 +00:00

feat: Implement Phase 3 neighbourhood data model

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: personal-projects/personal-portfolio#59