F-021: Nearby Transit & Schools

Status: Done · Priority: P2 · Branch: feature/F-021-nearby-transit-schools · Updated: Mar 5, 2026

Summary

Show the closest transit stops (REM, metro, bus) and schools on each listing detail page. Gives renters immediate context about commute options and family-friendliness of a location. Data is pre-seeded from free open data sources and periodically re-synced.

Requirements

• [x] points_of_interest table with source_id for stable upsert matching
• [ ] GTFS parser: download ARTM GTFS, parse stops.txt + routes.txt, seed transit stops
• [ ] MEQ parser: download Quebec school directory CSV, seed schools
• [ ] Admin triggers: "Re-sync transit" / "Re-sync schools" buttons
• [x] API: GET /listings/:id/nearby — nearest POIs by category
• [x] Frontend: "Nearby" section on listing detail page (list with icons + distances)
• [x] Bilingual translations (EN/FR)
• [x] Bus stop deduplication — group by route, show nearest stop per unique route

Data Sources

Transit

Source	Coverage	Format	License	Notes
ARTM GTFS	All Greater Montreal transit	GTFS	Open Data	Consolidated feed: STM, RTL, STL, exo, REM. ~15K stops total.
STM GTFS	Bus + Metro	GTFS (stops.txt)	Open Data Montreal	~9,000 bus stops + 68 metro stations. Static feed updated weekly.
REM	REM stations	Manual / ARTM GTFS	ARTM Open Data	~10 stations (Phase 1). Included in ARTM's consolidated GTFS.

Chosen: ARTM GTFS — one download covers STM + RTL + STL + exo + REM.

GTFS stops.txt gives us: stop_id, stop_name, stop_lat, stop_lon, location_type, parent_station

• location_type=1 = station (metro/REM)
• location_type=0 = individual stop (bus)

GTFS routes.txt + stop_times.txt → which routes serve each stop (needed for bus dedup).

Schools

Source	Coverage	Format	License	Notes
Quebec Open Data (MEQ)	All QC schools	CSV	Open Data Quebec	Ministry of Education school directory. Includes type (primary/secondary/CEGEP), language, address, lat/lng.

Chosen: Quebec MEQ — authoritative, free, official, includes school type and language of instruction.

Design

Approach: Pre-seed + Periodic Re-sync (Option A)

Seed all transit stops and schools into a points_of_interest table. On listing detail, query nearest N POIs by haversine distance.

Why not live API calls? Transit stops and schools change infrequently (seasonal schedule changes, annual school updates). Pre-seeding is fast, free, and has zero runtime dependencies.

Data Freshness Strategy

Source	Update Cadence	Trigger
ARTM GTFS	~3-4x/year (schedule changes)	Monthly automated cron or admin button
MEQ Schools	Annual (September)	Admin button, run once after school year starts

Re-sync process:

1. Download fresh data file (GTFS zip / MEQ CSV)
2. Parse and normalize into POI records
3. Upsert by source_id (GTFS stop_id / MEQ school code) — stable across updates
4. Mark POIs not seen in latest import as inactive (soft delete, not hard delete)
5. Log sync result: added/updated/deactivated counts

source_id examples:

• Transit: GTFS stop_id (e.g., STM:10001, REM:GARECENTRALE)
• Schools: MEQ establishment code (e.g., MEQ:763201)

Schema

CREATE TABLE points_of_interest (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  source_id text UNIQUE NOT NULL,     -- stable ID from data source for upsert
  source text NOT NULL,               -- 'artm_gtfs', 'meq'
  category text NOT NULL,             -- 'metro', 'bus', 'rem', 'train', 'school'
  subcategory text,                   -- 'green_line', 'orange_line', 'primary', 'secondary', 'cegep'
  name text NOT NULL,
  name_fr text,
  latitude numeric(10,7) NOT NULL,
  longitude numeric(10,7) NOT NULL,
  metadata jsonb,                     -- route numbers, school board, language, wheelchair, etc.
  is_active boolean NOT NULL DEFAULT true,
  synced_at timestamptz NOT NULL DEFAULT now(),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_poi_category ON points_of_interest (category) WHERE is_active;
CREATE INDEX idx_poi_coords ON points_of_interest (latitude, longitude) WHERE is_active;

API

GET /listings/:id/nearby?radius=2

Query nearest active POIs within radius (default 2km) grouped by category.

Response:

{
  "transit": {
    "metro": [
      { "name": "Papineau", "subcategory": "green_line", "distanceM": 350, "latitude": "...", "longitude": "..." }
    ],
    "rem": [],
    "bus": [
      { "name": "24 Sherbrooke", "distanceM": 50 },
      { "name": "125 Ontario", "distanceM": 120 }
    ]
  },
  "schools": [
    { "name": "École Saint-Louis-de-Gonzague", "subcategory": "primary", "distanceM": 400 },
    { "name": "Collège de Maisonneuve", "subcategory": "cegep", "distanceM": 1100 }
  ]
}

Limits per category:

• Metro/REM: nearest 3 within 2km
• Bus: nearest 5 unique routes within 500m
• Schools: nearest 3 within 2km

Bus Stop Deduplication

GTFS has ~9,000 bus stops for STM alone. Showing "nearest 3 bus stops" isn't useful — they might all be on the same block.

Strategy: Store route info in metadata.routes array. Query groups by route — for each unique route, keep only the nearest stop. Show top 5 routes.

Frontend Display

New section on listing detail page, in the sidebar below the location card:

Nearby
─────────────────────────────────────
🚇 Papineau (Green Line)        350m
   Beaudry (Green Line)         800m
🚌 24 Sherbrooke                 50m
   125 Ontario                  120m
   45 Papineau                  200m
🏫 École Saint-Louis (Primary)  400m
   Collège de Maisonneuve       1.1km

Icons: Train (metro/REM), Bus (bus), School (schools), GraduationCap (CEGEP/university). Distance: meters if <1km, km with 1 decimal if >=1km.

GTFS Parsing

GTFS is a ZIP containing CSV files. Key files:

• stops.txt — stop_id, stop_name, stop_lat, stop_lon, location_type, parent_station
• routes.txt — route_id, route_short_name, route_long_name, route_type
• trips.txt — trip_id, route_id, service_id
• stop_times.txt — trip_id, stop_id (links stops to routes via trips)

Parsing pipeline:

1. Download ARTM GTFS ZIP
2. Extract stops.txt → filter by Greater Montreal bounding box
3. For location_type=1 (stations): category = metro/rem based on route_type or naming
4. For location_type=0 (stops): category = bus, join with routes via stop_times→trips→routes to get route numbers
5. Store route numbers in metadata.routes

Metro vs REM distinction: GTFS route_type:

• 1 = Subway (metro)
• 2 = Rail (REM, exo commuter trains)
• 3 = Bus

Estimated Effort

Task	Effort
points_of_interest table + migration	Small
GTFS downloader + parser + seed script	Medium — parse ZIP, join stops/routes, filter Montreal
MEQ school data parser + seed	Small — CSV parse + seed
Re-sync logic (upsert by source_id, deactivate stale)	Small
Admin triggers (re-sync buttons)	Small — HTTP API on scraper/admin
API: GET /listings/:id/nearby	Small — haversine query with category grouping
Frontend: nearby section on detail page	Small — list with icons + distances
Bus dedup (group by route)	Medium — SQL or app-level grouping
Bilingual (EN/FR)	Small

Discussion Notes

Mar 5, 2026

• Feature proposed. Core idea: show closest metro/REM, bus routes, and schools on listing detail.
• Best free data sources: ARTM GTFS (all Greater Montreal transit) + Quebec MEQ (all schools).
• Prefer pre-seeding POI data over live API calls — transit stops and schools don't move often.
• Bus stop deduplication needed — group by route, show nearest stop per unique route.
• Walking distance (Google Distance Matrix) can be added later as enhancement.
• Data freshness: ARTM GTFS changes ~3-4x/year, MEQ annually. Periodic re-sync via idempotent upsert on source_id. Admin button + optional monthly cron. Stale POIs soft-deleted (is_active=false).

Implementation Notes

Database

• Added points_of_interest table in packages/database/src/schema.ts with source_id unique constraint for idempotent upsert syncing.
• Categories: metro, bus, train, school. Subcategories for metro lines and school types.
• metadata JSONB stores bus route numbers and school board/language info.
• is_active flag for soft-delete on re-sync when a POI disappears from source data.
• Added sync:poi script for re-syncing POI data from source files.

API

• GET /listings/:id/nearby endpoint returns POIs grouped by category, ordered by haversine distance.
• Limits: 3 metro stations (2km), 5 unique bus routes (500m), 3 train stations (2km), 3 schools (2km).
• Bus deduplication: groups stops by route, returns only the nearest stop per unique route.

Frontend

• NearbySection component on listing detail page displays transit and schools with icons and distances.
• Distance formatting: meters when <1km, km with 1 decimal when >=1km.
• Icons: Train (metro), Bus (bus), School/GraduationCap (schools).
• Bilingual support via EN/FR translation keys.