← back to La Socrata Ingester

db/gis_schema.sql

30 lines

-- GIS layers from LA City NavigateLA (ArcGIS). Geometry stored as jsonb (Esri
-- geometry in WGS84). Idempotent.

-- Parcel polygons (Layer 397, ~2.4M) — join to permits/assessor by ain.
CREATE TABLE IF NOT EXISTS la_parcel_geom (
  oid        bigint NOT NULL,
  ain        text,
  geom       jsonb,
  raw        jsonb,
  source_url text,
  fetched_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (oid)
);
CREATE INDEX IF NOT EXISTS idx_la_parcel_geom_ain ON la_parcel_geom (ain);

-- Everything else (zoning, land use, boundaries, hazards) in one generic table.
-- layer = logical layer name; oid = source OBJECTID; name = best-effort label.
CREATE TABLE IF NOT EXISTS la_gis_features (
  layer      text   NOT NULL,
  oid        bigint NOT NULL,
  name       text,
  geom       jsonb,
  raw        jsonb,
  source_url text,
  fetched_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (layer, oid)
);
CREATE INDEX IF NOT EXISTS idx_la_gis_layer ON la_gis_features (layer);
CREATE INDEX IF NOT EXISTS idx_la_gis_name  ON la_gis_features (name);