alex
12407b74e4
Saving current working state before proceeding to Stage 2. Includes: - Backend: Python-based QC validator with shapefile processing - Frontend: Drag-and-drop file upload interface - Sample files for testing - Documentation and revision history 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude <noreply@anthropic.com>
97 lines
5.1 KiB
SQL
97 lines
5.1 KiB
SQL
-- Recreate sites table with proper column types
|
|
-- Drop the old table with incorrect column types
|
|
DROP TABLE IF EXISTS eli_test.sites CASCADE;
|
|
|
|
-- Create sites table with proper structure
|
|
CREATE TABLE eli_test.sites (
|
|
id_0 SERIAL PRIMARY KEY,
|
|
gid INTEGER,
|
|
id INTEGER,
|
|
"MapProjectID" INTEGER,
|
|
"Latitude" DOUBLE PRECISION,
|
|
"Longitude" DOUBLE PRECISION,
|
|
"Exclude" INTEGER,
|
|
"Custom" INTEGER,
|
|
"Color" VARCHAR(50),
|
|
"Opacity" VARCHAR(50),
|
|
"ShapeID" VARCHAR(50),
|
|
"StyleSize" VARCHAR(50),
|
|
"CreatedBy" INTEGER,
|
|
"CreatedDate" BIGINT,
|
|
"ModifiedBy" INTEGER,
|
|
"ModifiedDate" BIGINT,
|
|
"HistoryID" INTEGER,
|
|
"Name" VARCHAR(255),
|
|
"StatusID" INTEGER,
|
|
"Group 1" VARCHAR(255),
|
|
"Group 2" VARCHAR(255),
|
|
"IconTypeID" INTEGER,
|
|
"SchoolID" VARCHAR(100),
|
|
"SiteDemarc" VARCHAR(255),
|
|
"Address1" VARCHAR(255),
|
|
"Address2" VARCHAR(255),
|
|
"City" VARCHAR(100),
|
|
"State" VARCHAR(50),
|
|
"Zip" VARCHAR(20),
|
|
geometry GEOMETRY(Point, 4326)
|
|
);
|
|
|
|
-- Create spatial index on geometry
|
|
CREATE INDEX sidx_sites_geometry ON eli_test.sites USING GIST(geometry);
|
|
|
|
-- Insert test sites with proper data
|
|
INSERT INTO eli_test.sites (id, "MapProjectID", "Name", "Address1", "City", "State", "Zip", "Group 1", geometry)
|
|
VALUES
|
|
-- Sites in Zone_A (correctly within the zone)
|
|
(1001, 1, 'Home-1001', '123 Market St', 'San Francisco', 'CA', '94102', 'Zone_A', ST_GeomFromText('POINT(-122.4190 37.7755)', 4326)),
|
|
(1002, 1, 'Home-1002', '456 Mission St', 'San Francisco', 'CA', '94103', 'Zone_A', ST_GeomFromText('POINT(-122.4175 37.7765)', 4326)),
|
|
(1003, 1, 'Home-1003', '789 Howard St', 'San Francisco', 'CA', '94103', 'Zone_A', ST_GeomFromText('POINT(-122.4160 37.7775)', 4326)),
|
|
(1004, 1, 'Home-1004', '321 Folsom St', 'San Francisco', 'CA', '94107', 'Zone_A', ST_GeomFromText('POINT(-122.4150 37.7785)', 4326)),
|
|
(1005, 1, 'Home-1005', '555 Bryant St', 'San Francisco', 'CA', '94107', 'Zone_A', ST_GeomFromText('POINT(-122.4200 37.7710)', 4326)),
|
|
(1006, 1, 'Home-1006', '888 Harrison St', 'San Francisco', 'CA', '94107', 'Zone_A', ST_GeomFromText('POINT(-122.4205 37.7805)', 4326)),
|
|
(1007, 1, 'Home-1007', '999 7th St', 'San Francisco', 'CA', '94103', 'Zone_A', ST_GeomFromText('POINT(-122.4135 37.7735)', 4326)),
|
|
(1008, 1, 'Home-1008', '111 8th St', 'San Francisco', 'CA', '94103', 'Zone_A', ST_GeomFromText('POINT(-122.4125 37.7745)', 4326)),
|
|
(1009, 1, 'Home-1009', '222 9th St', 'San Francisco', 'CA', '94103', 'Zone_A', ST_GeomFromText('POINT(-122.4330 37.7685)', 4326)),
|
|
(1010, 1, 'Home-1010', '333 10th St', 'San Francisco', 'CA', '94103', 'Zone_A', ST_GeomFromText('POINT(-122.4325 37.7695)', 4326)),
|
|
(1011, 1, 'Home-1011', '444 11th St', 'San Francisco', 'CA', '94103', 'Zone_A', ST_GeomFromText('POINT(-122.4105 37.7750)', 4326)),
|
|
(1012, 1, 'Home-1012', '666 Townsend St', 'San Francisco', 'CA', '94107', 'Zone_A', ST_GeomFromText('POINT(-122.4340 37.7720)', 4326)),
|
|
|
|
-- Sites in Zone_B (correctly within the zone)
|
|
(2001, 1, 'Home-2001', '100 Oak St', 'San Francisco', 'CA', '94102', 'Zone_B', ST_GeomFromText('POINT(-122.4090 37.7855)', 4326)),
|
|
(2002, 1, 'Home-2002', '200 Fell St', 'San Francisco', 'CA', '94102', 'Zone_B', ST_GeomFromText('POINT(-122.4078 37.7865)', 4326)),
|
|
(2003, 1, 'Home-2003', '300 Hayes St', 'San Francisco', 'CA', '94102', 'Zone_B', ST_GeomFromText('POINT(-122.4070 37.7875)', 4326)),
|
|
|
|
-- Sites in Zone_C (correctly within the zone)
|
|
(3001, 1, 'Home-3001', '400 Grove St', 'San Francisco', 'CA', '94117', 'Zone_C', ST_GeomFromText('POINT(-122.3990 37.7955)', 4326)),
|
|
(3002, 1, 'Home-3002', '500 Fulton St', 'San Francisco', 'CA', '94117', 'Zone_C', ST_GeomFromText('POINT(-122.3980 37.7965)', 4326)),
|
|
|
|
-- INVALID: Site labeled Zone_A but physically located in Zone_B
|
|
(1013, 1, 'Home-1013-INVALID', '777 Invalid Location', 'San Francisco', 'CA', '94102', 'Zone_A', ST_GeomFromText('POINT(-122.4080 37.7860)', 4326)),
|
|
|
|
-- INVALID: Site labeled Zone_B but physically located in Zone_A
|
|
(2004, 1, 'Home-2004-INVALID', '888 Wrong Zone', 'San Francisco', 'CA', '94103', 'Zone_B', ST_GeomFromText('POINT(-122.4200 37.7750)', 4326)),
|
|
|
|
-- INVALID: Site labeled Zone_C but physically outside all zones
|
|
(3003, 1, 'Home-3003-INVALID', '999 Outside All Zones', 'San Francisco', 'CA', '94110', 'Zone_C', ST_GeomFromText('POINT(-122.3800 37.7500)', 4326)),
|
|
|
|
-- INVALID: Site labeled Zone_A but physically outside all zones
|
|
(1014, 1, 'Home-1014-INVALID', '1111 Far Away', 'San Francisco', 'CA', '94110', 'Zone_A', ST_GeomFromText('POINT(-122.3700 37.7400)', 4326)),
|
|
|
|
-- INVALID: Site with NULL Group 1 (unassigned) but inside Zone_A
|
|
(1015, 1, 'Home-1015-UNASSIGNED', '1212 Unassigned St', 'San Francisco', 'CA', '94103', NULL, ST_GeomFromText('POINT(-122.4250 37.7800)', 4326)),
|
|
|
|
-- Site in Zone_D (correctly in Zone_D)
|
|
(4001, 1, 'Home-4001', '1313 Empty Zone', 'San Francisco', 'CA', '94110', 'Zone_D', ST_GeomFromText('POINT(-122.3875 37.7625)', 4326));
|
|
|
|
-- Verify sites were inserted
|
|
SELECT COUNT(*) as total_sites FROM eli_test.sites;
|
|
SELECT id, "Name", "Address1", "Group 1", ST_AsText(geometry) as location FROM eli_test.sites ORDER BY id LIMIT 5;
|
|
|
|
-- Summary
|
|
SELECT
|
|
"Group 1" as zone,
|
|
COUNT(*) as site_count
|
|
FROM eli_test.sites
|
|
GROUP BY "Group 1"
|
|
ORDER BY "Group 1";
|