In [1]:
import psycopg2
import pandas as pd
from IPython.display import display
conn = psycopg2.connect(service='nfirs')
pd.options.display.max_rows = 1000
df = pd.read_sql_query("select * from codelookup where fieldid = 'PROP_USE' and length(code_value) = 3 order by code_value", conn)['code_value']
codes = list(df.values)
In [2]:
q = """
create materialized view if not exists holistic_buildingfires as (select b.*, f.num_unit, f.not_res, f.bldg_invol, f.acres_burn, f.less_1acre, f.on_site_m1, f.mat_stor1, f.on_site_m2,
f.mat_stor2, f.on_site_m3, f.mat_stor3, f.area_orig, f.heat_sourc, f.first_ign, f.conf_orig, f.type_mat, f.cause_ign,
f.fact_ign_1, f.fact_ign_2, f.hum_fac_1, f.hum_fac_2, f.hum_fac_3, f.hum_fac_4, f.hum_fac_5, f.hum_fac_6, f.hum_fac_7,
f.hum_fac_8, f.age, f.sex, f.equip_inv, f.sup_fac_1, f.sup_fac_2, f.sup_fac_3, f.mob_invol, f.mob_type, f.mob_make, f.mob_model,
f.mob_year, f.mob_lic_pl, f.mob_state, f.mob_vin_no, f.eq_brand, f.eq_model, f.eq_ser_no, f.eq_year, f.eq_power, f.eq_port,
f.fire_sprd, f.struc_type, f.struc_stat, f.bldg_above, f.bldg_below, f.bldg_lgth, f.bldg_width, f.tot_sq_ft, f.fire_orig,
f.st_dam_min, f.st_dam_sig, f.st_dam_hvy, f.st_dam_xtr, f.flame_sprd, f.item_sprd, f.mat_sprd, f.detector, f.det_type,
f.det_power, f.det_operat, f.det_effect, f.det_fail, f.aes_pres, f.aes_type, f.aes_oper, f.no_spr_op, f.aes_fail from basicincident b
inner join fireincident f
on ltrim(trim(b.inc_no), '0') = ltrim(trim(f.inc_no), '0') and
ltrim(trim(b.fdid), '0') = ltrim(trim(f.fdid), '0') and
b.state = f.state and
b.inc_date = f.inc_date and
trim(b.exp_no) = trim(f.exp_no)
);
"""
with conn.cursor() as c:
c.execute(q)
conn.commit()
In [2]:
# Create a CSV for each property use type
q = """
SELECT x.prop_use,
area_orig,
first_ign,
x.civ_inj,
x.civ_death,
x.flame_sprd,
x.item_sprd,
x.cnt
FROM
( SELECT *,
row_number() over (partition BY area_orig
ORDER BY area_orig, w.cnt DESC, first_ign, w.flame_sprd,w.item_sprd, w.civ_death, w.civ_inj DESC) row_num
FROM
(SELECT distinct bf.area_orig,
bf.first_ign,
bf.prop_use,
bf.flame_sprd,
bf.item_sprd,
COALESCE(bf.oth_death, 0) as civ_death,
COALESCE(bf.oth_inj, 0) as civ_inj,
count(*) OVER ( PARTITION BY bf.area_orig, bf.first_ign, bf.flame_sprd, bf.item_sprd, COALESCE(bf.oth_death, 0)+COALESCE(bf.oth_inj, 0) ) AS cnt,
row_number() OVER ( PARTITION BY bf.area_orig, bf.first_ign, bf.flame_sprd, bf.item_sprd, COALESCE(bf.oth_death, 0)+COALESCE(bf.oth_inj, 0) ) AS row_numbers
FROM holistic_buildingfires bf
WHERE bf.area_orig IN
( SELECT area_orig
FROM holistic_buildingfires
WHERE prop_use = %(use)s
AND area_orig != 'UU'
AND extract(year from inc_date) > 2011
GROUP BY area_orig
ORDER BY count(1) DESC LIMIT 8)
AND bf.prop_use = %(use)s
AND bf.first_ign != 'UU'
AND extract(year from inc_date) > 2011
ORDER BY area_orig,
first_ign ) w
WHERE w.row_numbers = 1) x
ORDER BY area_orig,
x.cnt DESC,
first_ign
"""
# for c in codes[1:2]:
# df = pd.read_sql_query(q, conn, params=dict(use=c))
# display(df)
for c in codes:
df = pd.read_sql_query(q, conn, params=dict(use=c))
df.to_csv('/tmp/{}.csv'.format(c))
In [ ]:
# Testing/sanity checks
q = """SELECT bf.prop_use, bf.area_orig,
bf.first_ign,
bf.flame_sprd,
COALESCE(bf.oth_death, 0) + COALESCE(bf.oth_inj,0) as civ_inj_death,
count(*) OVER ( PARTITION BY bf.area_orig, bf.first_ign, bf.flame_sprd, COALESCE(bf.oth_death, 0)+COALESCE(bf.oth_inj,0) ) AS cnt,
row_number() OVER ( PARTITION BY bf.area_orig, bf.first_ign, bf.flame_sprd, COALESCE(bf.oth_death, 0)+COALESCE(bf.oth_inj,0) ) AS row_numbers
FROM buildingfires bf
WHERE bf.area_orig IN
( SELECT area_orig
FROM buildingfires
WHERE prop_use = %(use)s
AND area_orig != 'UU'
GROUP BY area_orig
ORDER BY count(1) DESC LIMIT 8)
AND bf.prop_use = %(use)s
AND bf.first_ign != 'UU'
ORDER BY area_orig,
first_ign,
cnt desc"""
pd.read_sql_query(q, conn, params=dict(use='100'))
In [ ]:
q = """
select count(1)
from joint_buildingfires
where prop_use='100'
and area_orig = '00'
and first_ign = '00'
and COALESCE(oth_death, 0) + COALESCE(oth_inj, 0) = 0
and flame_sprd = 'N'
"""
pd.read_sql_query(q, conn)
In [ ]:
# Sanity checks
q = """
select area_orig, first_ign, count(1)
from joint_buildingfires
where area_orig != 'UU'
and first_ign != 'UU'
group by area_orig, first_ign
order by count desc
"""
pd.read_sql_query(q, conn)
In [ ]:
# More sanity checks, including civ death/inj + flame spread
q = """
select area_orig, first_ign, flame_sprd, COALESCE(oth_death, 0)+COALESCE(oth_inj,0) as civ_death_inj, count(1)
from joint_buildingfires
where area_orig != 'UU'
and first_ign != 'UU'
group by area_orig, first_ign, flame_sprd, civ_death_inj
order by count desc"""
pd.read_sql_query(q, conn)
In [ ]:
# For grouped propety usage only 6 most popular ignition sources
q = """
--
SELECT area_orig,
first_ign,
x.cnt
FROM
( SELECT *,
row_number() over (partition BY area_orig
ORDER BY area_orig, w.cnt DESC, first_ign) row_num
FROM
(SELECT bf.area_orig,
bf.first_ign,
count(*) OVER ( PARTITION BY bf.area_orig, bf.first_ign ) AS cnt,
row_number() OVER ( PARTITION BY bf.area_orig, bf.first_ign ) AS row_numbers
FROM joint_buildingfires bf
WHERE bf.area_orig IN
( SELECT area_orig
FROM joint_buildingfires
WHERE prop_use in ('120', '121', '122', '123', '124', '129')
AND area_orig != 'UU'
GROUP BY area_orig
ORDER BY count(1) DESC LIMIT 8)
AND bf.prop_use in ('120', '121', '122', '123', '124', '129')
AND bf.first_ign != 'UU'
ORDER BY area_orig,
first_ign ) w
WHERE w.row_numbers = 1) x
WHERE x.row_num < 7
ORDER BY area_orig,
x.cnt DESC,
first_ign
"""
df = pd.read_sql_query(q, conn)
display(df)
In [ ]:
# Pull all from buildingfires to CSV
q = """
select prop_use, area_orig, first_ign, oth_inj, oth_death, flame_sprd
from joint_buildingfires"""
df = pd.read_sql_query(q, conn)
df.to_csv('/tmp/buildingfires.csv')
In [ ]: