forked from azavea/pfb
-
Notifications
You must be signed in to change notification settings - Fork 0
/
stress_tertiary.sql
173 lines (171 loc) · 9.34 KB
/
stress_tertiary.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
----------------------------------------
-- INPUTS
-- location: neighborhood
-- notes: this includes residential streets that have bike lanes
-- of any type
----------------------------------------
UPDATE neighborhood_ways SET ft_seg_stress = NULL, tf_seg_stress = NULL
WHERE functional_class IN ('tertiary','tertiary_link')
OR (functional_class = 'residential' AND ft_bike_infra IN ('track','buffered_lane','lane'))
OR (functional_class = 'residential' AND tf_bike_infra IN ('track','buffered_lane','lane'))
OR (functional_class = 'residential' AND ft_lanes > 1)
OR (functional_class = 'residential' AND tf_lanes > 1)
OR (functional_class = 'residential' AND speed_limit > 30);
-- ft direction
UPDATE neighborhood_ways
SET ft_seg_stress =
CASE
WHEN ft_bike_infra = 'track' THEN 1
WHEN ft_bike_infra = 'buffered_lane'
THEN CASE
WHEN speed_limit = 35
THEN CASE
WHEN ft_lanes > 1 THEN 3
ELSE 2 -- assume 1 lane
END
WHEN speed_limit > 35
THEN CASE
WHEN ft_lanes > 1 THEN 3
ELSE 2 -- assume 1 lane
END
ELSE CASE -- assume speed 30
WHEN ft_lanes > 1 THEN 2
ELSE 1 -- assume 1 lane
END
END
WHEN ft_bike_infra = 'lane'
THEN CASE
WHEN speed_limit > 30 THEN 3
WHEN speed_limit <= 20
THEN CASE
WHEN ft_lanes > 2 THEN 3
WHEN ft_lanes = 2
THEN CASE
WHEN ft_park = 0 THEN 2
ELSE 2 -- assume parking
END
ELSE CASE -- assume 1 lane
WHEN ft_park = 0 THEN 1
ELSE 2 -- assume parking
END
END
WHEN speed_limit = 25
THEN CASE
WHEN ft_lanes > 2 THEN 3
WHEN ft_lanes = 2
THEN CASE
WHEN ft_park = 0 THEN 2
ELSE 3 -- assume parking
END
ELSE CASE -- assume 1 lane
WHEN ft_park = 0 THEN 1
ELSE 2 -- assume parking
END
END
ELSE CASE -- assume 30 mph speed limit
WHEN ft_lanes > 1 THEN 3
ELSE CASE -- assume 1 lane
WHEN ft_park = 0 THEN 1
ELSE 2 -- assume parking
END
END
END
ELSE CASE
WHEN speed_limit = 30
THEN CASE
WHEN ft_lanes = 1 THEN 2
ELSE 3 -- assumee more than 1 lane
END
WHEN speed_limit <= 25
THEN CASE
WHEN ft_lanes = 1 THEN 1
ELSE 3 -- assumee more than 1 lane
END
ELSE 3
END
END
WHERE functional_class IN ('tertiary','tertiary_link')
OR (functional_class = 'residential' AND ft_bike_infra IN ('track','buffered_lane','lane'))
OR (functional_class = 'residential' AND tf_bike_infra IN ('track','buffered_lane','lane'))
OR (functional_class = 'residential' AND ft_lanes > 1)
OR (functional_class = 'residential' AND tf_lanes > 1)
OR (functional_class = 'residential' AND speed_limit > 30);
-- tf direction
UPDATE neighborhood_ways
SET tf_seg_stress =
CASE
WHEN tf_bike_infra = 'track' THEN 1
WHEN tf_bike_infra = 'buffered_lane'
THEN CASE
WHEN speed_limit = 35
THEN CASE
WHEN tf_lanes > 1 THEN 3
ELSE 2 -- assume 1 lane
END
WHEN speed_limit > 35
THEN CASE
WHEN tf_lanes > 1 THEN 3
ELSE 2 -- assume 1 lane
END
ELSE CASE -- assume speed 30
WHEN tf_lanes > 1 THEN 2
ELSE 1 -- assume 1 lane
END
END
WHEN tf_bike_infra = 'lane'
THEN CASE
WHEN speed_limit > 30 THEN 3
WHEN speed_limit <= 20
THEN CASE
WHEN tf_lanes > 2 THEN 3
WHEN tf_lanes = 2
THEN CASE
WHEN tf_park = 0 THEN 2
ELSE 2 -- assume parking
END
ELSE CASE -- assume 1 lane
WHEN tf_park = 0 THEN 1
ELSE 2 -- assume parking
END
END
WHEN speed_limit = 25
THEN CASE
WHEN tf_lanes > 2 THEN 3
WHEN tf_lanes = 2
THEN CASE
WHEN tf_park = 0 THEN 2
ELSE 3 -- assume parking
END
ELSE CASE -- assume 1 lane
WHEN tf_park = 0 THEN 1
ELSE 2 -- assume parking
END
END
ELSE CASE -- assume 30 mph speed limit
WHEN tf_lanes > 1 THEN 3
ELSE CASE -- assume 1 lane
WHEN tf_park = 0 THEN 1
ELSE 2 -- assume parking
END
END
END
ELSE CASE
WHEN speed_limit = 30
THEN CASE
WHEN tf_lanes = 1 THEN 2
ELSE 3 -- assumee more than 1 lane
END
WHEN speed_limit <= 25
THEN CASE
WHEN tf_lanes = 1 THEN 1
ELSE 3 -- assumee more than 1 lane
END
ELSE 3
END
END
WHERE functional_class IN ('tertiary','tertiary_link')
OR (functional_class = 'residential' AND ft_bike_infra IN ('track','buffered_lane','lane'))
OR (functional_class = 'residential' AND tf_bike_infra IN ('track','buffered_lane','lane'))
OR (functional_class = 'residential' AND ft_lanes > 1)
OR (functional_class = 'residential' AND tf_lanes > 1)
OR (functional_class = 'residential' AND speed_limit > 30);