FireCARES / research

1 stars 1 forks source link

Automated jurisdictional boundary determination #2

Open meilinger opened 5 years ago

meilinger commented 5 years ago

Using a buffered distance from a department's headquarters intersected with administrative/government units, we should be able to find a best-fit government unit geometry for a department's jurisdictional boundary.

meilinger commented 5 years ago
meilinger commented 5 years ago

@tbuffington7

select setseed(0.123123123);
select id, fdid, state, name from firestation_firedepartment
where boundary_verified = false
and geom is null
order by random()
limit 100;

yields department_sample.csv.zip

meilinger commented 5 years ago

@tbuffington7 pulled the whole list and pruned down to only departments w/ 30 or more residential structure fires (over the time span that we have NFIRS data for)...8568 departments match that criteria

select fd.id, fd.fdid, fd.state, fd.name, sum(stat.count) as res_structure_fire_count from firestation_firedepartment fd
inner join firestation_nfirsstatistic stat on fd.id = stat.fire_department_id and stat.level = 0 and stat.metric = 'residential_structure_fires'
where fd.geom is null
and fd.archived = false
and fd.boundary_verified = false
and fd.headquarters_address_id is not null
group by fd.id, fd.fdid, fd.state, fd.name
having sum(stat.count) >= 30
order by sum(stat.count) desc;

departments_to_add_boundary.csv.zip

Let me know if you have any questions!

meilinger commented 5 years ago

Updated to only include statistic from 2008 onward:

department_to_make_boundary.csv.zip