<commit_before>#!/bin/sh

# OVERVIEW:
# The purpose of this collection of scripts is to load the NFIRS yearly data
# into a dump of the existing Firecares Database.  The individual 18 incident types
# (fireincident, hazchem, hazmat, etc) are loaded into temp tables and then
# appended to the master "fireincident", etc tables.  The bulk of this script
# has to do w/ matching addresses in geocoding results to the new incidents in
# the "incidentaddress_${YEAR}" and appending that to the "incidentaddress" table.
# The incident_address_${YEAR}_aa/ab/ac/ad/ae tables contain the geocoding information
# from shapefiles and will be used to augment the incidentaddress_${YEAR}
# table's records with geometries.


# Assumes that the assocated shapefiles are alongside the NFIRS data

DB=nfirs15
YEAR=2015

psql $DB << EOF
create table fireincident_$YEAR as table fireincident with no data;
create table hazchem_$YEAR as table hazchem with no data;
create table hazmat_$YEAR as table hazmat with no data;
create table hazmatequipinvolved_$YEAR as table hazmatequipinvolved with no data;
create table hazmobprop_$YEAR as table hazmobprop with no data;
create table incidentaddress_$YEAR as table incidentaddress with no data;
create table wildlands_$YEAR as table wildlands with no data;
create table arson_$YEAR as table arson with no data;
create table arsonagencyreferal_$YEAR as table arsonagencyreferal with no data;
create table arsonjuvsub_$YEAR as table arsonjuvsub with no data;
create table basicaid_$YEAR as table basicaid with no data;
create table basicincident_$YEAR as table basicincident with no data;
create table civiliancasualty_$YEAR as table civiliancasualty with no data;
create table codelookup_$YEAR as table codelookup with no data;
create table ems_$YEAR as table ems with no data;
create table fdheader_$YEAR as table fdheader with no data;
create table ffcasualty_$YEAR as table ffcasualty with no data;
create table ffequipfail_$YEAR as table ffequipfail with no data;
EOF


# convert from MMDDYYYY to something that Postgres can handle MM/DD/YYYY
LANG=C sed -i.bak 's/\^\([0-9]\{2\}\)\([0-9]\{2\}\)\([0-9]\{4\}\)\^/^"\1\/\2\/\3"^/' fireincident.txt
LANG=C sed -i.bak 's/\^\([0-9]\{2\}\)\([0-9]\{2\}\)\([0-9]\{4\}\)\^/^"\1\/\2\/\3"^/' incidentaddress.txt
LANG=C sed -i.bak 's/\^\([0-9]\{2\}\)\([0-9]\{2\}\)\([0-9]\{4\}\)\^/^"\1\/\2\/\3"^/' arsonjuvsub.txt
LANG=C sed -i.bak 's/\"\([U123]\) -- [^"]*\"/"\1"/' arsonjuvsub.txt
LANG=C sed -i.bak 's/\^\([0-9]\{2\}\)\([0-9]\{2\}\)\([0-9]\{4\}\)\^/^"\1\/\2\/\3"^/' basicincident.txt
# convert from MMDDYYYYHHmm to something that Postgres can handle (MM/DD/YYYY HH:mm), matching the closing ^ causes adjacent fields to NOT be matched
LANG=C sed -i.bak 's/\^\([0-9]\{2\}\)\([0-9]\{2\}\)\([0-9]\{4\}\)\([0-9]\{2\}\)\([0-9]\{2\}\)/^"\1\/\2\/\3 \4:\5"/g' basicincident.txt
LANG=C sed -i.bak 's/\^\([0-9]\{2\}\)\([0-9]\{2\}\)\([0-9]\{4\}\)\^/^"\1\/\2\/\3"^/' civiliancasualty.txt
LANG=C sed -i.bak 's/\^\([0-9]\{2\}\)\([0-9]\{2\}\)\([0-9]\{4\}\)\^/^"\1\/\2\/\3"^/' ffcasualty.txt
# found one \x00 byte, vim => :%s/[\x00]//g OR the tr below
cp incidentaddress.txt incidentaddress.prereplace.txt && LANG=C tr < incidentaddress.txt -d '\000' > incidentaddress2.txt && mv incidentaddress2.txt incidentaddress.txt
# fdheader riddled w/ \x00 bytes, vim => :%s/[\x00]//g OR the tr below
cp fdheader.txt fdheader.prereplace.txt && LANG=C tr < fdheader.txt -d '\000' > fdheader2.txt && mv fdheader2.txt fdheader.txt

# looks like the dumps are ASCII, but have some utf-8 encoded characters, force LATIN1 to load w/o having automagic UTF-8 decoding issues
psql $DB << EOF
set client_encoding = 'LATIN1';
copy fireincident_$YEAR from '`pwd`/fireincident.txt' CSV DELIMITER '^' HEADER;
copy hazchem_$YEAR from '`pwd`/hazchem.txt' CSV DELIMITER '^' HEADER;
copy hazmat_$YEAR from '`pwd`/hazmat.txt' CSV DELIMITER '^' HEADER;
copy hazmatequipinvolved_$YEAR from '`pwd`/hazmatequipinvolved.txt' CSV DELIMITER '^' HEADER;
copy hazmobprop_$YEAR from '`pwd`/hazmobprop.txt' CSV DELIMITER '^' HEADER;
copy incidentaddress_$YEAR (state, fdid, inc_date, inc_no, exp_no, loc_type, num_mile, street_pre, streetname, streettype, streetsuf, apt_no, city, state_id, zip5, zip4, x_street) from '`pwd`/incidentaddress.txt' CSV DELIMITER '^' HEADER;
copy wildlands_$YEAR from '`pwd`/wildlands.txt' CSV DELIMITER '^' HEADER;
copy arson_$YEAR from '`pwd`/arson.txt' CSV DELIMITER '^' HEADER;
copy arsonagencyreferal_$YEAR from '`pwd`/arsonagencyreferal.txt' CSV DELIMITER '^' HEADER;
copy arsonjuvsub_$YEAR from '`pwd`/arsonjuvsub.txt' CSV DELIMITER '^' HEADER;
copy basicaid_$YEAR from '`pwd`/basicaid.txt' CSV DELIMITER '^' HEADER;
copy basicincident_$YEAR from '`pwd`/basicincident.txt' CSV DELIMITER '^' HEADER;
copy civiliancasualty_$YEAR from '`pwd`/civiliancasualty.txt' CSV DELIMITER '^' HEADER;
copy codelookup_$YEAR from '`pwd`/codelookup.txt' CSV DELIMITER '^' HEADER;
copy ems_$YEAR from '`pwd`/ems.txt' CSV DELIMITER '^' HEADER;
copy fdheader_$YEAR from '`pwd`/fdheader.txt' CSV DELIMITER '^' HEADER;
copy ffcasualty_$YEAR (state, fdid, inc_date, inc_no, exp_no, ff_seq_no, version, gender, career, age, inj_date, responses, assignment, phys_cond, severity, taken_to, activity, symptom, pabi, cause, factor, object, wio, relation, story, location, vehicle, prot_eqp) from '`pwd`/ffcasualty.txt' CSV DELIMITER '^' HEADER;
copy ffequipfail_$YEAR from '`pwd`/ffequipfail.txt' CSV DELIMITER '^' HEADER;

EOF


alter table geocoding_2015.geocoded_addresses alter column zip5 type character varying(5);
update geocoding_2015.geocoded_addresses set zip5 = lpad(zip5, 5, '0');
update geocoding_2015.geocoded_addresses set apt_no = '' where apt_no is null;


create table public.arson_bk as select * from public.arson;
create table public.arsonagencyreferal_bk as select * from public.arsonagencyreferal;
create table public.arsonjuvsub_bk as select * from public.arsonjuvsub;
create table public.basicaid_bk as select * from public.basicaid;
create table public.incidentaddress_bk as select * from public.incidentaddress;
create table public.fireincident_bk as select * from public.fireincident;
create table public.ems_bk as select * from public.ems;
create table public.wildlands_bk as select * from public.wildlands;
create table public.civiliancasualty_bk as select * from public.civiliancasualty;
create table public.fdheader_bk as select * from public.fdheader;
create table public.hazmobprop_bk as select * from public.hazmobprop;
create table public.ffcasualty_bk as select * from public.ffcasualty;
create table public.ffequipfail_bk as select * from public.ffequipfail;
create table public.basicincident_bk as select * from public.basicincident;
create table public.hazchem_bk as select * from public.hazchem;
create table public.hazmat_bk as select * from public.hazmat;
create table public.hazmatequipinvolved_bk as select * from public.hazmatequipinvolved;


-- 486689 pre - 526386 post
INSERT INTO public.arson_bk(
            state, fdid, inc_date, inc_no, exp_no, version, case_stat, avail_mfi,
            mot_facts1, mot_facts2, mot_facts3, grp_invol1, grp_invol2, grp_invol3,
            entry_meth, ext_fire, devi_cont, devi_ignit, devi_fuel, inv_info1,
            inv_info2, inv_info3, inv_info4, inv_info5, inv_info6, inv_info7,
            inv_info8, prop_owner, init_ob1, init_ob2, init_ob3, init_ob4,
            init_ob5, init_ob6, init_ob7, init_ob8, lab_used1, lab_used2,
            lab_used3, lab_used4, lab_used5, lab_used6)
    (SELECT state, fdid, inc_date, inc_no, exp_no, version, case_stat, avail_mfi,
       mot_facts1, mot_facts2, mot_facts3, grp_invol1, grp_invol2, grp_invol3,
       entry_meth, ext_fire, devi_cont, devi_ignit, devi_fuel, inv_info1,
       inv_info2, inv_info3, inv_info4, inv_info5, inv_info6, inv_info7,<commit_msg>arson<commit_after>#!/bin/sh

# OVERVIEW:
# The purpose of this collection of scripts is to load the NFIRS yearly data
# into a dump of the existing Firecares Database.  The individual 18 incident types
# (fireincident, hazchem, hazmat, etc) are loaded into temp tables and then
# appended to the master "fireincident", etc tables.  The bulk of this script
# has to do w/ matching addresses in geocoding results to the new incidents in
# the "incidentaddress_${YEAR}" and appending that to the "incidentaddress" table.
# The incident_address_${YEAR}_aa/ab/ac/ad/ae tables contain the geocoding information
# from shapefiles and will be used to augment the incidentaddress_${YEAR}
# table's records with geometries.


# Assumes that the assocated shapefiles are alongside the NFIRS data

DB=nfirs15
YEAR=2015

psql $DB << EOF
create table fireincident_$YEAR as table fireincident with no data;
create table hazchem_$YEAR as table hazchem with no data;
create table hazmat_$YEAR as table hazmat with no data;
create table hazmatequipinvolved_$YEAR as table hazmatequipinvolved with no data;
create table hazmobprop_$YEAR as table hazmobprop with no data;
create table incidentaddress_$YEAR as table incidentaddress with no data;
create table wildlands_$YEAR as table wildlands with no data;
create table arson_$YEAR as table arson with no data;
create table arsonagencyreferal_$YEAR as table arsonagencyreferal with no data;
create table arsonjuvsub_$YEAR as table arsonjuvsub with no data;
create table basicaid_$YEAR as table basicaid with no data;
create table basicincident_$YEAR as table basicincident with no data;
create table civiliancasualty_$YEAR as table civiliancasualty with no data;
create table codelookup_$YEAR as table codelookup with no data;
create table ems_$YEAR as table ems with no data;
create table fdheader_$YEAR as table fdheader with no data;
create table ffcasualty_$YEAR as table ffcasualty with no data;
create table ffequipfail_$YEAR as table ffequipfail with no data;
EOF


# convert from MMDDYYYY to something that Postgres can handle MM/DD/YYYY
LANG=C sed -i.bak 's/\^\([0-9]\{2\}\)\([0-9]\{2\}\)\([0-9]\{4\}\)\^/^"\1\/\2\/\3"^/' fireincident.txt
LANG=C sed -i.bak 's/\^\([0-9]\{2\}\)\([0-9]\{2\}\)\([0-9]\{4\}\)\^/^"\1\/\2\/\3"^/' incidentaddress.txt
LANG=C sed -i.bak 's/\^\([0-9]\{2\}\)\([0-9]\{2\}\)\([0-9]\{4\}\)\^/^"\1\/\2\/\3"^/' arsonjuvsub.txt
LANG=C sed -i.bak 's/\"\([U123]\) -- [^"]*\"/"\1"/' arsonjuvsub.txt
LANG=C sed -i.bak 's/\^\([0-9]\{2\}\)\([0-9]\{2\}\)\([0-9]\{4\}\)\^/^"\1\/\2\/\3"^/' basicincident.txt
# convert from MMDDYYYYHHmm to something that Postgres can handle (MM/DD/YYYY HH:mm), matching the closing ^ causes adjacent fields to NOT be matched
LANG=C sed -i.bak 's/\^\([0-9]\{2\}\)\([0-9]\{2\}\)\([0-9]\{4\}\)\([0-9]\{2\}\)\([0-9]\{2\}\)/^"\1\/\2\/\3 \4:\5"/g' basicincident.txt
LANG=C sed -i.bak 's/\^\([0-9]\{2\}\)\([0-9]\{2\}\)\([0-9]\{4\}\)\^/^"\1\/\2\/\3"^/' civiliancasualty.txt
LANG=C sed -i.bak 's/\^\([0-9]\{2\}\)\([0-9]\{2\}\)\([0-9]\{4\}\)\^/^"\1\/\2\/\3"^/' ffcasualty.txt
# found one \x00 byte, vim => :%s/[\x00]//g OR the tr below
cp incidentaddress.txt incidentaddress.prereplace.txt && LANG=C tr < incidentaddress.txt -d '\000' > incidentaddress2.txt && mv incidentaddress2.txt incidentaddress.txt
# fdheader riddled w/ \x00 bytes, vim => :%s/[\x00]//g OR the tr below
cp fdheader.txt fdheader.prereplace.txt && LANG=C tr < fdheader.txt -d '\000' > fdheader2.txt && mv fdheader2.txt fdheader.txt

# looks like the dumps are ASCII, but have some utf-8 encoded characters, force LATIN1 to load w/o having automagic UTF-8 decoding issues
psql $DB << EOF
set client_encoding = 'LATIN1';
copy fireincident_$YEAR from '`pwd`/fireincident.txt' CSV DELIMITER '^' HEADER;
copy hazchem_$YEAR from '`pwd`/hazchem.txt' CSV DELIMITER '^' HEADER;
copy hazmat_$YEAR from '`pwd`/hazmat.txt' CSV DELIMITER '^' HEADER;
copy hazmatequipinvolved_$YEAR from '`pwd`/hazmatequipinvolved.txt' CSV DELIMITER '^' HEADER;
copy hazmobprop_$YEAR from '`pwd`/hazmobprop.txt' CSV DELIMITER '^' HEADER;
copy incidentaddress_$YEAR (state, fdid, inc_date, inc_no, exp_no, loc_type, num_mile, street_pre, streetname, streettype, streetsuf, apt_no, city, state_id, zip5, zip4, x_street) from '`pwd`/incidentaddress.txt' CSV DELIMITER '^' HEADER;
copy wildlands_$YEAR from '`pwd`/wildlands.txt' CSV DELIMITER '^' HEADER;
copy arson_$YEAR from '`pwd`/arson.txt' CSV DELIMITER '^' HEADER;
copy arsonagencyreferal_$YEAR from '`pwd`/arsonagencyreferal.txt' CSV DELIMITER '^' HEADER;
copy arsonjuvsub_$YEAR from '`pwd`/arsonjuvsub.txt' CSV DELIMITER '^' HEADER;
copy basicaid_$YEAR from '`pwd`/basicaid.txt' CSV DELIMITER '^' HEADER;
copy basicincident_$YEAR from '`pwd`/basicincident.txt' CSV DELIMITER '^' HEADER;
copy civiliancasualty_$YEAR from '`pwd`/civiliancasualty.txt' CSV DELIMITER '^' HEADER;
copy codelookup_$YEAR from '`pwd`/codelookup.txt' CSV DELIMITER '^' HEADER;
copy ems_$YEAR from '`pwd`/ems.txt' CSV DELIMITER '^' HEADER;
copy fdheader_$YEAR from '`pwd`/fdheader.txt' CSV DELIMITER '^' HEADER;
copy ffcasualty_$YEAR (state, fdid, inc_date, inc_no, exp_no, ff_seq_no, version, gender, career, age, inj_date, responses, assignment, phys_cond, severity, taken_to, activity, symptom, pabi, cause, factor, object, wio, relation, story, location, vehicle, prot_eqp) from '`pwd`/ffcasualty.txt' CSV DELIMITER '^' HEADER;
copy ffequipfail_$YEAR from '`pwd`/ffequipfail.txt' CSV DELIMITER '^' HEADER;

EOF


alter table geocoding_2015.geocoded_addresses alter column zip5 type character varying(5);
update geocoding_2015.geocoded_addresses set zip5 = lpad(zip5, 5, '0');
update geocoding_2015.geocoded_addresses set apt_no = '' where apt_no is null;


create table public.arson_bk as select * from public.arson;
create table public.arsonagencyreferal_bk as select * from public.arsonagencyreferal;
create table public.arsonjuvsub_bk as select * from public.arsonjuvsub;
create table public.basicaid_bk as select * from public.basicaid;
create table public.incidentaddress_bk as select * from public.incidentaddress;
create table public.fireincident_bk as select * from public.fireincident;
create table public.ems_bk as select * from public.ems;
create table public.wildlands_bk as select * from public.wildlands;
create table public.civiliancasualty_bk as select * from public.civiliancasualty;
create table public.fdheader_bk as select * from public.fdheader;
create table public.hazmobprop_bk as select * from public.hazmobprop;
create table public.ffcasualty_bk as select * from public.ffcasualty;
create table public.ffequipfail_bk as select * from public.ffequipfail;
create table public.basicincident_bk as select * from public.basicincident;
create table public.hazchem_bk as select * from public.hazchem;
create table public.hazmat_bk as select * from public.hazmat;
create table public.hazmatequipinvolved_bk as select * from public.hazmatequipinvolved;


-- 605099 pre - 643442 post
INSERT INTO public.arson_bk(
            state, fdid, inc_date, inc_no, exp_no, version, case_stat, avail_mfi,
            mot_facts1, mot_facts2, mot_facts3, grp_invol1, grp_invol2, grp_invol3,
            entry_meth, ext_fire, devi_cont, devi_ignit, devi_fuel, inv_info1,
            inv_info2, inv_info3, inv_info4, inv_info5, inv_info6, inv_info7,
            inv_info8, prop_owner, init_ob1, init_ob2, init_ob3, init_ob4,
            init_ob5, init_ob6, init_ob7, init_ob8, lab_used1, lab_used2,
            lab_used3, lab_used4, lab_used5, lab_used6)
    (SELECT state, fdid, inc_date, inc_no, exp_no, version, case_stat, avail_mfi,
       mot_facts1, mot_facts2, mot_facts3, grp_invol1, grp_invol2, grp_invol3,
       entry_meth, ext_fire, devi_cont, devi_ignit, devi_fuel, inv_info1,
       inv_info2, inv_info3, inv_info4, inv_info5, inv_info6, inv_info7,
{'fraction_non_alphanumeric': 0.1322663252240717, 'fraction_numerical': 0.025480153649167735, 'mean_word_length': 4.719882826803369, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '130358', 'n_tokens_mistral': 6122, 'n_tokens_neox': 5720, 'n_words': 1621}