-
Notifications
You must be signed in to change notification settings - Fork 20
/
Copy pathgenerated_feature_workorders_nearby_blocks.sql
145 lines (141 loc) · 4.83 KB
/
generated_feature_workorders_nearby_blocks.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
drop table if exists features.workorders_near_blocks;
select
SL.street_id
, 2004 as year_curr
, (select count (FOO.workid) from
(select
ww.workid
from streets.street_lines SL1, public.water_workorders as ww
where ST_DWithin(SL1.geom,ww.geom,300)
and ww.leak_num_y <= 2004
and ww.leak_num_y >= (2004)
and SL1.street_id = SL.street_id--so it's not comparing to itself
) as FOO
) as WO_nearby_last_1
, (select count (FOO.workid) from
(select
ww.workid
from streets.street_lines SL1, public.water_workorders as ww
where ST_DWithin(SL1.geom,ww.geom,300)
and ww.leak_num_y <= 2004
and ww.leak_num_y >= (2004 - 1)
and SL1.street_id = SL.street_id--so it's not comparing to itself
) as FOO
) as WO_nearby_last_2
, (select count (FOO.workid) from
(select
ww.workid
from streets.street_lines SL1, public.water_workorders as ww
where ST_DWithin(SL1.geom,ww.geom,300)
and ww.leak_num_y <= 2004
and ww.leak_num_y >= (2004 - 2)
and SL1.street_id = SL.street_id--so it's not comparing to itself
) as FOO
) as WO_nearby_last_3
, (select count (FOO.workid) from
(select
ww.workid
from streets.street_lines SL1, public.water_workorders as ww
where ST_DWithin(SL1.geom,ww.geom,300)
and ww.leak_num_y <= 2004
and ww.leak_num_y >= (2004 - 3)
and SL1.street_id = SL.street_id--so it's not comparing to itself
) as FOO
) as WO_nearby_last_4
, (select count (FOO.workid) from
(select
ww.workid
from streets.street_lines SL1, public.water_workorders as ww
where ST_DWithin(SL1.geom,ww.geom,300)
and ww.leak_num_y <= 2004
and ww.leak_num_y >= (2004 - 4)
and SL1.street_id = SL.street_id--so it's not comparing to itself
) as FOO
) as WO_nearby_last_5
, (select count (FOO.workid) from
(select
ww.workid
from streets.street_lines SL1, public.water_workorders as ww
where ST_DWithin(SL1.geom,ww.geom,300)
and ww.leak_num_y <= 2004
and ww.leak_num_y >= (2004 - 5)
and SL1.street_id = SL.street_id--so it's not comparing to itself
) as FOO
) as WO_nearby_last_6
into features.workorders_near_blocks
from features.mains_to_streets as SL
group by SL.street_id, year_curr;
--Looping over the workorders from 2005 to 2016
do
$$
begin
for YEAR_num in 2005..2016 loop
insert into features.workorders_near_blocks
select
SL.street_id
, YEAR_num as year_curr
, (select count (FOO.workid) from
(select
ww.workid
from streets.street_lines SL1, public.water_workorders as ww
where ST_DWithin(SL1.geom,ww.geom,300)
and ww.leak_num_y <= YEAR_num
and ww.leak_num_y >= (YEAR_num)
and SL1.street_id = SL.street_id--so it's not comparing to itself
) as FOO
) as WO_nearby_last_1
, (select count (FOO.workid) from
(select
ww.workid
from streets.street_lines SL1, public.water_workorders as ww
where ST_DWithin(SL1.geom,ww.geom,300)
and ww.leak_num_y <= YEAR_num
and ww.leak_num_y >= (YEAR_num - 1)
and SL1.street_id = SL.street_id--so it's not comparing to itself
) as FOO
) as WO_nearby_last_2
, (select count (FOO.workid) from
(select
ww.workid
from streets.street_lines SL1, public.water_workorders as ww
where ST_DWithin(SL1.geom,ww.geom,300)
and ww.leak_num_y <= YEAR_num
and ww.leak_num_y >= (YEAR_num - 2)
and SL1.street_id = SL.street_id--so it's not comparing to itself
) as FOO
) as WO_nearby_last_3
, (select count (FOO.workid) from
(select
ww.workid
from streets.street_lines SL1, public.water_workorders as ww
where ST_DWithin(SL1.geom,ww.geom,300)
and ww.leak_num_y <= YEAR_num
and ww.leak_num_y >= (YEAR_num - 3)
and SL1.street_id = SL.street_id--so it's not comparing to itself
) as FOO
) as WO_nearby_last_4
, (select count (FOO.workid) from
(select
ww.workid
from streets.street_lines SL1, public.water_workorders as ww
where ST_DWithin(SL1.geom,ww.geom,300)
and ww.leak_num_y <= YEAR_num
and ww.leak_num_y >= (YEAR_num - 4)
and SL1.street_id = SL.street_id--so it's not comparing to itself
) as FOO
) as WO_nearby_last_5
, (select count (FOO.workid) from
(select
ww.workid
from streets.street_lines SL1, public.water_workorders as ww
where ST_DWithin(SL1.geom,ww.geom,300)
and ww.leak_num_y <= YEAR_num
and ww.leak_num_y >= (YEAR_num - 5)
and SL1.street_id = SL.street_id--so it's not comparing to itself
) as FOO
) as WO_nearby_last_6
from features.mains_to_streets as SL
group by SL.street_id, year_curr;
end loop;
end;
$$