Feature: GeoSketch & Isochrone Search - Spatial SQL filters for polygons, school zones, and commute time

Summary
Enable map-driven property discovery powered by spatial SQL. Users can draw a polygon on the map, filter homes inside school attendance zones, and constrain results to areas reachable within X minutes of a target address by drive/walk/bike. Backed by DuckDB Spatial (phase 1) with an optional PostGIS path for production scale. Results surface both a table and instant charts (price, psf, DOM) plus an explainable “why included” badge.

Why

• Current search is keyword/top-K; many buyers think in shapes and travel time.
• Spatial SQL (ST_* functions) delivers fast, verifiable filters over 30k+ points.
• School-zone and commute gates convert casual browsing into actionable shortlists.

---

Scope

User stories

• As a buyer, I can draw a shape and only see listings within that shape.
• As a parent, I can search within a school’s attendance boundary.
• As a commuter, I can see homes ≤ 20-minute bike ride from my office.
• As a power user, I can combine all three (polygon ∩ school zone ∩ isochrone).

Acceptance criteria

• Frontend: /map gets a “Draw” mode (freehand polygon), School Zone selector, and Commute filter (mode + minutes).
• Backend: POST /api/geo/search accepts geometry + filters; returns paginated listings + summary stats.
• Spatial engine: DuckDB w/ Spatial by default; PostGIS toggle via env.
• Data model: properties persisted with POINT( lon lat ) geometry and SRID 4326.
• Isochrone support via backend helper (Valhalla/OSRM/GraphHopper or cached polygons).
• Security: read-only SQL, AST validation, param binding; max polygon vertices; timeouts.
• Metrics: query latency, rows returned, cache hit rate, isochrone generation time.
• Tests: unit (SQL builders), integration (API), E2E (draw→filter→results).

---

Data & schema (read-only)

Tables / Views

• vw_properties_geom — (zpid, price, beds, baths, sqft, neighborhood, zip, lat, lon, geom GEOMETRY(Point, 4326), list_date, status, …)
• vw_school_zones — (zone_id, school_name, district, geom GEOMETRY(Polygon/MultiPolygon, 4326))
• mv_psf_by_area_month — (area_hash, month, median_psf, count) for quick charting of arbitrary areas (computed by hashing polygon WKB).

DuckDB Spatial: store polygons/points in Parquet; register tables; create views.
PostGIS: identical columns; add GIST(geom) index.

---

API

POST /api/geo/search

Body

{
  "polygonWkt": "POLYGON((...))",
  "schoolZoneId": "chapel_hill_high_2025",
  "commute": { "mode": "bike", "minutes": 20, "to": "100 Smith Level Rd, Chapel Hill, NC" },
  "filters": { "minBeds": 3, "maxPrice": 750000 },
  "page": 1,
  "pageSize": 50,
  "withCharts": true
}

Response

{
  "results": [{ "zpid": 123, "price": 689000, "beds": 3, "sqft": 1850, "neighborhood": "Southern Village", "why": ["IN_DRAWN_AREA","IN_SCHOOL_ZONE","WITHIN_ISOCHRONE"] }],
  "page": 1,
  "total": 134,
  "charts": [{ "type": "line", "x": "month", "y": ["median_psf"], "series": ["area"] }]
}

---

Backend design

Core SQL (DuckDB Spatial / PostGIS)

• Polygon filter

SELECT *
FROM vw_properties_geom
WHERE ST_Within(geom, ST_GeomFromText($polygon_wkt, 4326));

• School zone join

SELECT p.*
FROM vw_properties_geom p
JOIN vw_school_zones s
  ON ST_Intersects(p.geom, s.geom)
WHERE s.zone_id = $zone_id;

• Isochrone intersection
  ($iso_poly is a cached GEOMETRY from the routing service)

SELECT p.*
FROM vw_properties_geom p
WHERE ST_Within(p.geom, ST_GeomFromText($iso_poly_wkt, 4326));

• All filters combined (param-driven builder)

WITH base AS (
  SELECT *
  FROM vw_properties_geom p
  WHERE ($polygon_wkt IS NULL OR ST_Within(p.geom, ST_GeomFromText($polygon_wkt, 4326)))
    AND ($zone_id IS NULL OR EXISTS (
          SELECT 1 FROM vw_school_zones s
          WHERE s.zone_id = $zone_id AND ST_Intersects(p.geom, s.geom)))
    AND ($iso_poly_wkt IS NULL OR ST_Within(p.geom, ST_GeomFromText($iso_poly_wkt, 4326)))
    AND (p.beds >= $minBeds)
    AND (p.price <= $maxPrice)
)
SELECT * FROM base
ORDER BY price ASC
LIMIT $limit OFFSET $offset;

Isochrone generation

• Service wrapper calls a routing engine to produce a polygon (drive/walk/bike, minutes).
• Hash {mode, minutes, to} → Redis cache of WKT (TTL e.g., 24h).
• Store last N polygons in geo_iso_cache(area_hash, geom) for repeat queries.

Caching & performance

• Result cache: hash of normalized filters + polygon WKB → Redis.
• PostGIS path: add GIST(geom) on properties, GIST(geom) on zones; optionally pre-clip zones to Chapel Hill bbox to shrink search.
• DuckDB: quack faster with Parquet partitioning by zip or neighborhood.

Safety & validation

• AST validator forbids DDL/DML; only SELECT over whitelisted views.
• Polygon constraints: max 1e4 vertices; simplify overly dense shapes server-side.
• Timeouts + row caps; paginate.

---

Frontend (/map) updates

• New toolbar: Draw, School Zone, Commute.
• Draw: freehand → simplify → WKT; preview area and stats.
• School Zone: searchable dropdown; highlights boundary on map.
• Commute: mode selector (drive/walk/bike), minutes slider, destination input; shows shaded isochrone.
• Results panel: list + “why” chips (e.g., IN_SCHOOL_ZONE, WITHIN_ISOCHRONE).
• “View on Map” stays linked; add Export CSV of results and Copy as WKT/GeoJSON.

---

Prometheus metrics

• geo_query_duration_seconds (histogram, by engine=duckdb|postgis)
• geo_results_rows_total
• geo_isochrone_build_seconds and cache hit ratio
• geo_polygon_vertices (gauge)

---

Example templates (saved queries)

1. Median PSF inside drawn area, by month

SELECT month, median_psf, count
FROM mv_psf_by_area_month
WHERE area_hash = $area_hash
  AND month >= $from
ORDER BY month;

2. Top 10 “value” homes in school zone (price vs area median)

WITH z AS (
  SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS area_median
  FROM vw_properties_geom p
  JOIN vw_school_zones s ON ST_Intersects(p.geom, s.geom)
  WHERE s.zone_id = $zone_id
)
SELECT p.zpid, p.price, (z.area_median - p.price) AS below_median
FROM vw_properties_geom p CROSS JOIN z
JOIN vw_school_zones s ON ST_Intersects(p.geom, s.geom)
WHERE s.zone_id = $zone_id
ORDER BY below_median DESC
LIMIT 10;

---

Milestones

M1 — Spatial foundations

• Add DuckDB Spatial + geometry columns; ETL to populate geom.
• Ingest vw_school_zones (GeoJSON); unit tests for SRID and validity.
• /api/geo/search (polygon + basic filters) + caching.

M2 — Isochrone & UI

• Isochrone wrapper + cache; frontend controls; combined filters.
• Summary charts; “why chips”; CSV export.

M3 — PostGIS option & polish

• PostGIS adapter with GIST indexes; perf benchmarks.
• Rate limiting, timeouts, detailed metrics; docs & OpenAPI.

---

Risks & mitigations

• Routing API latency → cache by {mode, minutes, to}; debounce requests.
• Invalid/self-intersecting polygons → server-side ST_MakeValid + ST_Simplify.
• CRS mismatches → enforce SRID 4326 end-to-end; reproject if needed.

---

Open questions

• Which routing backend do we standardize on for isochrones? (Valhalla vs OSRM vs hosted)
• Do we want multi-stop commute (home→school→work) in v1, or single destination only?
• Should school zones be versioned per year (e.g., 2024–2025) and selectable?

---