CHRyBf3x

1-- SQL for creating corner points from walkway table in PostGIS
2-- Melelani Sax-Barnett, Katie Urey, Scott Parker May-August 2012
3
4--import walkway shapefile as LINESTRING -- NOT MULTILINESTRING, or geom won't work
5--& vaccum/analyze for better speed
6select Populate_Geometry_Columns();
7
8drop table if exists public.corners;
9
10-- 1) create all fields
11create table public.corners (
12	id serial primary key,
13	intersection_id bigint,
14	fm_bearing int,
15	to_bear_a int,
16	to_bear_b int,
17	c_direct text,
18	to_dir_a text,
19	to_dir_b text,
20	st_lf_id bigint,
21	st_rt_id bigint,
22	--fm_join_id bigint,
23	st_lf_nm text,
24	st_rt_nm text,
25	geom geometry
26);
27
28-- 2) populate first round of fields from walkways table
29insert into public.corners (intersection_id, fm_bearing, to_bear_a, 
30	st_lf_id, st_rt_id, geom) --removed fm_join_id
31select distinct
32	ww.to_end_id as intersection_id,
33	ww.fm_bearing,
34	ww.to_bearing as to_bear_a,
35	ww.to_join_id as st_lf_id,
36	ww.street_seg as st_rt_id,
37	--ww.fm_join_id,
38	ST_StartPoint(ww.geom)
39	from public.walkways ww where ww.walk_type = 'crosswalk'
40;
41
42-- 3) get names
43update public.corners set st_rt_nm = s.full_name
44	from public.streets s
45	where st_rt_id = s.localid;
46
47update public.corners set st_lf_nm = s.full_name
48	from public.streets s
49	where st_lf_id = s.localid;
50
51-- 4) remove dead-ends
52delete from public.corners where st_lf_id = -1;
53
54-- 5) OLD WAY get direction of other crossing by matching the fm_join_id back to ww file (didn't work)
55/*update public.corners set to_bear_b = ww.fm_bearing
56	from public.walkways ww where public.corners.fm_join_id = ww.street_seg
57	;*/
58
59--new way, join by the corner w/the same bearing & same intersection id, grab other bearing
60update public.corners set to_bear_b = ww.fm_bearing
61	from public.walkways ww 
62	where (ww.walk_type = 'crosswalk') 
63		and (public.corners.intersection_id = ww.to_end_id) 
64		and (public.corners.fm_bearing = ww.to_bearing);
65
66
67-- 6) change bearing to direction
68update public.corners set c_direct = 'N' 
69	where (fm_bearing >= 0) AND (fm_bearing <= 10);
70update public.corners set c_direct = 'NE' 
71	where (fm_bearing > 10) AND (fm_bearing < 80);
72update public.corners set c_direct = 'E' 
73	where (fm_bearing >= 80) AND (fm_bearing <= 100);
74update public.corners set c_direct = 'SE' 
75	where (fm_bearing > 100) AND (fm_bearing < 170);
76update public.corners set c_direct = 'S' 
77	where (fm_bearing >= 170) AND (fm_bearing <= 190);
78update public.corners set c_direct = 'SW' 
79	where (fm_bearing > 190) AND (fm_bearing < 260);
80update public.corners set c_direct = 'W' 
81	where (fm_bearing >= 260) AND (fm_bearing <= 280);
82update public.corners set c_direct = 'NW' 
83	where (fm_bearing > 280) AND (fm_bearing < 350);
84update public.corners set c_direct = 'N' 
85	where (fm_bearing >= 350) AND (fm_bearing <= 360);
86
87update public.corners set to_dir_a = 'N' 
88	where (to_bear_a >= 0) AND (to_bear_a <= 10);
89update public.corners set to_dir_a = 'NE' 
90	where (to_bear_a > 10) AND (to_bear_a < 80);
91update public.corners set to_dir_a = 'E' 
92	where (to_bear_a >= 80) AND (to_bear_a <= 100);
93update public.corners set to_dir_a = 'SE' 
94	where (to_bear_a > 100) AND (to_bear_a < 170);
95update public.corners set to_dir_a = 'S' 
96	where (to_bear_a >= 170) AND (to_bear_a <= 190);
97update public.corners set to_dir_a = 'SW' 
98	where (to_bear_a > 190) AND (to_bear_a < 260);
99update public.corners set to_dir_a = 'W' 
100	where (to_bear_a >= 260) AND (to_bear_a <= 280);
101update public.corners set to_dir_a = 'NW' 
102	where (to_bear_a > 280) AND (to_bear_a < 350);
103update public.corners set to_dir_a = 'N' 
104	where (to_bear_a >= 350) AND (to_bear_a <= 360);
105
106update public.corners set to_dir_b = 'N' 
107	where (to_bear_b >= 0) AND (to_bear_b <= 10);
108update public.corners set to_dir_b = 'NE' 
109	where (to_bear_b > 10) AND (to_bear_b < 80);
110update public.corners set to_dir_b = 'E' 
111	where (to_bear_b >= 80) AND (to_bear_b <= 100);
112update public.corners set to_dir_b = 'SE' 
113	where (to_bear_b > 100) AND (to_bear_b < 170);
114update public.corners set to_dir_b = 'S' 
115	where (to_bear_b >= 170) AND (to_bear_b <= 190);
116update public.corners set to_dir_b = 'SW' 
117	where (to_bear_b > 190) AND (to_bear_b < 260);
118update public.corners set to_dir_b = 'W' 
119	where (to_bear_b >= 260) AND (to_bear_b <= 280);
120update public.corners set to_dir_b = 'NW' 
121	where (to_bear_b > 280) AND (to_bear_b < 350);
122update public.corners set to_dir_b = 'N' 
123	where (to_bear_b >= 350) AND (to_bear_b <= 360);
124
125-- 7) finish & display
126select Populate_Geometry_Columns();
127select * from public.corners;
128--select * from public.corners where to_dir_b is null;
129
130--after export to shapefile, manually delete any extra corners you don't want
131--manually fix missing to_dir_b values (happens when walkway dataset is missing crosswalk segments)