<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
Step1:  People per structure used for sanity check on parcel counts by department
Step3:  Get owned census-tracts for department and count parcels
Step5:  Structure count by hazard category by owned census tract geometries
Step8:  Export of parcels by owned census tracts
<ASSISTANT_TASK:>
Python Code:
q = select fd.id, fd.name, fd.state,
    COALESCE(fd.population, 0) as population,
    sum(rm.structure_count) as structure_count,
    fd.population / sum(rm.structure_count)::float as people_per_structure
from firestation_firedepartment fd
    inner join firestation_firedepartmentriskmodels rm
    on rm.department_id = fd.id
where rm.level != 0
group by fd.id, COALESCE(fd.population, 0)
order by COALESCE(population / sum(rm.structure_count)::float, 0) desc
df = pd.read_sql_query(q, conn)
df.to_csv('/tmp/people_per_structure_by_department.csv')
filtered = df[df['population'] > 100000][:30]
res = tabulate(filtered, headers='keys', tablefmt='pipe')
open('/tmp/outf.md', 'w').write(res)
! cat /tmp/outf.md | pbcopy
display(filtered)
Explanation: People per structure used for sanity check on parcel counts by department
End of explanation
q = SELECT ST_Multi(ST_Union(bg.geom))
        FROM nist.tract_years ty
        INNER JOIN census_block_groups_2010 bg
        ON ty.tr10_fid = ('14000US'::text || "substring"((bg.geoid10)::text, 0, 12))
        WHERE ty.fc_dept_id = %(id)s
        GROUP BY ty.fc_dept_id
geom = pd.read_sql_query(q, nfirs, params={'id': 96649})['st_multi'][0]
display_geom(wkb.loads(geom, hex=True))
Explanation: Get owned census-tracts for department and count parcels
End of explanation
q = select count(1), risk_category
from parcel_risk_category_local p
where ST_Intersects(p.wkb_geometry, ST_SetSRID(%(owned_geom)s::geometry, 4326))
group by risk_category
df = pd.read_sql_query(q, nfirs, params={'owned_geom': geom})
display(df)
Explanation: Structure count by hazard category by owned census tract geometries
End of explanation
q = select parcel_id, risk_category
from parcel_risk_category_local l
where ST_Intersects(l.wkb_geometry, ST_SetSRID(%(owned_geom)s::geometry, 4326))
owned_parcels = pd.read_sql_query(q, nfirs, params={'owned_geom': geom})
import geopandas
q = select p.*, rc.risk_category as hazard_level from parcels p
inner join parcel_risk_category_local rc using (parcel_id)
where p.parcel_id in %(ids)s
res = map(lambda x: x[0], owned_parcels.values)
gdf = geopandas.read_postgis(q, nfirs, geom_col='wkb_geometry', params={'ids': tuple(res)})
gdf.drop('risk_category', 1)
gdf.crs = {'init': 'epsg:4326'}
gdf.to_file('/tmp/tamarac-parcels.shp', driver='ESRI Shapefile')
Explanation: Export of parcels by owned census tracts
End of explanation
<END_TASK>