← back to Nationalrealestate

db/migrations/004_parcels.sql

31 lines

-- Generic parcel table for counties WITHOUT a pre-built SQLite (source_kind='pg').
-- LA stays in its CRCP SQLite (source_kind='sqlite'); NYC/others land here.
CREATE TABLE parcel (
  county_fips TEXT NOT NULL,
  source_id TEXT NOT NULL,          -- BBL (NYC), PIN, APN...
  address TEXT,
  norm_address TEXT,                -- upper/trimmed for prefix search
  city TEXT,
  zip TEXT,
  lat DOUBLE PRECISION,
  lng DOUBLE PRECISION,
  year_built INT,
  sqft NUMERIC,
  beds NUMERIC,
  baths NUMERIC,
  units NUMERIC,
  use_desc TEXT,
  land_value NUMERIC,
  improvement_value NUMERIC,
  total_value NUMERIC,
  tax_year TEXT,
  owner_name TEXT,
  zoning TEXT,
  last_sale_date DATE,
  last_sale_price NUMERIC,
  extra JSONB,
  PRIMARY KEY (county_fips, source_id)
);
CREATE INDEX idx_parcel_addr ON parcel (county_fips, norm_address text_pattern_ops);
CREATE INDEX idx_parcel_use ON parcel (county_fips, use_desc);