Add new fact and bridge SQLAlchemy models #56

New Issue

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

lmiranda commented

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

Overview

Add SQLAlchemy ORM models for new fact tables and CMHC-neighbourhood bridge table.

File to Modify

portfolio_app/toronto/models/facts.py

Models to Add

BridgeCMHCNeighbourhood

class BridgeCMHCNeighbourhood(Base):
    """Bridge table for CMHC zone to neighbourhood mapping with area weights."""
    __tablename__ = "bridge_cmhc_neighbourhood"
    
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    cmhc_zone_code: Mapped[str] = mapped_column(String(10), nullable=False)
    neighbourhood_id: Mapped[int] = mapped_column(Integer, nullable=False)
    weight: Mapped[float] = mapped_column(Numeric(5, 4), nullable=False)  # 0-1

FactCensus

class FactCensus(Base):
    """Census statistics by neighbourhood and year."""
    __tablename__ = "fact_census"
    
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    neighbourhood_id: Mapped[int] = mapped_column(Integer, nullable=False)
    census_year: Mapped[int] = mapped_column(Integer, nullable=False)
    population: Mapped[int | None]
    median_household_income: Mapped[float | None]
    # ... additional census indicators

FactCrime

class FactCrime(Base):
    """Crime statistics by neighbourhood and year."""
    __tablename__ = "fact_crime"
    
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    neighbourhood_id: Mapped[int] = mapped_column(Integer, nullable=False)
    year: Mapped[int] = mapped_column(Integer, nullable=False)
    crime_type: Mapped[str] = mapped_column(String(50), nullable=False)
    count: Mapped[int] = mapped_column(Integer, nullable=False)
    rate_per_100k: Mapped[float | None]

FactAmenities

class FactAmenities(Base):
    """Amenity counts by neighbourhood."""
    __tablename__ = "fact_amenities"
    
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    neighbourhood_id: Mapped[int] = mapped_column(Integer, nullable=False)
    amenity_type: Mapped[str] = mapped_column(String(50), nullable=False)
    count: Mapped[int] = mapped_column(Integer, nullable=False)
    year: Mapped[int] = mapped_column(Integer, nullable=False)

Also Update

portfolio_app/toronto/models/__init__.py - Export new models

Acceptance Criteria

All models use SQLAlchemy 2.0 style (Mapped, mapped_column)
Models follow existing patterns in facts.py
Import test passes
Linter passes

Labels

Type/Feature, Priority/High, Component/Backend, Component/Database, Tech/Python

## Overview Add SQLAlchemy ORM models for new fact tables and CMHC-neighbourhood bridge table. ## File to Modify `portfolio_app/toronto/models/facts.py` ## Models to Add ### BridgeCMHCNeighbourhood ```python class BridgeCMHCNeighbourhood(Base): """Bridge table for CMHC zone to neighbourhood mapping with area weights.""" __tablename__ = "bridge_cmhc_neighbourhood" id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True) cmhc_zone_code: Mapped[str] = mapped_column(String(10), nullable=False) neighbourhood_id: Mapped[int] = mapped_column(Integer, nullable=False) weight: Mapped[float] = mapped_column(Numeric(5, 4), nullable=False) # 0-1 ``` ### FactCensus ```python class FactCensus(Base): """Census statistics by neighbourhood and year.""" __tablename__ = "fact_census" id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True) neighbourhood_id: Mapped[int] = mapped_column(Integer, nullable=False) census_year: Mapped[int] = mapped_column(Integer, nullable=False) population: Mapped[int | None] median_household_income: Mapped[float | None] # ... additional census indicators ``` ### FactCrime ```python class FactCrime(Base): """Crime statistics by neighbourhood and year.""" __tablename__ = "fact_crime" id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True) neighbourhood_id: Mapped[int] = mapped_column(Integer, nullable=False) year: Mapped[int] = mapped_column(Integer, nullable=False) crime_type: Mapped[str] = mapped_column(String(50), nullable=False) count: Mapped[int] = mapped_column(Integer, nullable=False) rate_per_100k: Mapped[float | None] ``` ### FactAmenities ```python class FactAmenities(Base): """Amenity counts by neighbourhood.""" __tablename__ = "fact_amenities" id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True) neighbourhood_id: Mapped[int] = mapped_column(Integer, nullable=False) amenity_type: Mapped[str] = mapped_column(String(50), nullable=False) count: Mapped[int] = mapped_column(Integer, nullable=False) year: Mapped[int] = mapped_column(Integer, nullable=False) ``` ## Also Update `portfolio_app/toronto/models/__init__.py` - Export new models ## Acceptance Criteria - [ ] All models use SQLAlchemy 2.0 style (Mapped, mapped_column) - [ ] Models follow existing patterns in facts.py - [ ] Import test passes - [ ] Linter passes ## Labels `Type/Feature`, `Priority/High`, `Component/Backend`, `Component/Database`, `Tech/Python`

lmiranda commented

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

Starting implementation of SQLAlchemy models for FactCensus, FactCrime, FactAmenities, and BridgeCMHCNeighbourhood.

lmiranda commented

2026-01-16 15:57:57 +00:00

Implementation complete:

Added BridgeCMHCNeighbourhood - CMHC zone to neighbourhood mapping with weights
Added FactCensus - Census statistics by neighbourhood/year
Added FactCrime - Crime statistics by neighbourhood/year/type
Added FactAmenities - Amenity counts by neighbourhood/type/year
Added appropriate indexes for query performance
Updated models/__init__.py with exports
Import test passed

Implementation complete: - Added `BridgeCMHCNeighbourhood` - CMHC zone to neighbourhood mapping with weights - Added `FactCensus` - Census statistics by neighbourhood/year - Added `FactCrime` - Crime statistics by neighbourhood/year/type - Added `FactAmenities` - Amenity counts by neighbourhood/type/year - Added appropriate indexes for query performance - Updated `models/__init__.py` with exports - Import test passed

lmiranda closed this issue

2026-01-16 15:57:59 +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:49 +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#56