class AnnualIndicativeBurnProgram(models.Model):
objectid = models.IntegerField(primary_key=True)
wkb_geometry = models.MultiPolygonField(srid=4326, blank=True, null=True)
region = models.CharField(max_length=35, blank=True)
district = models.CharField(max_length=35, blank=True)
burnid = models.CharField(max_length=30, blank=True)
fin_yr = models.CharField(verbose_name='Fin Year',
max_length=9,
blank=True,
null=True)
location = models.CharField(max_length=254, blank=True)
status = models.CharField(max_length=254, blank=True)
priority = models.DecimalField(max_digits=9,
decimal_places=0,
blank=True,
null=True)
#content = models.CharField(max_length=254, blank=True)
#issues = models.CharField(max_length=254, blank=True)
treatment = models.DecimalField(max_digits=9,
decimal_places=0,
blank=True,
null=True)
#purpose_1 = models.CharField(max_length=254, blank=True)
#program = models.CharField(max_length=254, blank=True)
#acb = models.CharField(max_length=254, blank=True)
trtd_area = models.CharField(max_length=254, blank=True)
#yslb = models.CharField(max_length=254, blank=True)
area_ha = models.DecimalField(max_digits=19,
decimal_places=11,
blank=True,
null=True)
perim_km = models.DecimalField(max_digits=19,
decimal_places=11,
blank=True,
null=True)
longitude = models.DecimalField(max_digits=19,
decimal_places=11,
blank=True,
null=True)
latitude = models.DecimalField(max_digits=19,
decimal_places=11,
blank=True,
null=True)
objects = models.GeoManager()
class Meta:
managed = False
class BurnProgramLink(models.Model):
prescription = models.ForeignKey(Prescription, unique=True)
wkb_geometry = models.MultiPolygonField(srid=4326)
area_ha = models.FloatField()
longitude = models.FloatField()
latitude = models.FloatField()
perim_km = models.FloatField()
trtd_area = models.FloatField()
@classmethod
def populate(cls):
# Links prescriptions to burn program records imported using ogr2ogr
import subprocess
subprocess.check_call([
'ogr2ogr', '-overwrite', '-f', 'PostgreSQL',
"PG:dbname='{NAME}' host='{HOST}' port='{PORT}' user='******' password={PASSWORD}"
.format(**settings.DATABASES["default"]),
settings.ANNUAL_INDIC_PROGRAM_PATH, settings.SHP_LAYER, '-nln',
'review_annualindicativeburnprogram', '-nlt', 'PROMOTE_TO_MULTI',
'-t_srs', 'EPSG:4326', '-lco', 'GEOMETRY_NAME=wkb_geometry'
])
for p in AnnualIndicativeBurnProgram.objects.all():
try:
for prescription in Prescription.objects.filter(
burn_id=p.burnid,
financial_year=p.fin_yr.replace("/", "/20")):
if cls.objects.filter(prescription=prescription).exists():
obj = cls.objects.get(prescription=prescription)
else:
obj = cls(prescription=prescription)
obj.wkb_geometry = p.wkb_geometry
obj.area_ha = p.area_ha
obj.longitude = p.longitude
obj.latitude = p.latitude
obj.perim_km = p.perim_km
obj.trtd_area = p.trtd_area if p.trtd_area and isinstance(
p.trtd_area, float) else 0.0
obj.save()
except:
logger.error(
'ERROR: Assigning AnnulaIndicativeBurnProgram \n{}'.format(
sys.exc_info()))
from django.db import connection
cursor = connection.cursor()
cursor.execute('''
create or replace view review_v_dailyburns as
select
p.burn_id,
to_char(pb.date, 'FMDay, DD Mon YYYY') as burn_target_date,
pb.date as burn_target_date_raw,
case
when string_agg(pb.form_name::text, ', ') = '1, 2' or string_agg(pb.form_name::text, ', ') = '2, 1' then
'Active - Planned Ignitions Today'
when string_agg(pb.form_name::text, ', ') = '2' then
'Active - No Planned Ignitions Today'
when string_agg(pb.form_name::text, ', ') = '1' then
'Planned - No Prior Ignitions'
else
'Error'
end as burn_stat,
case
when pb.location like '%|%' then
case
when p.forest_blocks not like '' then
split_part(pb.location, '|', 1) || ', ' || split_part(pb.location, '|', 2) || 'km ' ||
split_part(pb.location, '|', 3) || ' of '|| split_part(pb.location, '|', 4) ||
' (' || p.forest_blocks || ')'
else
split_part(pb.location, '|', 1) || ', ' || split_part(pb.location, '|', 2) || 'km ' ||
split_part(pb.location, '|', 3) || ' of '|| split_part(pb.location, '|', 4)
end
else
case
when p.forest_blocks not like '' then
pb.location || ' (' || p.forest_blocks || ')'
else
pb.location
end
end as location,
p.forest_blocks,
link.area_ha AS indicative_area,
coalesce(
(select rpb.est_start
from review_prescribedburn rpb
where
rpb.date = pb.date and
rpb.prescription_id::text = pb.prescription_id::text and
rpb.form_name = 1 and
rpb.location = pb.location)::text,
'') AS burn_est_start, -- use time from 268a
coalesce(
(select rpb.longitude
from review_prescribedburn rpb
where
rpb.date = pb.date and
rpb.prescription_id::text = pb.prescription_id::text and
rpb.form_name = 1 and
rpb.location = pb.location),
pb.longitude) AS burn_target_long, -- use longitude from 268a, else 268b
coalesce(
(select rpb.latitude
from review_prescribedburn rpb
where
rpb.date = pb.date and
rpb.prescription_id::text = pb.prescription_id::text and
rpb.form_name = 1 and
rpb.location = pb.location),
pb.latitude) AS burn_target_lat, -- use latitude from 268a, else 268b
coalesce(
cast((select rpb.planned_area
from review_prescribedburn rpb
where
rpb.date = pb.date and
rpb.prescription_id::text = pb.prescription_id::text and
rpb.form_name = 1 and
rpb.location = pb.location) as text),
'') AS burn_planned_area_today, -- use planned_area from 268a
coalesce(
cast((select rpb.planned_distance
from review_prescribedburn rpb
where
rpb.date = pb.date and
rpb.prescription_id::text = pb.prescription_id::text and
rpb.form_name = 1 and
rpb.location = pb.location) as text),
'') AS burn_planned_distance_today, -- use planned_distance from 268a
link.wkb_geometry,
coalesce((SELECT array_to_string(array_agg(pp.name),' , ')
FROM prescription_prescription_purposes ppps JOIN prescription_purpose pp ON ppps.purpose_id = pp.id
WHERE ppps.prescription_id = p.id)
,'') AS burn_purpose
from
(((prescription_prescription p
LEFT JOIN review_prescribedburn pb ON ((p.id = pb.prescription_id)))
LEFT JOIN review_acknowledgement ack ON ((pb.id = ack.burn_id)))
LEFT JOIN review_burnprogramlink link ON ((p.id = link.prescription_id)))
WHERE (
(((ack.acknow_type)::text = 'SDO_A'::text) AND (pb.form_name = 1)) OR -- approved 268a
((((ack.acknow_type)::text = 'SDO_B'::text) AND (pb.form_name = 2)) AND (pb.status = 1))) -- approved active 268b
group by
p.id,p.burn_id, pb.location, p.forest_blocks, burn_target_date, indicative_area,
burn_target_long, burn_target_lat, burn_est_start, link.wkb_geometry,
burn_planned_area_today, burn_planned_distance_today, burn_target_date_raw
ORDER BY p.burn_id, burn_target_date_raw;
create or replace view review_v_todaysburns as select * from review_v_dailyburns where burn_target_date_raw = current_date;
create or replace view review_v_yesterdaysburns as select * from review_v_dailyburns where burn_target_date_raw = current_date - interval '1 day';
CREATE OR REPLACE FUNCTION review_f_lastdaysburns() RETURNS setof review_v_dailyburns AS $$
DECLARE
last_date review_v_dailyburns.burn_target_date_raw%TYPE;
BEGIN
SELECT max(pb.date) INTO last_date
FROM
review_prescribedburn pb LEFT JOIN review_acknowledgement ack ON pb.id = ack.burn_id
WHERE (
(((ack.acknow_type)::text = 'SDO_A'::text) AND (pb.form_name = 1))
OR
((((ack.acknow_type)::text = 'SDO_B'::text) AND (pb.form_name = 2)) AND (pb.status = 1))
)
AND (pb.date < current_date);
IF last_date IS NULL THEN
RETURN QUERY SELECT * FROM review_v_dailyburns WHERE false;
ELSE
RETURN QUERY SELECT * FROM review_v_dailyburns WHERE burn_target_date_raw = last_date;
END IF;
END;
$$ LANGUAGE plpgsql;
create or replace view review_v_lastdaysburns as select * from review_f_lastdaysburns();
''')