In [2]:
import psycopg2
from nfirs_transaction_parser.settings import CONFIG
# Override DB_STRING to production
DB_STRING = 'postgresql://firecares:password@localhost/nfirs'
CONFIG['sqlalchemy.url'] = DB_STRING
conn = psycopg2.connect(DB_STRING)
from nfirs_transaction_parser.core.parser import IncidentFile
from nfirs_transaction_parser.core.loader import DBLoader
# List of files to be processed
# eg. ['newdep/data.inc.txt', 'newdep/data2.inc.txt',']
FILES = []
# FDID of the department to update
FDID = "66003"
STATE = 'OK'
# Load files to DB
print "Parsing files"
incidents = 0
for item in FILES:
if item:
inc = IncidentFile.load(open(item))
incidents += len(inc.incidents)
DBLoader(inc).load()
print "Incidents loaded: {}".format(incidents)
if incidents:
# Update materialized views inside department_nfirs schema
print "Updating department_nfirs materialized views"
with conn.cursor() as cursor:
cursor.execute("REFRESH MATERIALIZED VIEW department_nfirs.buildingfires")
conn.commit()
# Update materialized views in the public schema
print "Updating public materialized views"
with conn.cursor() as cursor:
cursor.execute("REFRESH MATERIALIZED VIEW joint_buildingfires")
cursor.execute("REFRESH MATERIALIZED VIEW joint_incidentaddress")
cursor.execute("REFRESH MATERIALIZED VIEW joint_fireincident")
cursor.execute("REFRESH MATERIALIZED VIEW joint_ffcasualty")
cursor.execute("REFRESH MATERIALIZED VIEW joint_civiliancasualty")
conn.commit()
conn.close()
In [3]:
import django.conf as conf
setattr(conf.settings, 'CELERY_ALWAYS_EAGER', True)
import django
django.setup()
from firecares.tasks import update
from firecares import celery
from firecares.firestation.models import FireDepartment
fd = FireDepartment.objects.get(fdid=FDID, state=STATE)
# Export buildingfires geom
print "Export buildingfires geom with the command:"
sql = """\COPY (select alarm, a.inc_type, alarms,ff_death, oth_death, ST_X(geom) as x, st_y(geom) as y, COALESCE(y.risk_category, 'Unknown') as risk_category from buildingfires a LEFT JOIN (SELECT state, fdid, inc_date, inc_no, exp_no, x.geom, 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}' and a.fdid = '{fdid}') to PROGRAM 'aws s3 cp - s3://firecares-test/{id}-building-fires.csv --acl=\"public-read\"' DELIMITER ',' CSV HEADER;"""
print sql.format(fdid=FDID, state=STATE, id=fd.id)
update.update_department(fd.id)