def populate_db():
config = ConfigParser.ConfigParser()
config.read(local_settings_path)
db_url = config.get('app:main', 'sqlalchemy.url')
from sqlalchemy import create_engine
engine = create_engine(db_url)
from ..scripts.initializedb import populate_db as populate
populate(engine, drop_all=True)
shape = MultiPolygon([
Polygon([(0, 0), (0, 1), (1, 1), (1, 0), (0, 0)])
])
geometry = from_shape(shape, 4326)
div_level_1 = AdministrativeDivision(**{
'code': 10,
'leveltype_id': 1,
'name': u'Division level 1'
})
div_level_1.geom = geometry
DBSession.add(div_level_1)
div_level_2 = AdministrativeDivision(**{
'code': 20,
'leveltype_id': 2,
'name': u'Division level 2'
})
div_level_2.parent_code = div_level_1.code
div_level_2.geom = geometry
DBSession.add(div_level_2)
shape = MultiPolygon([
Polygon([(0, 0), (0, 1), (.5, 1), (.5, 0), (0, 0)])
])
geometry = from_shape(shape, 4326)
div_level_3_1 = AdministrativeDivision(**{
'code': 30,
'leveltype_id': 3,
'name': u'Division level 3 - 1'
})
div_level_3_1.parent_code = div_level_2.code
div_level_3_1.geom = geometry
div_level_3_1.hazardcategories = []
shape = MultiPolygon([
Polygon([(.5, 0), (.5, 1), (1, 1), (1, 0), (.5, 0)])
])
geometry = from_shape(shape, 4326)
div_level_3_2 = AdministrativeDivision(**{
'code': 31,
'leveltype_id': 3,
'name': u'Division level 3 - 2'
})
div_level_3_2.parent_code = div_level_2.code
div_level_3_2.geom = geometry
div_level_3_2.hazardcategories = []
category_eq_hig = HazardCategory(**{
'general_recommendation': u'General recommendation for EQ HIG',
})
category_eq_hig.hazardtype = DBSession.query(HazardType) \
.filter(HazardType.mnemonic == u'EQ').one()
category_eq_hig.hazardlevel = DBSession.query(HazardLevel) \
.filter(HazardLevel.mnemonic == u'HIG').one()
associate_admindiv_hazardcategory(div_level_3_1, category_eq_hig, u'test')
associate_admindiv_hazardcategory(div_level_3_2, category_eq_hig, u'test')
climate_rec = ClimateChangeRecommendation()
climate_rec.text = u'Climate change recommendation'
climate_rec.administrativedivision = div_level_1
climate_rec.hazardtype = DBSession.query(HazardType) \
.filter(HazardType.mnemonic == u'EQ').one()
DBSession.add(climate_rec)
technical_rec = TechnicalRecommendation(**{
'text': u'Recommendation #1 for earthquake, applied to'
' hazard categories HIG, MED and LOW'
})
association = HazardCategoryTechnicalRecommendationAssociation(order=1)
association.hazardcategory = category_eq_hig
technical_rec.hazardcategory_associations.append(association)
DBSession.add(technical_rec)
technical_rec = TechnicalRecommendation(**{
'text': u'Educational web resources on earthquakes and'
' seismic hazard'
})
association = HazardCategoryTechnicalRecommendationAssociation(order=1)
association.hazardcategory = category_eq_hig
technical_rec.hazardcategory_associations.append(association)
DBSession.add(technical_rec)
category_fl_med = HazardCategory(**{
'general_recommendation': u'General recommendation for FL MED',
})
category_fl_med.hazardtype = DBSession.query(HazardType) \
.filter(HazardType.mnemonic == u'FL').one()
category_fl_med.hazardlevel = DBSession.query(HazardLevel) \
.filter(HazardLevel.mnemonic == u'MED').one()
associate_admindiv_hazardcategory(div_level_3_1, category_fl_med, u'test')
DBSession.add(div_level_3_1)
associate_admindiv_hazardcategory(div_level_3_2, category_fl_med, u'test')
DBSession.add(div_level_3_2)
further_resource = FurtherResource(**{
'text': u'Educational web resources on earthquakes and' +
' seismic hazard',
'url': u'http://earthquake.usgs.gov/learn/?source=sitemap'
})
association = HazardCategoryFurtherResourceAssociation(order=1)
association.hazardcategory = category_eq_hig
further_resource.hazardcategory_associations.append(association)
DBSession.add(further_resource)
# Add further resource for one division only
further_resource = FurtherResource(**{
'text': u'Further resource specific to division level 3 - 2',
'url': u'http://domain.com/the/document/url.txt'
})
association = HazardCategoryFurtherResourceAssociation(order=2)
association.hazardcategory = category_eq_hig
association.administrativedivision = div_level_3_2
further_resource.hazardcategory_associations.append(association)
DBSession.add(further_resource)
def import_recommendations():
engine = engine_from_config(settings, 'sqlalchemy.')
DBSession.configure(bind=engine)
with transaction.manager:
DBSession.query(HazardCategoryTechnicalRecommendationAssociation) \
.delete()
DBSession.query(TechnicalRecommendation).delete()
# First load general recommendations
with open('data/general_recommendations.csv', 'rb') as csvfile:
recommendations = csv.reader(csvfile, delimiter=',')
for row in recommendations:
hazardcategory = DBSession.query(HazardCategory) \
.join(HazardLevel) \
.join(HazardType) \
.filter(HazardLevel.mnemonic == row[1]) \
.filter(HazardType.mnemonic == row[0]) \
.one()
hazardcategory.general_recommendation = row[2]
DBSession.add(hazardcategory)
categories = []
for type in [u'EQ', u'FL', u'CY', u'TS', u'CF', u'VA', u'DG']:
for level in [u'HIG', u'MED', u'LOW', u'VLO']:
hazardcategory = DBSession.query(HazardCategory) \
.join(HazardLevel) \
.join(HazardType) \
.filter(HazardLevel.mnemonic == level) \
.filter(HazardType.mnemonic == type) \
.one()
categories.append(hazardcategory)
# Then technical recommendations
hctra = HazardCategoryTechnicalRecommendationAssociation
with open('data/technical_recommendations.csv', 'rb') as csvfile:
recommendations = csv.reader(csvfile, delimiter=',')
next(recommendations, None) # skip the headers
for row in recommendations:
technical_rec = TechnicalRecommendation(**{
'text': row[0]
})
associations = technical_rec.hazardcategory_associations
# the other columns are hazard category (type / level)
for col_index in range(1, 28):
value = row[col_index]
if value is not '' and value is not 'Y':
association = hctra(order=value)
association.hazardcategory = categories[col_index - 1]
associations.append(association)
DBSession.add(technical_rec)
# Climate change recommendations
DBSession.query(ClimateChangeRecommendation).delete()
# hazard types and corresponding columns
hazard_types = [
(u'FL', 6),
(u'EQ', 7),
(u'CY', 8),
(u'CF', 9),
(u'DG', 10),
(u'TS', 11),
(u'VA', 12),
(u'LS', 13),
]
with open('data/climate_change_recommendations.csv', 'rb') as csvfile:
countries = csv.reader(csvfile, delimiter=',')
next(countries, None) # skip the headers
for row in countries:
division = DBSession.query(AdministrativeDivision) \
.filter(AdministrativeDivision.code == row[1]) \
.one_or_none()
if not division:
continue
for hazard_type, column in hazard_types:
text = row[column]
if text == 'NA':
continue
climate_rec = ClimateChangeRecommendation()
climate_rec.text = row[column]
climate_rec.administrativedivision = division
climate_rec.hazardtype = DBSession.query(HazardType) \
.filter(HazardType.mnemonic == hazard_type).one()
DBSession.add(climate_rec)
