

In [ ]:

    
import sys
import os
import time
import pandas as pd
sys.path.insert(0, os.path.realpath('..'))
import django
django.setup()
from django.db import connections
from firecares.firestation.models import FireDepartment, FireStation, NFIRSStatistic

fdid = '20019'
state = 'NJ'
years = tuple(map(int, pd.read_sql_query('select distinct(extract(year from inc_date)) as year from buildingfires order by year desc;', connections['nfirs'])['year']))
fd = FireDepartment.objects.filter(fdid=fdid, state=state).first()

params = dict(fdid=fdid, state=state, years=years)

Firefighter casualty segmented by structure hazard level vs flat NFIRS statistics

Using closest parcel



In [ ]:

    
%%time

q = """select count(1), extract(year from inc_date) as year, COALESCE(b.risk_category, 'N/A') as risk_category
FROM ffcasualty a left join (SELECT
  *
FROM (
  SELECT state, 
    fdid,
    inc_date,
    inc_no,
    exp_no,
    geom,
    b.parcel_id,
    b.wkb_geometry,
    b.risk_category,
    ROW_NUMBER() OVER (PARTITION BY state, fdid, inc_date, inc_no, exp_no, geom ORDER BY st_distance(b.wkb_geometry, a.geom)) AS r
  FROM (select * from incidentaddress where state=%(state)s and fdid=%(fdid)s) a
     left join parcel_risk_category_local b on a.geom && b.wkb_geometry
     ) x
WHERE x.r = 1) b using (state, inc_date, exp_no, fdid, inc_no) where state=%(state)s and fdid=%(fdid)s and extract(year from inc_date) in %(years)s
GROUP by extract(year from inc_date), COALESCE(b.risk_category, 'N/A')
ORDER BY extract(year from inc_date) DESC"""
    
df = pd.read_sql_query(q, connections['nfirs'], params=params)

print df

print df.groupby('year').sum()

Using old non risk-aware method



In [ ]:

    
%%time
q = """select extract(year from inc_date) as year, count(*) from ffcasualty where fdid=%(fdid)s and state=%(state)s and extract(year from inc_date) in %(years)s group by year order by year desc;"""
cur = connections['nfirs'].cursor()


print pd.read_sql_query(q, connections['nfirs'], params=params)

Using `parcel_id` join to `parcel_risk_category_local`



In [ ]:

    
%%time

q = """SELECT count(1) as count, extract(year from a.inc_date) as year, COALESCE(y.risk_category, 'N/A') as risk_level
FROM ffcasualty a
LEFT JOIN
    (SELECT state, fdid, inc_date, inc_no, exp_no, x.parcel_id, x.risk_category
        FROM ( SELECT *
            FROM incidentaddress a
            LEFT JOIN parcel_risk_category_local using (parcel_id)
        ) AS x
    ) AS y
USING (state, fdid, inc_date, inc_no, exp_no)
WHERE a.state = %(state)s AND a.fdid = %(fdid)s AND extract(year FROM a.inc_date) IN %(years)s
GROUP BY y.risk_category, extract(year from a.inc_date)
ORDER BY extract(year from a.inc_date) DESC"""

print pd.read_sql_query(q, connections['nfirs'], params=params)

Building fires segmented by structure hazard level vs existing NFIRS statistics



In [ ]:

    
%%time

q = """select count(1), extract(year from alarm) as year, COALESCE(b.risk_category, 'N/A') as risk_category
FROM buildingfires a left join (SELECT
  *
FROM (
  SELECT state,
    fdid,
    inc_date,
    inc_no,
    exp_no,
    geom,
    b.parcel_id,
    b.wkb_geometry,
    b.risk_category,
    ROW_NUMBER() OVER (PARTITION BY state, fdid, inc_date, inc_no, exp_no, geom ORDER BY st_distance(st_centroid(b.wkb_geometry), a.geom)) AS r
  FROM (select * from incidentaddress where state=%(state)s and fdid=%(fdid)s) a
     left join parcel_risk_category_local b on a.geom && b.wkb_geometry
     ) x
WHERE x.r = 1) b using (state, inc_date, exp_no, fdid, inc_no) where state=%(state)s and fdid=%(fdid)s and extract(year from alarm) in %(years)s
GROUP by extract(year from alarm), COALESCE(b.risk_category, 'N/A')
ORDER BY extract(year from alarm) DESC"""

print pd.read_sql_query(q, connections['nfirs'], params=params)

Using directly-attached parcel



In [ ]:

    
%%time

q = """SELECT count(1) as count, extract(year from a.alarm) as year, COALESCE(y.risk_category, 'N/A') as risk_level
FROM buildingfires a
LEFT JOIN
    (SELECT state, fdid, inc_date, inc_no, exp_no, x.parcel_id, x.risk_category
        FROM ( SELECT *
            FROM incidentaddress a
            LEFT JOIN parcel_risk_category_local using (parcel_id)
        ) AS x
    ) AS y
USING (state, fdid, inc_date, inc_no, exp_no)
WHERE a.state = %(state)s AND a.fdid = %(fdid)s AND extract(year FROM a.inc_date) IN %(years)s
GROUP BY y.risk_category, extract(year from a.alarm)
ORDER BY extract(year from a.alarm) DESC"""

display(pd.read_sql_query(q, connections['nfirs'], params=params))



In [ ]:

    
%%time

q = """select extract(year from inc_date) as year, count(*) from buildingfires where fdid=%(fdid)s and state=%(state)s and extract(year from inc_date) in %(years)s group by year order by year desc;"""
    
print pd.read_sql_query(q, connections['nfirs'], params=params)

Civilian casualities segmented by structure hazard level vs existing NFIRS statistics



In [ ]:

    
%%time

print 'Using closest parcel'

q = """select count(1), extract(year from inc_date) as year, COALESCE(b.risk_category, 'N/A') as risk_category
FROM civiliancasualty a left join (SELECT
  *
FROM (
  SELECT state, 
    fdid,
    inc_date,
    inc_no,
    exp_no,
    geom,
    b.parcel_id,
    b.wkb_geometry,
    b.risk_category,
    ROW_NUMBER() OVER (PARTITION BY state, fdid, inc_date, inc_no, exp_no, geom ORDER BY st_distance(st_centroid(b.wkb_geometry), a.geom)) AS r
  FROM (select * from incidentaddress where state=%(state)s and fdid=%(fdid)s) a
     left join parcel_risk_category_local b on a.geom && b.wkb_geometry
     ) x
WHERE x.r = 1) b using (state, inc_date, exp_no, fdid, inc_no) where state=%(state)s and fdid=%(fdid)s and extract(year from inc_date) in %(years)s
GROUP by extract(year from inc_date), COALESCE(b.risk_category, 'N/A')
ORDER BY extract(year from inc_date) DESC"""

print pd.read_sql_query(q, connections['nfirs'], params=params)



In [ ]:

    
%%time

q = """SELECT count(1) as count, extract(year from b.inc_date) as year, COALESCE(y.risk_category, 'N/A') as risk_level
FROM fireincident b
LEFT JOIN
    (SELECT state, fdid, inc_date, inc_no, exp_no, x.parcel_id, x.risk_category
        FROM (SELECT *
            FROM incidentaddress a
            LEFT JOIN parcel_risk_category_local using (parcel_id)
        ) AS x
    ) AS y
USING (state, fdid, inc_date, inc_no, exp_no)
WHERE b.state = %(state)s AND b.fdid = %(fdid)s AND extract(year FROM b.inc_date) IN %(years)s
GROUP BY y.risk_category, extract(year FROM b.inc_date)
ORDER BY extract(year FROM b.inc_date) DESC"""

display(pd.read_sql_query(q, connections['nfirs'], params=params))

Structure fire calls



In [ ]:

    
%%time

q = """SELECT count(1) as count, extract(year from a.alarm) as year, COALESCE(y.risk_category, 'N/A') as risk_level
FROM buildingfires a
LEFT JOIN
    (SELECT state, fdid, inc_date, inc_no, exp_no, x.parcel_id, x.risk_category
        FROM ( SELECT *
            FROM incidentaddress a
            LEFT JOIN parcel_risk_category_local using (parcel_id)
        ) AS x
    ) AS y
USING (state, fdid, inc_date, inc_no, exp_no)
WHERE a.state = %(state)s AND a.fdid = %(fdid)s AND extract(year FROM a.inc_date) IN %(years)s
GROUP BY y.risk_category, extract(year from a.alarm)
ORDER BY extract(year from a.alarm) DESC"""

display(pd.read_sql_query(q, connections['nfirs'], params=params))



In [ ]:

    
from firecares.tasks.update import update_nfirs_counts

update_nfirs_counts.delay(fd.id)

Firefighter casualty segmented by structure hazard level vs flat NFIRS statistics

Using closest parcel

Using old non risk-aware method

Using parcel_id join to parcel_risk_category_local

Building fires segmented by structure hazard level vs existing NFIRS statistics

Using directly-attached parcel

Civilian casualities segmented by structure hazard level vs existing NFIRS statistics

All fire-related calls

Structure fire calls

Using `parcel_id` join to `parcel_risk_category_local`