auto-data-snapshot: 2026-08-11T09:10:52 (1 data files) — db/gis_schema.sql

bc01699a57f23001c5b02c03aa1766e65d8a79de · 2026-08-11 09:11:35 -0700 · auto-commit-fleet

Files touched

A db/gis_schema.sql

Diff

commit bc01699a57f23001c5b02c03aa1766e65d8a79de
Author: auto-commit-fleet <steve@designerwallcoverings.com>
Date:   Tue Aug 11 09:11:35 2026 -0700

    auto-data-snapshot: 2026-08-11T09:10:52 (1 data files) — db/gis_schema.sql
---
 db/gis_schema.sql | 29 +++++++++++++++++++++++++++++
 1 file changed, 29 insertions(+)

diff --git a/db/gis_schema.sql b/db/gis_schema.sql
new file mode 100644
index 0000000..7d8c3b1
--- /dev/null
+++ b/db/gis_schema.sql
@@ -0,0 +1,29 @@
+-- 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);

← d9ab84f Promote merged card+lead-score viewer to index.html; preserv · back to La Socrata Ingester · GIS layers (NavigateLA ArcGIS) + CSLB contractor loader cc12805 →