← back to Animals

migrations/006_zip_centroids.sql

22 lines

-- ZIP centroid cache for the Haversine-based "near me" / 25mi visibility predicate.
-- Geocoded once via the free US Census Geocoder OneLine API; reused forever
-- (ZIP centroids don't move). Used by:
--   src/lib/geo.js   — geocodeZip(zip) reads/writes this table
--   src/lib/auth.js  — signup populates app_users.home_lat/home_lng from the zip
--   src/server/community.js — POST /marketplace/new populates listing.latitude/longitude
--                              and SELECT /marketplace uses Haversine + zip-equality.

BEGIN;

CREATE TABLE IF NOT EXISTS zip_centroids (
  zip          TEXT PRIMARY KEY,
  latitude     NUMERIC(10,7) NOT NULL,
  longitude    NUMERIC(10,7) NOT NULL,
  source       TEXT NOT NULL DEFAULT 'us_census_onelineaddress',
  geocoded_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_zip_centroids_latlng ON zip_centroids(latitude, longitude);

COMMIT;