In [98]:
import psycopg2
import pandas as pd
import sh
from IPython.display import display, HTML, Javascript
# Note: assumes that you have a "parcels"/"nfirs"/"firecares-vm" service listed in your ~/.pg_service.conf, "firecares-vm"
# should point at your dev FireCARES vm's database (which requires the FireDepartment.owned_tracts_geom property for
# correct intersection), "parcels" and "nfirs" connections point to the production database instances
parcels = psycopg2.connect('service=parcels')
fc = psycopg2.connect('service=firecares-vm')
fc_dev = psycopg2.connect('service=firecares-dev')
nfirs = psycopg2.connect('service=nfirs')
def heading(text, tag='h3'):
display(HTML('<{tag}>{text}</{tag}>'.format(tag=tag, text=text)))
# Requires https://github.com/catherinedevlin/ipython-sql
%load_ext sql
The sql extension is already loaded. To reload it, use:
%reload_ext sql
In [ ]:
# Create an intermediary table for backing-up only the department information we care about for local restoration
# into the parcels database
with fc.cursor() as c:
c.execute('SELECT id, owned_tracts_geom INTO firedepartment_owned_geom FROM firestation_firedepartment where owned_tracts_geom IS NOT null;')
fc.commit()
sh.pg_dump('service=firecares-vm', '-O', '-t', 'firedepartment_owned_geom', _out='/tmp/firedepartment_owned_geom.sql')
# Load FDs
sh.psql('service=parcels', _in=file('/tmp/firedepartment_owned_geom.sql', 'r'))
In [ ]:
# Might be overkill, but filtering down to a much smaller table size to scan when performing intersections
# this will definitely take some time to complete
with parcels.cursor() as c:
c.execute("""SELECT ogc_fid, wkb_geometry, parcel_id, state, zip, land_use, story_nbr INTO parcel_stories
FROM parcels p INNER JOIN "LUSE_swg" lu
on p.land_use = lu."Code"
WHERE lu.include_in_floor_dist""")
c.execute("""CREATE INDEX ON parcel_stories USING gist (wkb_geometry);""")
c.execute("""CREATE INDEX on parcel_stories (state);""")
c.execute("""CREATE INDEX on parcel_stories (land_use);""")
parcels.commit()
In [80]:
df = pd.read_sql_query("""SELECT count(1) FROM parcels p INNER JOIN "LUSE_swg" u ON u."Code" = p.land_use AND p.state = 'DC';""", parcels)
print 'Total parcels in DC w/ land_use hit: {}'.format(df['count'][0])
df = pd.read_sql_query("""SELECT count(1) FROM parcels p WHERE p.land_use is null and p.state = 'DC';""", parcels)
print 'Total parcels in DC w/ no land_use: {}'.format(df['count'][0])
Total parcels in DC w/ land_use hit: 133014
Total parcels in DC w/ no land_use: 3538
In [59]:
# Get counts over DC for medium/high structures
df = pd.read_sql_query("""select count(1), p.story_nbr, p.land_use, lu.risk_category, lu."Description", lu.residential
from parcels p
join "LUSE_swg" lu
on lu."Code" = p.land_use
where state = 'DC' and lu.include_in_floor_dist
group by p.story_nbr, p.land_use, lu.risk_category, lu."Description", lu.residential
order by count desc, p.story_nbr, p.land_use""", parcels)
df.style.highlight_null(null_color='red')
Out[59]:
count
story_nbr
land_use
risk_category
Description
residential
0
9256
2
133
Medium
MULTI FAMILY DWELLING
Yes
1
3361
3
133
Medium
MULTI FAMILY DWELLING
Yes
2
1538
3
106
Medium
APARTMENT
Yes
3
844
2
278
Medium
STORE BUILDING
No
4
721
2
106
Medium
APARTMENT
Yes
5
660
1
278
Medium
STORE BUILDING
No
6
572
2
244
Medium
OFFICE BUILDING
No
7
455
nan
112
Medium
CONDOMINIUM
Yes
8
376
2.5
133
Medium
MULTI FAMILY DWELLING
Yes
9
311
3
244
Medium
OFFICE BUILDING
No
10
282
3
278
Medium
STORE BUILDING
No
11
216
4
133
Medium
MULTI FAMILY DWELLING
Yes
12
211
4
106
Medium
APARTMENT
Yes
13
141
4
244
Medium
OFFICE BUILDING
No
14
138
1
244
Medium
OFFICE BUILDING
No
15
134
12
244
Medium
OFFICE BUILDING
No
16
126
3
111
Medium
COOPERATIVE
Yes
17
122
nan
133
Medium
MULTI FAMILY DWELLING
Yes
18
112
8
244
Medium
OFFICE BUILDING
No
19
107
nan
244
Medium
OFFICE BUILDING
No
20
106
3
660
High
EDUCATIONAL SERVICE
No
21
103
5
133
Medium
MULTI FAMILY DWELLING
Yes
22
101
2
111
Medium
COOPERATIVE
Yes
23
79
2
660
High
EDUCATIONAL SERVICE
No
24
76
10
244
Medium
OFFICE BUILDING
No
25
74
8
133
Medium
MULTI FAMILY DWELLING
Yes
26
71
3
607
Medium
EMBASSIES/CHANCERIES
No
27
71
nan
278
Medium
STORE BUILDING
No
28
68
4
278
Medium
STORE BUILDING
No
29
68
5
244
Medium
OFFICE BUILDING
No
30
68
9
244
Medium
OFFICE BUILDING
No
31
60
nan
660
High
EDUCATIONAL SERVICE
No
32
59
11
244
Medium
OFFICE BUILDING
No
33
54
4
607
Medium
EMBASSIES/CHANCERIES
No
34
49
1
133
Medium
MULTI FAMILY DWELLING
Yes
35
45
6
133
Medium
MULTI FAMILY DWELLING
Yes
36
44
7
244
Medium
OFFICE BUILDING
No
37
43
4
660
High
EDUCATIONAL SERVICE
No
38
40
10
133
Medium
MULTI FAMILY DWELLING
Yes
39
40
nan
106
Medium
APARTMENT
Yes
40
39
4
111
Medium
COOPERATIVE
Yes
41
38
6
244
Medium
OFFICE BUILDING
No
42
37
9
133
Medium
MULTI FAMILY DWELLING
Yes
43
35
1
660
High
EDUCATIONAL SERVICE
No
44
30
7
133
Medium
MULTI FAMILY DWELLING
Yes
45
28
nan
111
Medium
COOPERATIVE
Yes
46
27
nan
127
Medium
HOTEL
Yes
47
25
3
164
Medium
TRANSIENT LODGING
Yes
48
25
13
244
Medium
OFFICE BUILDING
No
49
24
1.5
133
Medium
MULTI FAMILY DWELLING
Yes
50
24
2
607
Medium
EMBASSIES/CHANCERIES
No
51
21
8
127
Medium
HOTEL
Yes
52
19
nan
607
Medium
EMBASSIES/CHANCERIES
No
53
17
3.5
133
Medium
MULTI FAMILY DWELLING
Yes
54
16
1
106
Medium
APARTMENT
Yes
55
16
10
127
Medium
HOTEL
Yes
56
15
3
119
Medium
RESIDENCE HALL/DORMITORIES
Yes
57
15
9
127
Medium
HOTEL
Yes
58
15
9
660
High
EDUCATIONAL SERVICE
No
59
14
2
127
Medium
HOTEL
Yes
60
14
5
111
Medium
COOPERATIVE
Yes
61
14
8
111
Medium
COOPERATIVE
Yes
62
12
2
164
Medium
TRANSIENT LODGING
Yes
63
11
5
607
Medium
EMBASSIES/CHANCERIES
No
64
11
11
133
Medium
MULTI FAMILY DWELLING
Yes
65
10
2
119
Medium
RESIDENCE HALL/DORMITORIES
Yes
66
10
6
106
Medium
APARTMENT
Yes
67
10
12
133
Medium
MULTI FAMILY DWELLING
Yes
68
9
4
127
Medium
HOTEL
Yes
69
9
4
164
Medium
TRANSIENT LODGING
Yes
70
9
5
660
High
EDUCATIONAL SERVICE
No
71
9
14
244
Medium
OFFICE BUILDING
No
72
8
5
278
Medium
STORE BUILDING
No
73
8
8
119
Medium
RESIDENCE HALL/DORMITORIES
Yes
74
8
14
133
Medium
MULTI FAMILY DWELLING
Yes
75
8
nan
119
Medium
RESIDENCE HALL/DORMITORIES
Yes
76
7
1
112
Medium
CONDOMINIUM
Yes
77
7
3
127
Medium
HOTEL
Yes
78
7
4
119
Medium
RESIDENCE HALL/DORMITORIES
Yes
79
7
7
111
Medium
COOPERATIVE
Yes
80
7
11
127
Medium
HOTEL
Yes
81
6
6
111
Medium
COOPERATIVE
Yes
82
6
9
111
Medium
COOPERATIVE
Yes
83
6
9
607
Medium
EMBASSIES/CHANCERIES
No
84
5
1
213
Medium
COMMERCIAL CONDOMINIUM
No
85
5
2.5
607
Medium
EMBASSIES/CHANCERIES
No
86
5
5
127
Medium
HOTEL
Yes
87
5
7
127
Medium
HOTEL
Yes
88
5
9
119
Medium
RESIDENCE HALL/DORMITORIES
Yes
89
4
1
111
Medium
COOPERATIVE
Yes
90
4
1
127
Medium
HOTEL
Yes
91
4
1
247
Medium
OFFICE CONDO
No
92
4
2.5
244
Medium
OFFICE BUILDING
No
93
4
2.5
278
Medium
STORE BUILDING
No
94
4
3
118
High
FRAT/SORORITY HOUSE
Yes
95
4
5
106
Medium
APARTMENT
Yes
96
4
5
119
Medium
RESIDENCE HALL/DORMITORIES
Yes
97
4
6
127
Medium
HOTEL
Yes
98
4
6
660
High
EDUCATIONAL SERVICE
No
99
4
10
111
Medium
COOPERATIVE
Yes
100
4
12
127
Medium
HOTEL
Yes
101
4
13
133
Medium
MULTI FAMILY DWELLING
Yes
102
4
nan
213
Medium
COMMERCIAL CONDOMINIUM
No
103
3
1
607
Medium
EMBASSIES/CHANCERIES
No
104
3
1.5
244
Medium
OFFICE BUILDING
No
105
3
2
142
Medium
MOTEL
Yes
106
3
3
142
Medium
MOTEL
Yes
107
3
3.5
607
Medium
EMBASSIES/CHANCERIES
No
108
3
7
660
High
EDUCATIONAL SERVICE
No
109
3
9
106
Medium
APARTMENT
Yes
110
3
10
119
Medium
RESIDENCE HALL/DORMITORIES
Yes
111
3
13
127
Medium
HOTEL
Yes
112
2
1
164
Medium
TRANSIENT LODGING
Yes
113
2
1.5
660
High
EDUCATIONAL SERVICE
No
114
2
4
112
Medium
CONDOMINIUM
Yes
115
2
6
119
Medium
RESIDENCE HALL/DORMITORIES
Yes
116
2
8
607
Medium
EMBASSIES/CHANCERIES
No
117
2
8
660
High
EDUCATIONAL SERVICE
No
118
2
9
164
Medium
TRANSIENT LODGING
Yes
119
2
9
278
Medium
STORE BUILDING
No
120
2
10
660
High
EDUCATIONAL SERVICE
No
121
2
13
111
Medium
COOPERATIVE
Yes
122
2
14
127
Medium
HOTEL
Yes
123
2
15
244
Medium
OFFICE BUILDING
No
124
2
21
106
Medium
APARTMENT
Yes
125
1
1
119
Medium
RESIDENCE HALL/DORMITORIES
Yes
126
1
1
142
Medium
MOTEL
Yes
127
1
1.5
278
Medium
STORE BUILDING
No
128
1
2
247
Medium
OFFICE CONDO
No
129
1
2.5
119
Medium
RESIDENCE HALL/DORMITORIES
Yes
130
1
2.5
660
High
EDUCATIONAL SERVICE
No
131
1
3.5
106
Medium
APARTMENT
Yes
132
1
3.5
278
Medium
STORE BUILDING
No
133
1
4
118
High
FRAT/SORORITY HOUSE
Yes
134
1
4
247
Medium
OFFICE CONDO
No
135
1
4.5
133
Medium
MULTI FAMILY DWELLING
Yes
136
1
4.5
244
Medium
OFFICE BUILDING
No
137
1
5
164
Medium
TRANSIENT LODGING
Yes
138
1
7
106
Medium
APARTMENT
Yes
139
1
7
119
Medium
RESIDENCE HALL/DORMITORIES
Yes
140
1
8
106
Medium
APARTMENT
Yes
141
1
12
111
Medium
COOPERATIVE
Yes
142
1
12
119
Medium
RESIDENCE HALL/DORMITORIES
Yes
143
1
12
278
Medium
STORE BUILDING
No
144
1
12
607
Medium
EMBASSIES/CHANCERIES
No
145
1
12
660
High
EDUCATIONAL SERVICE
No
146
1
14
111
Medium
COOPERATIVE
Yes
147
1
15
127
Medium
HOTEL
Yes
148
1
16
111
Medium
COOPERATIVE
Yes
149
1
16
127
Medium
HOTEL
Yes
150
1
16
133
Medium
MULTI FAMILY DWELLING
Yes
151
1
16
244
Medium
OFFICE BUILDING
No
152
1
17
127
Medium
HOTEL
Yes
153
1
17
244
Medium
OFFICE BUILDING
No
154
1
21
607
Medium
EMBASSIES/CHANCERIES
No
155
1
28
244
Medium
OFFICE BUILDING
No
156
1
31
607
Medium
EMBASSIES/CHANCERIES
No
157
1
32
244
Medium
OFFICE BUILDING
No
158
1
41
607
Medium
EMBASSIES/CHANCERIES
No
159
1
42
244
Medium
OFFICE BUILDING
No
160
1
43
133
Medium
MULTI FAMILY DWELLING
Yes
161
1
65
133
Medium
MULTI FAMILY DWELLING
Yes
162
1
nan
142
Medium
MOTEL
Yes
163
1
nan
164
Medium
TRANSIENT LODGING
Yes
164
1
nan
247
Medium
OFFICE CONDO
No
In [61]:
# Get counts over Missouri for medium/high structures
df = pd.read_sql_query("""select count(1), p.story_nbr, p.land_use, lu.risk_category, lu."Description", lu.residential
from parcels p
join "LUSE_swg" lu
on lu."Code" = p.land_use
where state = 'MO' and lu.include_in_floor_dist
group by p.story_nbr, p.land_use, lu.risk_category, lu."Description", lu.residential
order by count desc, p.story_nbr, p.land_use""", parcels)
df.style.highlight_null(null_color='red')
Out[61]:
count
story_nbr
land_use
risk_category
Description
residential
0
35826
1
112
Medium
CONDOMINIUM
Yes
1
16498
nan
112
Medium
CONDOMINIUM
Yes
2
12842
2
112
Medium
CONDOMINIUM
Yes
3
6409
nan
244
Medium
OFFICE BUILDING
No
4
5481
nan
106
Medium
APARTMENT
Yes
5
5467
1
133
Medium
MULTI FAMILY DWELLING
Yes
6
5435
2
151
Medium
QUADRUPLEX
Yes
7
5212
2
133
Medium
MULTI FAMILY DWELLING
Yes
8
3941
nan
133
Medium
MULTI FAMILY DWELLING
Yes
9
2430
1
132
Medium
MULTI FAMILY 10 UNITS LESS
Yes
10
1631
4
112
Medium
CONDOMINIUM
Yes
11
1535
1.5
133
Medium
MULTI FAMILY DWELLING
Yes
12
1218
1.5
112
Medium
CONDOMINIUM
Yes
13
1134
nan
278
Medium
STORE BUILDING
No
14
1057
3
112
Medium
CONDOMINIUM
Yes
15
946
1
244
Medium
OFFICE BUILDING
No
16
898
nan
247
Medium
OFFICE CONDO
No
17
880
nan
279
Medium
STORES & OFFICES
No
18
823
2
106
Medium
APARTMENT
Yes
19
706
nan
650
High
SCHOOL
No
20
633
nan
660
High
EDUCATIONAL SERVICE
No
21
601
nan
213
Medium
COMMERCIAL CONDOMINIUM
No
22
564
nan
134
Medium
MIXED COMPLEX
Yes
23
554
1
106
Medium
APARTMENT
Yes
24
534
2
132
Medium
MULTI FAMILY 10 UNITS LESS
Yes
25
500
2
165
Medium
TRIPLEX
Yes
26
442
nan
103
Medium
APARTMENT/HOTEL
Yes
27
439
1
278
Medium
STORE BUILDING
No
28
397
1
151
Medium
QUADRUPLEX
Yes
29
373
2.5
133
Medium
MULTI FAMILY DWELLING
Yes
30
303
nan
142
Medium
MOTEL
Yes
31
264
3
151
Medium
QUADRUPLEX
Yes
32
238
nan
127
Medium
HOTEL
Yes
33
228
nan
652
High
NURSERY SCHOOL
No
34
213
1.5
132
Medium
MULTI FAMILY 10 UNITS LESS
Yes
35
203
1
650
High
SCHOOL
No
36
196
1
165
Medium
TRIPLEX
Yes
37
178
3
165
Medium
TRIPLEX
Yes
38
165
nan
165
Medium
TRIPLEX
Yes
39
164
2
244
Medium
OFFICE BUILDING
No
40
161
nan
132
Medium
MULTI FAMILY 10 UNITS LESS
Yes
41
156
nan
680
High
UNIVERSITY
No
42
149
nan
157
High
NURSING HOME
Yes
43
140
2
278
Medium
STORE BUILDING
No
44
130
1
213
Medium
COMMERCIAL CONDOMINIUM
No
45
113
3
133
Medium
MULTI FAMILY DWELLING
Yes
46
84
nan
156
Medium
ORPHANAGE
Yes
47
82
nan
118
High
FRAT/SORORITY HOUSE
Yes
48
82
nan
151
Medium
QUADRUPLEX
Yes
49
76
nan
281
Medium
STORES & RESIDENTIAL
No
50
74
1.75
133
Medium
MULTI FAMILY DWELLING
Yes
51
55
1.5
650
High
SCHOOL
No
52
53
2
281
Medium
STORES & RESIDENTIAL
No
53
48
1
652
High
NURSERY SCHOOL
No
54
44
10
112
Medium
CONDOMINIUM
Yes
55
39
1.5
165
Medium
TRIPLEX
Yes
56
37
3
106
Medium
APARTMENT
Yes
57
36
1.5
106
Medium
APARTMENT
Yes
58
34
1
134
Medium
MIXED COMPLEX
Yes
59
33
1
157
High
NURSING HOME
Yes
60
31
2.5
112
Medium
CONDOMINIUM
Yes
61
31
9
133
Medium
MULTI FAMILY DWELLING
Yes
62
29
1
142
Medium
MOTEL
Yes
63
29
2
164
Medium
TRANSIENT LODGING
Yes
64
28
nan
119
Medium
RESIDENCE HALL/DORMITORIES
Yes
65
27
2
650
High
SCHOOL
No
66
25
1
103
Medium
APARTMENT/HOTEL
Yes
67
24
2
142
Medium
MOTEL
Yes
68
23
nan
164
Medium
TRANSIENT LODGING
Yes
69
20
nan
246
Medium
OFFICE & SHOWROOM
No
70
18
1.25
133
Medium
MULTI FAMILY DWELLING
Yes
71
18
2.75
133
Medium
MULTI FAMILY DWELLING
Yes
72
17
1
281
Medium
STORES & RESIDENTIAL
No
73
15
2
103
Medium
APARTMENT/HOTEL
Yes
74
13
1.5
244
Medium
OFFICE BUILDING
No
75
12
1
118
High
FRAT/SORORITY HOUSE
Yes
76
12
1
127
Medium
HOTEL
Yes
77
11
1
660
High
EDUCATIONAL SERVICE
No
78
11
1.5
151
Medium
QUADRUPLEX
Yes
79
11
1.5
278
Medium
STORE BUILDING
No
80
10
nan
245
Medium
OFFICE & RESIDENTIAL
No
81
9
3
142
Medium
MOTEL
Yes
82
8
2.25
133
Medium
MULTI FAMILY DWELLING
Yes
83
8
2.5
164
Medium
TRANSIENT LODGING
Yes
84
8
3
244
Medium
OFFICE BUILDING
No
85
8
3
278
Medium
STORE BUILDING
No
86
7
1.5
213
Medium
COMMERCIAL CONDOMINIUM
No
87
7
2
213
Medium
COMMERCIAL CONDOMINIUM
No
88
7
2.5
132
Medium
MULTI FAMILY 10 UNITS LESS
Yes
89
7
5
112
Medium
CONDOMINIUM
Yes
90
6
1.5
103
Medium
APARTMENT/HOTEL
Yes
91
6
1.5
164
Medium
TRANSIENT LODGING
Yes
92
6
2
127
Medium
HOTEL
Yes
93
6
2
157
High
NURSING HOME
Yes
94
6
2.5
165
Medium
TRIPLEX
Yes
95
6
3.5
112
Medium
CONDOMINIUM
Yes
96
5
1
279
Medium
STORES & OFFICES
No
97
5
1.5
281
Medium
STORES & RESIDENTIAL
No
98
5
9
281
Medium
STORES & RESIDENTIAL
No
99
4
2
118
High
FRAT/SORORITY HOUSE
Yes
100
4
2
279
Medium
STORES & OFFICES
No
101
4
4
106
Medium
APARTMENT
Yes
102
3
2
134
Medium
MIXED COMPLEX
Yes
103
3
2
660
High
EDUCATIONAL SERVICE
No
104
3
2.5
106
Medium
APARTMENT
Yes
105
3
3
281
Medium
STORES & RESIDENTIAL
No
106
3
9
112
Medium
CONDOMINIUM
Yes
107
2
1
156
Medium
ORPHANAGE
Yes
108
2
1
680
High
UNIVERSITY
No
109
2
1.25
132
Medium
MULTI FAMILY 10 UNITS LESS
Yes
110
2
1.25
244
Medium
OFFICE BUILDING
No
111
2
1.5
134
Medium
MIXED COMPLEX
Yes
112
2
2.5
151
Medium
QUADRUPLEX
Yes
113
2
2.5
281
Medium
STORES & RESIDENTIAL
No
114
2
3
132
Medium
MULTI FAMILY 10 UNITS LESS
Yes
115
2
3
279
Medium
STORES & OFFICES
No
116
2
4
133
Medium
MULTI FAMILY DWELLING
Yes
117
2
nan
656
High
VOCATIONAL/TRADE SCHOOL
No
118
1
1
164
Medium
TRANSIENT LODGING
Yes
119
1
1.5
652
High
NURSERY SCHOOL
No
120
1
1.5
660
High
EDUCATIONAL SERVICE
No
121
1
2
119
Medium
RESIDENCE HALL/DORMITORIES
Yes
122
1
2.5
244
Medium
OFFICE BUILDING
No
123
1
2.5
278
Medium
STORE BUILDING
No
124
1
2.5
650
High
SCHOOL
No
125
1
3
127
Medium
HOTEL
Yes
126
1
3
213
Medium
COMMERCIAL CONDOMINIUM
No
127
1
3.5
106
Medium
APARTMENT
Yes
128
1
3.75
133
Medium
MULTI FAMILY DWELLING
Yes
129
1
4
244
Medium
OFFICE BUILDING
No
130
1
5
106
Medium
APARTMENT
Yes
131
1
8
106
Medium
APARTMENT
Yes
132
1
9
157
High
NURSING HOME
Yes
133
1
13
112
Medium
CONDOMINIUM
Yes
134
1
nan
665
High
PUBLIC SCHOOL
No
In [2]:
# Pull CSV for land use codes to include in DIST score
df = pd.read_csv("/Users/joe/Downloads/risk_categories.csv")
df2 = df[df['floor_dist'].notnull()].sort_values(by='Code')
df2
Out[2]:
Code
Description
risk_category
residential
floor_dist
251
103
APARTMENT/HOTEL
Medium
Yes
yes
252
106
APARTMENT
Medium
Yes
yes
253
111
COOPERATIVE
Medium
Yes
yes
254
112
CONDOMINIUM
Medium
Yes
yes
255
113
CONDOMINIUM PROJECT
Medium
Yes
yes
232
116
MID RISE CONDO
High
Yes
yes
233
117
HIGH RISE CONDO
High
Yes
yes
257
118
FRAT/SORORITY HOUSE
High
Yes
yes
258
119
RESIDENCE HALL/DORMITORIES
Medium
Yes
yes
259
127
HOTEL
Medium
Yes
yes
234
130
RESORT HOTEL
Medium
Yes
yes
260
131
MULTI FAMILY 10 UNITS PLUS
Medium
Yes
yes
235
132
MULTI FAMILY 10 UNITS LESS
Medium
Yes
yes
261
133
MULTI FAMILY DWELLING
Medium
Yes
yes
262
134
MIXED COMPLEX
Medium
Yes
yes
263
142
MOTEL
Medium
Yes
yes
264
151
QUADRUPLEX
Medium
Yes
yes
265
155
GROUP QUARTERS
Medium
Yes
yes
236
156
ORPHANAGE
Medium
Yes
yes
237
157
NURSING HOME
High
Yes
yes
266
164
TRANSIENT LODGING
Medium
Yes
yes
238
165
TRIPLEX
Medium
Yes
yes
231
206
CONDOTEL
Medium
Yes
yes
10
209
BUSINESS PARK
Medium
No
yes
14
213
COMMERCIAL CONDOMINIUM
Medium
No
yes
29
236
LOFT BUILDING
Medium
No
yes
36
244
OFFICE BUILDING
Medium
No
yes
37
245
OFFICE & RESIDENTIAL
Medium
No
yes
38
246
OFFICE & SHOWROOM
Medium
No
yes
39
247
OFFICE CONDO
Medium
No
yes
40
248
CONVERTED RESIDENCE
Medium
No
yes
57
278
STORE BUILDING
Medium
No
yes
58
279
STORES & OFFICES
Medium
No
yes
59
281
STORES & RESIDENTIAL
Medium
No
yes
152
607
EMBASSIES/CHANCERIES
Medium
No
yes
164
650
SCHOOL
High
No
yes
165
652
NURSERY SCHOOL
High
No
yes
166
654
HIGH SCHOOL
High
No
yes
167
655
PRIVATE SCHOOL
High
No
yes
168
656
VOCATIONAL/TRADE SCHOOL
High
No
yes
169
658
VOCATIONAL/TRADE SCHOOL
High
No
yes
170
660
EDUCATIONAL SERVICE
High
No
yes
171
664
SEC EDUCATIONAL SCHOOL
High
No
yes
172
665
PUBLIC SCHOOL
High
No
yes
175
680
UNIVERSITY
High
No
yes
In [4]:
%sql postgresql:///?service=parcels alter table "LUSE_swg" add column include_in_floor_dist boolean not null default false;
Done.
Out[4]:
[]
In [ ]:
# Import included land use types in the DIST calculations
included_codes = map(str, list(df2['Code'].values))
for code in included_codes:
%sql update "LUSE_swg" set include_in_floor_dist = true where "Code" = :code
In [ ]:
%%sql
select * from
parcels p
join firedepartment_owned_geom og
on ST_Intersects(p.wkb_geometry, og.owned_tracts_geom)
where og.id = 87256
group by
In [ ]:
# Get most egregious offenders for null story counts by land usage category
df = pd.read_sql_query("""select count(1), p.story_nbr, p.land_use, lu.risk_category, lu."Description", lu.residential
from parcels p
join "LUSE_swg" lu
on lu."Code" = p.land_use
where lu.include_in_floor_dist and p.story_nbr is null
group by p.story_nbr, p.land_use, lu.risk_category, lu."Description", lu.residential
order by count desc, p.story_nbr, p.land_use""", parcels)
df.style.highlight_null(null_color='red')
In [4]:
from scipy.stats import lognorm
from matplotlib import pyplot as plt
In [ ]:
%%sql postgresql:///?service=parcels
select count(1), story_nbr
from parcel_stories p
join firedepartment_owned_geom g on ST_Intersects(p.wkb_geometry, g.owned_tracts_geom)
where p.state = 'CA'
group by story_nbr
limit 1000;
In [ ]:
# Get counts over LAFD for medium/high structures
df = pd.read_sql_query("""select count(1), p.story_nbr, p.land_use, lu.risk_category, lu."Description", lu.residential
from parcels p
join "LUSE_swg" lu on lu."Code" = p.land_use
join firedepartment_owned_geom g on ST_Intersects(g.owned_tracts_geom, p.wkb_geometry)
where lu.include_in_floor_dist and lu.risk_category = 'Medium'
group by p.story_nbr, p.land_use, lu.risk_category, lu."Description", lu.residential
order by count desc, p.story_nbr, p.land_use""", parcels)
df.style.highlight_null(null_color='red')
In [24]:
# Get counts for structures types over ALL states
df = pd.read_sql_query("""select count(1), p.story_nbr, lu."Code", lu."Description", p.state
from parcel_stories p
join "LUSE_swg" lu
on lu."Code" = p.land_use
where lu.include_in_floor_dist
group by p.story_nbr, lu."Code", lu."Description", p.state
having count(1) > 10
order by p.state, count desc, story_nbr;""", parcels)
df
Out[24]:
count
story_nbr
Code
Description
state
0
2742
NaN
106
APARTMENT
AK
1
2738
NaN
112
CONDOMINIUM
AK
2
1435
NaN
133
MULTI FAMILY DWELLING
AK
3
852
1.00
133
MULTI FAMILY DWELLING
AK
4
804
NaN
244
OFFICE BUILDING
AK
5
417
1.50
133
MULTI FAMILY DWELLING
AK
6
401
2.00
133
MULTI FAMILY DWELLING
AK
7
345
2.00
112
CONDOMINIUM
AK
8
342
2.00
165
TRIPLEX
AK
9
240
1.00
165
TRIPLEX
AK
10
180
1.75
133
MULTI FAMILY DWELLING
AK
11
168
NaN
165
TRIPLEX
AK
12
142
NaN
660
EDUCATIONAL SERVICE
AK
13
126
NaN
650
SCHOOL
AK
14
125
1.00
112
CONDOMINIUM
AK
15
122
NaN
164
TRANSIENT LODGING
AK
16
120
1.00
244
OFFICE BUILDING
AK
17
112
NaN
127
HOTEL
AK
18
94
2.00
106
APARTMENT
AK
19
72
1.00
119
RESIDENCE HALL/DORMITORIES
AK
20
60
NaN
119
RESIDENCE HALL/DORMITORIES
AK
21
48
1.00
106
APARTMENT
AK
22
48
2.00
244
OFFICE BUILDING
AK
23
42
NaN
652
NURSERY SCHOOL
AK
24
38
NaN
151
QUADRUPLEX
AK
25
36
NaN
655
PRIVATE SCHOOL
AK
26
34
NaN
665
PUBLIC SCHOOL
AK
27
33
2.00
119
RESIDENCE HALL/DORMITORIES
AK
28
30
2.00
142
MOTEL
AK
29
29
NaN
278
STORE BUILDING
AK
...
...
...
...
...
...
3990
505
3.00
106
APARTMENT
WY
3991
415
1.00
244
OFFICE BUILDING
WY
3992
369
3.00
112
CONDOMINIUM
WY
3993
357
1.00
106
APARTMENT
WY
3994
346
1.00
112
CONDOMINIUM
WY
3995
343
NaN
244
OFFICE BUILDING
WY
3996
245
1.00
133
MULTI FAMILY DWELLING
WY
3997
196
1.00
165
TRIPLEX
WY
3998
167
2.00
112
CONDOMINIUM
WY
3999
150
2.00
133
MULTI FAMILY DWELLING
WY
4000
121
2.00
106
APARTMENT
WY
4001
111
2.00
244
OFFICE BUILDING
WY
4002
71
3.00
133
MULTI FAMILY DWELLING
WY
4003
60
NaN
142
MOTEL
WY
4004
50
NaN
134
MIXED COMPLEX
WY
4005
46
NaN
112
CONDOMINIUM
WY
4006
45
1.00
142
MOTEL
WY
4007
45
2.00
142
MOTEL
WY
4008
32
NaN
133
MULTI FAMILY DWELLING
WY
4009
25
1.00
279
STORES & OFFICES
WY
4010
25
1.50
244
OFFICE BUILDING
WY
4011
21
1.50
165
TRIPLEX
WY
4012
20
NaN
127
HOTEL
WY
4013
16
1.00
278
STORE BUILDING
WY
4014
16
NaN
279
STORES & OFFICES
WY
4015
14
1.00
652
NURSERY SCHOOL
WY
4016
12
1.00
246
OFFICE & SHOWROOM
WY
4017
12
3.00
244
OFFICE BUILDING
WY
4018
12
NaN
652
NURSERY SCHOOL
WY
4019
11
NaN
278
STORE BUILDING
WY
4020 rows × 5 columns
In [ ]:
%%sql postgresql:///?service=parcels
select count(1), p.story_nbr, lu."Code", lu."Description", p.state
from parcel_stories p
join "LUSE_swg" lu
on lu."Code" = p.land_use
join firedepartment_owned_geom g
on ST_Intersects(g.owned_tracts_geom, p.wkb_geometry)
where g.id = 87256 and lu.include_in_floor_dist
group by p.story_nbr, lu."Code", lu."Description", p.state
order by p.state, count desc, story_nbr;
In [ ]:
# Move parcel_stories over to NFIRS db (takes quite awhile)
%%bash
pg_dump service=parcels -O -t parcel_stories > /tmp/parcel_stories.sql
psql service=nfrs < /tmp/parcel_stories.sql
In [6]:
with nfirs.cursor() as c:
c.execute("""CREATE INDEX ON parcel_stories USING gist (wkb_geometry);""")
c.execute("""CREATE INDEX on parcel_stories (state);""")
c.execute("""CREATE INDEX on parcel_stories (land_use);""")
In [ ]:
%%bash
pg_dump service=parcels -t firedepartment_owned_geom -O | psql service=nfirs
pg_dump service=parcels -t \"LUSE_swg\" -O | psql service=nfirs
In [ ]:
%%sql postgresql:///?service=nfirs
select count(1), p.story_nbr, p.land_use, lu.risk_category, lu."Description", lu.residential
from parcel_stories p
join "LUSE_swg" lu on lu."Code" = p.land_use
join firedepartment_owned_geom g on ST_Intersects(g.owned_tracts_geom, p.wkb_geometry)
where lu.include_in_floor_dist and lu.risk_category = 'Medium'
group by p.story_nbr, p.land_use, lu.risk_category, lu."Description", lu.residential
order by count desc, p.story_nbr, p.land_use;
In [103]:
%%sql postgresql:///?service=nfirs
select count(1), p.story_nbr
from parcel_stories p
join "LUSE_swg" lu on lu."Code" = p.land_use,
(select * from firedepartment_owned_geom) as g
where lu.include_in_floor_dist and lu.risk_category = 'Medium'
and ST_Intersects(g.owned_tracts_geom, p.wkb_geometry)
group by p.story_nbr
order by count desc, p.story_nbr;
137 rows affected.
Out[103]:
count
story_nbr
508284
None
329871
2.0
257268
1.0
117398
3.0
27059
4.0
15818
5.0
11889
6.0
7280
1.5
4713
2.5
2636
2.75
2083
7.0
1027
8.0
947
12.0
727
9.0
641
3.5
577
10.0
538
13.0
511
16.0
489
1.75
462
11.0
438
15.0
415
14.0
367
1.3
336
20.0
245
1.1
242
17.0
226
1.2
204
19.0
199
21.0
184
4.5
183
18.0
152
1.4
117
1.7
116
22.0
103
23.0
96
24.0
87
26.0
85
1.6
85
25.0
84
2.25
81
1.67
71
30.0
66
3.75
66
32.0
64
1.25
64
33.0
63
27.0
59
2.67
59
31.0
54
28.0
46
35.0
40
29.0
40
42.0
39
5.5
35
34.0
35
36.0
32
40.0
32
41.0
30
6.5
27
39.0
26
2.7
26
38.0
24
37.0
21
43.0
21
45.0
17
44.0
16
46.0
15
1.8
15
48.0
14
50.0
13
51.0
12
53.0
11
3.67
10
52.0
10
54.0
10
57.0
9
4.3
9
49.0
8
4.75
8
47.0
7
72.0
6
7.5
6
58.0
5
2.33
5
4.25
4
8.5
4
9.9
4
59.0
4
60.0
4
70.0
3
3.25
3
5.75
3
6.25
3
11.5
3
75.0
3
90.0
2
2.3
2
3.33
2
3.4
2
3.6
2
9.5
2
55.0
2
56.0
2
65.0
2
66.0
2
71.0
2
88.0
2
99.0
1
1.15
1
2.4
1
2.66
1
2.87
1
3.3
1
5.09
1
5.25
1
6.75
1
10.5
1
13.5
1
20.5
1
28.5
1
60.5
1
61.0
1
62.0
1
63.0
1
64.0
1
68.0
1
69.0
1
76.0
1
77.0
1
78.0
1
85.0
1
99.9
1
104.0
1
150.0
1
176.0
1
472.0
1
792.0
In [1]:
res = _
In [ ]:
from scipy.stats import lognorm
import numpy as np
import matplotlib.pyplot as plt
# Filter out `None` story counts
a = map(lambda x: (x[0], x[1] if x[1] else 0), filter(lambda x: x[1] is not None and x[1] <= 108, res))
weights = np.array(map(lambda x: x[0], a))
values = np.array(map(lambda x: x[1], a))
def expand(values, weights):
ret = []
for v in zip(values, weights):
ret = ret + [v[0]] * v[1]
return ret
#expanded = expand(values, weights)
print weights
print values
print 'NORMALIZED WEIGHT sum' # Should be 1
print sum(weights / float(sum(weights)))
samples = np.random.choice(values, size=1000, p=weights / float(sum(weights)))
print 'SAMPLES'
print samples[:100]
x = np.linspace(0, max(samples), 1000)
samp = lognorm.fit(samples)
print 'CURVE'
print samp
rvs_samples = lognorm.rvs(*samp, size=1000)
print 'RVS_SAMPLES'
print rvs_samples[:100]
pdf_fitted = lognorm.pdf(x, samp[0], loc=samp[1], scale=samp[2])
In [110]:
print 'PDF_FITTED'
print pdf_fitted[:100]
plt.hist(samples, 50, normed=1)
#plt.gca().set_yscale("log")
#plt.plot(x, pdf_fitted, 'r-')
plt.show()
PDF_FITTED
[ 0. 0. 0. 0. 0. 0. 0.
0. 0. 0. 0. 0. 0. 0.
0. 0. 0. 0. 0. 0. 0.
0. 0. 0. 0. 0. 0. 0.
0. 0. 0. 2.45488682 1.09207787 0.70625941
0.5225209 0.41473669 0.34378317 0.29350263 0.25599481 0.226935
0.20375517 0.18483346 0.16909483 0.15579834 0.14441664 0.13456409
0.12595217 0.11836067 0.1116186 0.10559117 0.10017065 0.09526997
0.09081791 0.08675573 0.08303449 0.07961311 0.07645685 0.07353615
0.07082567 0.06830358 0.06595097 0.06375137 0.06169039 0.05975538
0.05793517 0.05621989 0.05460077 0.05306996 0.05162048 0.05024603
0.04894098 0.04770022 0.04651913 0.04539355 0.04431966 0.043294
0.04231341 0.04137502 0.04047615 0.03961439 0.03878749 0.0379934
0.03723021 0.03649617 0.03578964 0.03510912 0.03445321 0.0338206
0.03321008 0.03262053 0.03205089 0.03150017 0.03096746 0.03045189
0.02995265 0.02946898 0.02900018 0.02854557 0.02810452 0.02767644]
In [94]:
# utils yanked from DIST calculation
class DrawType(object):
"""
Implements a base DrawType object.
"""
def draw(self):
raise NotImplementedError
class LogNormalDraw(DrawType):
"""
Implements a log normal draw.
>>> import numpy as np
>>> np.random.seed(1234)
>>> ud = LogNormalDraw(0.3381962232249362, -1.0844073333047395, 3.1682731892016429)
>>> ud.draw()
2.631505249260421
"""
def __init__(self, shape, location, scale, multiplier=1):
self.shape = shape
self.location = location
self.scale = scale
self.multiplier = multiplier
def draw(self):
return lognorm.rvs(self.shape, self.location, self.scale) * self.multiplier
In [104]:
print samp
lnd = LogNormalDraw(*samp)
samples = []
for i in range(10000):
samples.append(lnd.draw())
npa = np.array(samples)
print npa.mean()
print npa.max()
print npa.min()
plt.hist(samples, 50, normed=1)
plt.show()
(4.6229973046852173, 0.9999999999999829, 3.2652912388296444e-05)
3.58724348512
22590.3222823
1.0
In [ ]:
%%bash
pg_dump service=parcels -t \"LUSE_swg\" -O | psql service=nfirs
In [22]:
%%sql postgresql:///?service=firecares-dev
select count(1) from firestation_firedepartment where owned_tracts_geom is not null;
1 rows affected.
Out[22]:
count
17491
In [23]:
%%bash
pg_dump service=firecares-dev -t firestation_firedepartment -O > /tmp/firestation_firedepartment.sql
sed -i -e 's/firestation_firedepartment/firestation_firedepartment_dev/g' /tmp/firestation_firedepartment.sql
In [ ]:
%%sql postgresql:///?service=nfirs
In [6]:
with fc_dev.cursor() as c:
c.execute('select 1;')
res = c.fetchall()
res
Out[6]:
[(1,)]
In [4]:
similar_to_lafd = [87256, 87255,
77379,
84578,
88539,
91907,
91934,
91105,
94250,
77867,
89649,
79277,
73343,
81147,
81472]
In [5]:
with fc_dev.cursor() as c:
c.execute('select owned_tracts_geom from firestation_firedepartment where id in %(similar)s', {'similar': tuple(similar_to_lafd)})
geoms = c.fetchall()
In [8]:
with nfirs.cursor() as c:
for g in geoms:
c.execute('insert into firedepartment_owned_geom (owned_tracts_geom) values (%(geom)s::geometry)', {'geom': g[0]})
nfirs.commit()
0106000020
0106000020
0106000020
0106000020
0106000020
0106000020
0106000020
0106000020
0106000020
0106000020
0106000020
0106000020
0106000020
0106000020
0106000020
Content source: FireCARES/data
Similar notebooks: