Vero/dragndrop_hld
4
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
dragndrop_hld/oldqc/test_data_poles_accesspoints.sql
alex 12407b74e4 Initial commit - Stage 1 working version 
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>
2025-12-04 13:43:57 -07:00

376 lines
16 KiB
SQL
Raw Permalink Blame History

-- Test data for eli_test poles and access points
-- This creates poles and access points (handholes) for testing QC features
-- Coordinates match existing segment endpoints from test_data.sql
-- ============================================
-- CREATE TABLES
-- ============================================
-- Create poles table
CREATE TABLE IF NOT EXISTS eli_test.poles (
gid SERIAL PRIMARY KEY,
id INTEGER,
mapprojectid INTEGER,
latitude VARCHAR,
longitude VARCHAR,
custom INTEGER,
color VARCHAR,
shapeid VARCHAR,
stylesize VARCHAR,
opacity VARCHAR,
createdby INTEGER,
createddate INTEGER,
modifiedby INTEGER,
modifieddate INTEGER,
historyid INTEGER,
name VARCHAR,
tags VARCHAR,
group1 VARCHAR,
group2 VARCHAR,
mrstateid INTEGER,
commsmrchoiceid INTEGER,
powermrchoiceid VARCHAR,
poleheight VARCHAR,
attachmentheight VARCHAR,
mrnotes VARCHAR,
owner VARCHAR,
geom GEOMETRY(Point, 6561)
);
-- Create access_points table (handholes)
CREATE TABLE IF NOT EXISTS eli_test.access_points (
gid SERIAL PRIMARY KEY,
id INTEGER,
name VARCHAR,
mapprojectid INTEGER,
latitude VARCHAR,
longitude VARCHAR,
manufacturer VARCHAR,
size VARCHAR,
locked INTEGER,
description VARCHAR,
aka VARCHAR,
createdby INTEGER,
createddate INTEGER,
modifiedby VARCHAR,
modifieddate VARCHAR,
historyid INTEGER,
group1 VARCHAR,
group2 VARCHAR,
typeid INTEGER,
statusid INTEGER,
crmvendorid VARCHAR,
billdate VARCHAR,
geom GEOMETRY(Point, 6561)
);
-- ============================================
-- INSERT POLES (for aerial segments)
-- ============================================
-- Poles for the connected aerial segment chain in Zone_A
INSERT INTO eli_test.poles (id, mapprojectid, name, owner, poleheight, attachmentheight, group1, geom)
VALUES
-- Pole at start of first aerial segment
(101, 1, 'Pole-101', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4194 37.7749)', 4326), 6561)),
-- Pole at junction (end of segment 1, start of segment 2)
(102, 1, 'Pole-102', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4184 37.7759)', 4326), 6561)),
-- Pole at junction (end of segment 2, start of segment 3)
(103, 1, 'Pole-103', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4174 37.7769)', 4326), 6561)),
-- Pole at end of third aerial segment (connects to underground)
(104, 1, 'Pole-104', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4164 37.7779)', 4326), 6561)),
-- Poles for another aerial segment
(105, 1, 'Pole-105', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4200 37.7800)', 4326), 6561)),
(106, 1, 'Pole-106', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4210 37.7810)', 4326), 6561)),
-- Poles for long aerial segment (will be INVALID for single span - too long)
(107, 1, 'Pole-107', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4220 37.7750)', 4326), 6561)),
(108, 1, 'Pole-108', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4280 37.7760)', 4326), 6561)),
-- Poles for branching segments
(109, 1, 'Pole-109', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4140 37.7730)', 4326), 6561)),
(110, 1, 'Pole-110', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4130 37.7740)', 4326), 6561)),
(111, 1, 'Pole-111', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4120 37.7750)', 4326), 6561)),
(112, 1, 'Pole-112', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4120 37.7730)', 4326), 6561)),
-- Poles for long span test segment
(113, 1, 'Pole-113', 'Test Utility', '50', '45', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4300 37.7700)', 4326), 6561)),
(114, 1, 'Pole-114', 'Test Utility', '50', '45', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4100 37.7700)', 4326), 6561)),
-- Additional poles for new test segments
(115, 1, 'Pole-115', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4340 37.7680)', 4326), 6561)),
(116, 1, 'Pole-116', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4330 37.7690)', 4326), 6561)),
-- ONLY ONE POLE for disconnected aerial segment (will be invalid - no pole at start)
(117, 1, 'Pole-117', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4990 37.8010)', 4326), 6561));
-- ============================================
-- INSERT ACCESS POINTS / HANDHOLES (for underground segments)
-- ============================================
INSERT INTO eli_test.access_points (id, name, mapprojectid, description, manufacturer, size, typeid, statusid, group1, geom)
VALUES
-- Access point at junction between aerial and underground (already has pole 104)
-- This tests that underground can connect to EITHER pole OR access point
(201, 'Handhole-201', 1, 'Transition point from aerial to underground', 'CommScope', '24x36', 1, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4164 37.7779)', 4326), 6561)),
-- Access point at junction of underground segments
(202, 'Handhole-202', 1, 'Underground junction', 'CommScope', '24x36', 1, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4154 37.7789)', 4326), 6561)),
-- Access point at end of underground segment 2
(203, 'Handhole-203', 1, 'Underground termination', 'CommScope', '24x36', 1, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4144 37.7799)', 4326), 6561)),
-- Access point for short underground segment start
(204, 'Handhole-204', 1, 'Short segment start', 'Preformed', '18x24', 1, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4250 37.7820)', 4326), 6561)),
(205, 'Handhole-205', 1, 'Short segment end', 'Preformed', '18x24', 1, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4249 37.7821)', 4326), 6561)),
-- Access point at junction between underground and aerial (has pole 116)
(206, 'Handhole-206', 1, 'Transition point underground to aerial', 'CommScope', '24x36', 1, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4330 37.7690)', 4326), 6561)),
-- Access point at end of underground segment
(207, 'Handhole-207', 1, 'Underground endpoint', 'CommScope', '30x48', 1, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4320 37.7700)', 4326), 6561)),
-- Additional access points for new underground segments we'll create
(208, 'Handhole-208', 1, 'Underground network point', 'CommScope', '24x36', 1, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4180 37.7720)', 4326), 6561)),
(209, 'Handhole-209', 1, 'Underground network point', 'CommScope', '24x36', 1, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4170 37.7730)', 4326), 6561)),
(210, 'Handhole-210', 1, 'Underground vault', 'Oldcastle', '30x48', 2, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4160 37.7740)', 4326), 6561)),
(211, 'Handhole-211', 1, 'Underground junction', 'CommScope', '24x36', 1, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4150 37.7750)', 4326), 6561));
-- ============================================
-- INSERT ADDITIONAL UNDERGROUND SEGMENTS FOR TESTING
-- ============================================
-- These segments will test various scenarios for the underground endpoints QC
INSERT INTO eli_test.segment2 (type, length, cost, fdh_id, "Group 1", geom)
VALUES
-- VALID: Underground segment with access points at both ends (208 -> 209)
('Underground', 135.0, 2700.00, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('LINESTRING(-122.4180 37.7720, -122.4170 37.7730)', 4326), 6561)),
-- VALID: Underground segment with access points at both ends (209 -> 210)
('Underground', 132.0, 2640.00, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('LINESTRING(-122.4170 37.7730, -122.4160 37.7740)', 4326), 6561)),
-- VALID: Underground segment with access points at both ends (210 -> 211)
('Underground', 138.0, 2760.00, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('LINESTRING(-122.4160 37.7740, -122.4150 37.7750)', 4326), 6561)),
-- INVALID: Underground segment with NO endpoints (missing both access points)
('Underground', 145.0, 2900.00, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('LINESTRING(-122.4100 37.7650, -122.4090 37.7660)', 4326), 6561)),
-- INVALID: Underground segment with only ONE endpoint (start has access point 211, end missing)
('Underground', 142.0, 2840.00, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('LINESTRING(-122.4150 37.7750, -122.4140 37.7760)', 4326), 6561)),
-- INVALID: Underground segment with only ONE endpoint (end point only, start missing)
('Underground', 148.0, 2960.00, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('LINESTRING(-122.4190 37.7710, -122.4180 37.7720)', 4326), 6561)),
-- VALID: Long underground segment with endpoints
('Underground', 520.0, 10400.00, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('LINESTRING(-122.4250 37.7850, -122.4200 37.7850)', 4326), 6561));
-- Create access points for the last valid underground segment
INSERT INTO eli_test.access_points (id, name, mapprojectid, description, manufacturer, size, typeid, statusid, group1, geom)
VALUES
(212, 'Handhole-212', 1, 'Long underground run start', 'Oldcastle', '36x60', 2, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4250 37.7850)', 4326), 6561)),
(213, 'Handhole-213', 1, 'Long underground run end', 'Oldcastle', '36x60', 2, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4200 37.7850)', 4326), 6561));
-- ============================================
-- INSERT MULTI-VERTEX AERIAL SEGMENTS FOR SINGLE SPAN QC TESTING
-- ============================================
-- These segments will test the single span QC (should have exactly 2 vertices)
-- INVALID: Aerial segment with 3 vertices (multi-span)
INSERT INTO eli_test.segment2 (type, length, cost, fdh_id, "Group 1", geom)
VALUES
('Aerial', 290.0, 2900.00, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('LINESTRING(-122.4380 37.7620, -122.4370 37.7630, -122.4360 37.7640)', 4326), 6561)),
-- INVALID: Aerial segment with 4 vertices (multi-span)
('Aerial', 435.0, 4350.00, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('LINESTRING(-122.4380 37.7650, -122.4370 37.7660, -122.4360 37.7670, -122.4350 37.7680)', 4326), 6561)),
-- INVALID: Aerial segment with 5 vertices (many spans)
('Aerial', 580.0, 5800.00, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('LINESTRING(-122.4450 37.7620, -122.4440 37.7630, -122.4430 37.7640, -122.4420 37.7650, -122.4410 37.7660)', 4326), 6561));
-- Add poles for the multi-vertex segments (at their endpoints only, not mid-points)
INSERT INTO eli_test.poles (id, mapprojectid, name, owner, poleheight, attachmentheight, group1, geom)
VALUES
-- Poles for 3-vertex segment
(118, 1, 'Pole-118', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4380 37.7620)', 4326), 6561)),
(119, 1, 'Pole-119', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4360 37.7640)', 4326), 6561)),
-- Poles for 4-vertex segment
(120, 1, 'Pole-120', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4380 37.7650)', 4326), 6561)),
(121, 1, 'Pole-121', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4350 37.7680)', 4326), 6561)),
-- Poles for 5-vertex segment
(122, 1, 'Pole-122', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4450 37.7620)', 4326), 6561)),
(123, 1, 'Pole-123', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4410 37.7660)', 4326), 6561));
-- ============================================
-- DUPLICATE POLES TEST (for checking only ONE pole at each endpoint)
-- ============================================
-- Create an aerial segment with duplicate poles at one endpoint
INSERT INTO eli_test.segment2 (type, length, cost, fdh_id, "Group 1", geom)
VALUES
-- INVALID: Aerial segment where one endpoint has 2 poles
('Aerial', 150.0, 1500.00, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('LINESTRING(-122.4500 37.7900, -122.4490 37.7910)', 4326), 6561));
-- Add poles for this segment
INSERT INTO eli_test.poles (id, mapprojectid, name, owner, poleheight, attachmentheight, group1, geom)
VALUES
-- Start point: ONE pole (correct)
(124, 1, 'Pole-124', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4500 37.7900)', 4326), 6561)),
-- End point: TWO poles at same location (INVALID - should only be ONE)
(125, 1, 'Pole-125-A', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4490 37.7910)', 4326), 6561)),
(126, 1, 'Pole-125-B', 'Test Utility', '45', '38', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4490 37.7910)', 4326), 6561));
-- Create another test segment with THREE poles at one endpoint for extreme case testing
INSERT INTO eli_test.segment2 (type, length, cost, fdh_id, "Group 1", geom)
VALUES
-- INVALID: Aerial segment where one endpoint has 3 poles
('Aerial', 155.0, 1550.00, 1, 'Zone_A',
ST_Transform(ST_GeomFromText('LINESTRING(-122.4520 37.7920, -122.4510 37.7930)', 4326), 6561));
-- Add poles for this segment
INSERT INTO eli_test.poles (id, mapprojectid, name, owner, poleheight, attachmentheight, group1, geom)
VALUES
-- Start point: ONE pole (correct)
(127, 1, 'Pole-127', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4520 37.7920)', 4326), 6561)),
-- End point: THREE poles at same location (INVALID - should only be ONE)
(128, 1, 'Pole-128-A', 'Test Utility', '40', '35', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4510 37.7930)', 4326), 6561)),
(129, 1, 'Pole-128-B', 'Test Utility', '42', '36', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4510 37.7930)', 4326), 6561)),
(130, 1, 'Pole-128-C', 'Test Utility', '38', '33', 'Zone_A',
ST_Transform(ST_GeomFromText('POINT(-122.4510 37.7930)', 4326), 6561));
-- ============================================
-- VERIFICATION QUERIES
-- ============================================
-- Verify poles were inserted
SELECT COUNT(*) as total_poles FROM eli_test.poles;
SELECT id, name, ST_AsText(ST_Transform(geom, 4326)) as location FROM eli_test.poles ORDER BY id;
-- Verify access points were inserted
SELECT COUNT(*) as total_access_points FROM eli_test.access_points;
SELECT id, name, ST_AsText(ST_Transform(geom, 4326)) as location FROM eli_test.access_points ORDER BY id;
-- Verify all segments
SELECT COUNT(*) as total_segments FROM eli_test.segment2;
SELECT type, COUNT(*) as segment_count FROM eli_test.segment2 GROUP BY type ORDER BY type;
-- Check for duplicate poles at same location
SELECT ST_AsText(ST_Transform(geom, 4326)) as location, COUNT(*) as pole_count
FROM eli_test.poles
GROUP BY geom
HAVING COUNT(*) > 1
ORDER BY pole_count DESC;
-- ============================================
-- TEST SCENARIO SUMMARY
-- ============================================
/*
SINGLE SPAN QC TEST SCENARIOS:
- Valid (2 vertices): Most of the original aerial segments from test_data.sql
- Invalid (3 vertices): 1 segment added
- Invalid (4 vertices): 1 segment added
- Invalid (5 vertices): 1 segment added
AERIAL ENDPOINT POLE COUNT QC TEST SCENARIOS:
- Valid (exactly 1 pole at each endpoint): Most aerial segments
- Invalid (0 poles at start endpoint): 1 segment (disconnected segment)
- Invalid (2 poles at one endpoint): 1 segment
- Invalid (3 poles at one endpoint): 1 segment
UNDERGROUND ENDPOINTS QC TEST SCENARIOS:
- Valid (both endpoints present):
* Original 2 underground segments from test_data.sql
* 4 new underground segments with proper access points
- Invalid (no endpoints): 1 segment
- Invalid (only start endpoint): 1 segment
- Invalid (only end endpoint): 1 segment
TOTAL TEST DATA:
- Poles: 30 poles total (including 5 duplicates at 2 locations)
- Access Points: 13 handholes at underground segment endpoints
- Underground Segments: ~10 total (2 original + 7 new)
- Aerial Segments: ~22 total (original + 3 multi-vertex + 2 duplicate pole tests)
All test data uses mapid=1 and Group 1='Zone_A'
*/
Reference in New Issue View Git Blame Copy Permalink