def get_ems_to_import(feed_type='rapid', data_from_feed=None):
"""
Determine which events are updates and returns list of id to be imported
"""
if not data_from_feed:
data_from_feed = parse_feed(feed_type)
if data_from_feed:
#initialise db connection
db = DbUtils()
conn = db.get_db_conn()
curs = conn.cursor()
select_template = "SELECT asset_id FROM flows WHERE name = 'ems'" # AND asset_id IN ('{}')".format(ems_string)
curs.execute(select_template)
already_imported = []
while True:
row = curs.fetchone()
if row == None:
break
already_imported.append(str(row[0]))
conn.close()
tags_from_feed = [d['copernicus_id'] for d in data_from_feed]
#check if Europe
countries_needed = AdministrativeDivision.objects.filter(
parent__code='EU').values_list('name', flat=True)
tags_filtered = [
d['copernicus_id'] for d in data_from_feed
if d['country'] in countries_needed
]
return list(set(tags_filtered) - set(already_imported))
return []
def insert_attributes_row(self, params):
db_values = {
#'table': table_name, # From rp.layer
'the_geom': geos.fromstr(params['adm_div'].geom, srid=params['adm_div'].srid),
'dim1': params['x'].value,
'dim1_order': params['x'].order,
'dim2': params['y'].value,
'dim2_order': params['y'].order,
'dim3': None,
'dim4': None,
'dim5': None,
'risk_analysis_id': params['risk'].id,
'risk_analysis': params['risk'].name,
'hazard_type': params['risk'].hazard_type.mnemonic,
'adm_name': params['adm_div'].name.encode('utf-8').replace("'", "''"),
'adm_code': params['adm_div'].code,
'region': params['region'].name,
'adm_level': params['adm_div'].level,
'parent_adm_code': params['adm_div'].parent.code,
'event_id': params['event'].id,
'begin_date': params['event'].begin_date,
'end_date': params['event'].end_date,
'value': params['attribute_value']
}
db = DbUtils()
db.insert_db(params['conn'], db_values, params['first_call'])
if params['create_django_association']:
risk_adm, created = RiskAnalysisAdministrativeDivisionAssociation.objects.get_or_create(riskanalysis=params['risk'], administrativedivision=params['adm_div'])
def insert_aggregate_values(conn, sample_adm_div = None, adm_level_precision = 2):
region_adm_div = None
if sample_adm_div:
region_adm_div = [adm for adm in sample_adm_div.get_parents_chain() if adm.level == 0]
else:
try:
region_adm_div = [AdministrativeDivision.objects.get(name__icontains='Europe', level=0)]
except:
pass
if region_adm_div:
db = DbUtils()
adm_to_process = region_adm_div
if adm_level_precision == 2:
adm_to_process.append(AdministrativeDivision.objects.filter(parent=region_adm_div[0], level=1))
params = { 'adm_to_process': adm_to_process , 'risk_analysis_id': risk.id }
db.insert_aggregate_values(conn, params)
risk_adm, created = RiskAnalysisAdministrativeDivisionAssociation.objects.get_or_create(riskanalysis=risk, administrativedivision=region_adm_div[0])
def set_tags_imported(emergency_tags):
"""
Update flows table in Geoserver DB
"""
#initialise db connection
db = DbUtils()
conn = db.get_db_conn()
curs = conn.cursor()
insert_template = """INSERT INTO flows (name, asset_id) VALUES ('ems', '{asset_id}')
ON CONFLICT (name, asset_id) DO UPDATE
SET timestamp = now();"""
try:
for asset_id in emergency_tags:
parameters = {'asset_id': asset_id}
curs.execute(insert_template.format(**parameters))
conn.commit()
except Exception, e:
try:
conn.rollback()
except:
pass
raise CommandError(e)
def shp2postgis(directory, table_name):
DB = DbUtils()
connection = DB.get_db_conn()
cursor = connection.cursor()
files = os.listdir(directory)
shp = open('%s/%s' % (directory, [s for s in files if ".shp" in s][0]), "r")
dbf = open('%s/%s' % (directory, [s for s in files if ".dbf" in s][0]), "r")
sf = shapefile.Reader(shp=shp, dbf=dbf)
fields = sf.fields
names = [field[0] for field in fields]
if names:
if len(names) > 1:
names_str = ' text, '.join(names[1:])
else:
names_str = 'placeholder'
names.append(names_str)
cursor.execute('DROP TABLE IF EXISTS %s;' % table_name)
cursor.execute('CREATE TABLE %s (id serial, the_geom geometry);' % (table_name))
cursor.execute('CREATE INDEX %s_gix on %s USING GIST (the_geom)' % (table_name, table_name))
connection.commit()
shape_records = sf.shapeRecords()
queries = process_shapes(shape_records, table_name, names)
for q in queries:
cursor.execute(q)
connection.commit()
#cursor.execute('UPDATE %s SET the_geom=ST_makevalid(the_geom) WHERE NOT ST_isValid(the_geom);' % table_name)
cursor.execute('update %s set the_geom = st_multi(st_collectionextract(st_makevalid(the_geom),3)) where st_isvalid(the_geom) = false;' % table_name)
connection.commit()
shp.close()
dbf.close()
cursor.close()
connection.close()
def handle(self, **options):
db = DbUtils()
conn = db.get_db_conn()
curs = conn.cursor()
select_from_gsdb = """select ev.event_id as event_id, array_agg(distinct(ra.name)) as ra
from events ev
left join risk_dimensions rd on rd.event_id = ev.event_id
left join risk_analysis ra on ra.id = rd.risk_analysis_id
group by ev.event_id"""
replace_pattern = r'\{(w+),(w+)\}'
curs.execute(select_from_gsdb)
rows = []
while True:
row = curs.fetchone()
if row == None:
break
#temp = re.sub(replace_pattern, r"\1','\2", row[1])
#temp = row[1].replace('{', '')
#temp = temp.replace('}', '')
#print('current row: {}'.format(row[0], temp))
#adjusted_row = (row[0], temp.split(','))
print('current row: {}'.format(row))
rows.append(row)
conn.close()
for r in rows:
try:
event = Event.objects.get(event_id=r[0])
except Event.DoesNotExist:
pass
if event:
for risk in RiskAnalysis.objects.filter(name__in=r[1]):
assoc, created = EventRiskAnalysisAssociation.objects.get_or_create(
event=event, risk=risk)
print('created assoc {}'.format(assoc))
def sync_geodb(self, queryset):
db = DbUtils()
conn = db.get_db_conn()
rows_updated = 0
if queryset:
try:
for event in queryset:
code = event.code
if not code:
try:
country = AdministrativeDivision.objects.get(code=event.iso2, level=1)
except AdministrativeDivision.DoesNotExist:
pass
code, duplicates = Event.generate_code(event.hazard_type, country, event.begin_date, event.region)
if event.state != 'ready':
try:
Event.objects.filter(pk=event.pk).update(state='ready', code=str(code))
except:
pass
event.refresh_from_db()
event_dict = {
'event_id': event.id,
'begin_date': event.begin_date,
'end_date': event.end_date,
'state': event.state
}
db.insert_event(conn, event_dict)
rows_updated += 1
conn.commit()
except Exception, e:
try:
conn.rollback()
except:
pass
#traceback.print_exc()
raise CommandError(e)
finally:
def import_events(data_from_feed, tolerance=0.0001):
"""
For every shapefile in _merged folder, creates or updates event in Django DB and Geoserver DB.
The geometry inserted in Geoserver DB is the union of all features found in layer.
"""
db = DbUtils()
conn = db.get_db_conn()
curs = conn.cursor()
allowed_extensions = [".shp"]
try:
for root, dirs, files in os.walk(SHAPEFILES_BASE_DIR):
print('shapefiles base dir: {}'.format(SHAPEFILES_BASE_DIR))
for d in dirs:
if d.endswith('_merged'):
print('found merged folder')
for f in os.listdir(os.path.join(SHAPEFILES_BASE_DIR, d)):
if f.endswith(tuple(allowed_extensions)):
print('found shapefiles: {}'.format(f))
'''try:
filepath = os.path.join(SHAPEFILES_BASE_DIR, d, f)
ds = DataSource(filepath)
except GDALException, e:
traceback.print_exc()
break
for layer in ds:
count = 0
polygon_union = None
for feat in layer:
print('processing feature {}'.format(count))
# Simplify the Geometry
geom = geos.fromstr(feat.geom.wkt, srid=4326)
if tolerance > 0:
geom = geom.simplify(tolerance, preserve_topology=True)
# Generalize to 'Multiploygon'
if isinstance(geom, geos.Polygon):
geom = geos.MultiPolygon(geom)
if count == 0:
polygon_union = geom.buffer(0)
else:
polygon_union = polygon_union.union(geom.buffer(0))
count += 1
try:
print('re-extracting geom from union polygon')
polygon_union = geos.fromstr(polygon_union.wkt, srid=4326)
except:
break'''
ems = d.split('_')[0]
select_template = "SELECT ST_AsText(ST_Union(ARRAY(SELECT ST_Buffer(the_geom, 1e-5) FROM {})))".format(
ems)
row = None
try:
curs.execute(select_template)
row = curs.fetchone()
except:
pass
#set default multipolygon
ext_coords = ((0, 0), (0, 1), (1, 1), (1, 0), (0,
0))
int_coords = ((0.4, 0.4), (0.4, 0.6), (0.6, 0.6),
(0.6, 0.4), (0.4, 0.4))
polygon_union = geos.MultiPolygon(geos.Polygon(
ext_coords, int_coords),
srid=4326)
if row:
polygon_union = geos.fromstr(row[0], srid=4326)
# Update event in Django
event_from_feed = get_event_from_feed(
data_from_feed, ems)
new_event = generate_event_from_feed(
event_from_feed, polygon_union)
if new_event:
params = {
'geom': polygon_union,
'event_id': new_event.event_id,
'begin_date': new_event.begin_date,
'end_date': new_event.end_date
}
update_template = """INSERT INTO events (the_geom, event_id, begin_date, end_date)
SELECT
'{geom}', '{event_id}', '{begin_date}', '{end_date}'
ON CONFLICT (event_id)
DO UPDATE SET the_geom = excluded.the_geom;"""
print update_template.format(**params)
curs.execute(update_template.format(**params))
archive_path = os.path.join(SHAPEFILES_BASE_DIR, d,
'archive')
if not os.path.exists(archive_path):
os.makedirs(archive_path)
for f2 in os.listdir(os.path.join(SHAPEFILES_BASE_DIR, d)):
filepath = os.path.join(SHAPEFILES_BASE_DIR, d, f2)
if os.path.isfile(filepath):
print('moving file {} to {}'.format(
f2, archive_path))
os.rename(filepath, os.path.join(archive_path, f2))
conn.commit()
except Exception:
try:
conn.rollback()
except:
pass
traceback.print_exc()
finally:
conn.close()
def handle(self, **options):
db = DbUtils()
conn = db.get_db_conn()
basedir = "/home/geonode/import_data/emm"
allowed_extensions = [".json", ".geojson"]
default_region = Region.objects.get(name='Europe')
default_adm_region = AdministrativeDivision.objects.get(code='EU')
eh = EventHelper()
data_provider_mappings = DataProviderMappings.objects.filter(data_provider__name='EMM')
try:
for file in os.listdir(basedir):
if file.endswith(tuple(allowed_extensions)):
print 'opening file {}'.format(file)
with open(os.path.join(basedir, file), "r") as f:
parser = ijson.parse(f)
event_obj = {}
data_attributes = []
index = 0
for prefix, event, value in parser:
if data_provider_mappings:
for m in data_provider_mappings:
if prefix.endswith(m.provider_value) and 'hazard_type' in event_obj:
#print 'reading property {}'.format(prefix)
#print 'event obj {}'.format(event_obj)
index = len(data_attributes)-1
data_attributes[index]['dim1'] = m.rdh_value
data_attributes[index][m.rdh_value] = value
data_attributes[index]['risk_analysis'] = m.get_risk_analysis(default_region, event_obj['hazard_type'])
if (prefix, event) == ('data.item', 'start_map'):
print 'start map data'
country = None
event_obj = {}
data_attributes.append({})
elif prefix.endswith('description'):
event_obj['notes'] = value
elif prefix.endswith('pubDate'):
try:
begin_date = parse(value)
end_date = begin_date
year = begin_date.year
except:
year = value[:4]
begin_date = datetime.datetime(year, 1, 1)
end_date = begin_date
event_obj['begin_date'] = begin_date
event_obj['end_date'] = end_date
event_obj['year'] = year
elif prefix.endswith('type'):
resolved_hazard = self.resolve_hazard_type(value)
if resolved_hazard:
event_obj['hazard_type'] = resolved_hazard
else:
continue
elif prefix.endswith('locationLat'):
event_obj['lat'] = value
elif prefix.endswith('locationLon'):
event_obj['lon'] = value
elif (prefix, event) == ('data.item', 'end_map'):
if 'hazard_type' in event_obj:
#print 'event_obj {}'.format(event_obj)
event_obj['region'] = default_region
if 'lat' in event_obj and 'lon' in event_obj:
coordinates = {'lon': float(event_obj['lon']), 'lat': float(event_obj['lat'])}
country_from_intersection = self.get_adm_from_coordinates(coordinates, default_adm_region)
if not country_from_intersection:
data_attributes.pop()
continue
data_attributes[index]['adm_div'] = country_from_intersection
event_obj['iso2'] = country_from_intersection.code
nuts_from_intersection = self.get_adm_from_coordinates(coordinates, country_from_intersection)
if nuts_from_intersection:
event_obj['nuts3'] = nuts_from_intersection.code
data_attributes[index]['adm_div'] = nuts_from_intersection
del event_obj['lat']
del event_obj['lon']
duplicates = Event.find_matches(event_obj)
if not duplicates:
#event_obj['event_id'] = Event.generate_event_id(event_obj['hazard_type'], event_obj['iso2'], event_obj['begin_date'], default_region)
event_obj['sources'] = 'EMM'
event_obj['cause'] = ''
event_obj['state'] = 'draft'
event = Event(**event_obj)
print 'saving event object'
event.save()
event.refresh_from_db()
print event
eh.set_event(event)
eh.set_adm_associations()
#insert event into Geoserver DB
event_obj['event_id'] = event.id
db.insert_event(conn, event_obj)
#insert risk analysis values into Geoserver DB
if data_attributes:
data_attributes[index]['event'] = event
else:
data_attributes.pop()
else:
data_attributes.pop()
os.rename(os.path.join(basedir, file), '{}/archive/{}'.format(basedir, file))
print 'saving event attributes'
print data_attributes
eh.insert_event_attributes(data_attributes, conn)
conn.commit()
except Exception:
try:
conn.rollback()
except:
pass
traceback.print_exc()
finally:
conn.close()