def __get_a10_2018_targets_from_view(self, descr_obj, ok_ges_ids, muids):
t = sql2018.t_V_ART10_Targets_2018
descriptor = descr_obj.id
sess = db.session()
q = sess.query(t).filter(t.c.MarineReportingUnit.in_(muids))
ges_filtered = []
for row in q:
ges_comps = getattr(row, 'GESComponents', ())
ges_comps = set([g.strip() for g in ges_comps.split(',')])
if ges_comps.intersection(ok_ges_ids):
ges_filtered.append(row)
if descriptor.startswith('D1.'):
feature_filtered = []
ok_features = set([f.name for f in get_features(descriptor)])
for row in ges_filtered:
feats = set(row.Features.split(','))
if feats.intersection(ok_features):
feature_filtered.append(row)
ges_filtered = feature_filtered
return ges_filtered
def __get_a10_2012_targets(self, ok_ges_ids, muids):
sess = db.session()
T = sql.MSFD10Target
dt = sql.t_MSFD10_DESCrit
D_q = sess.query(dt).join(T)
D_a = aliased(dt, alias=D_q.subquery())
targets = sess\
.query(T)\
.order_by(T.ReportingFeature)\
.filter(T.MarineUnitID.in_(muids))\
.filter(T.Topic == 'EnvironmentalTarget')\
.join(D_a)\
.filter(D_a.c.GESDescriptorsCriteriaIndicators.in_(ok_ges_ids))\
.distinct()\
.all()
res = [
Target(
r.ReportingFeature.replace(' ', '_').lower(),
r.ReportingFeature, r.Description, '2012') for r in targets
]
# sort Targets and make them distinct
res_sorted = sorted(set(res), key=lambda _x: natural_sort_key(_x.id))
return res_sorted
def get_ges_extent_data(self):
sess = db.session()
rep_info_mem = sql.MSFD13ReportingInfoMemberState
rep_info = sql.MSFD13ReportingInfo
info = sql.MSFD13MeasuresInfo
columns = [
rep_info_mem.MemberState, info.InfoType, info.InfoText,
rep_info.ReportingDate
]
country_codes = self.country_code
if not hasattr(country_codes, '__iter__'):
country_codes = [country_codes]
conditions = [
rep_info_mem.MemberState.in_(country_codes),
rep_info.ReportType == 'Exceptions',
info.InfoType == 'RelevantGESDescriptors',
]
res = sess.query(*columns) \
.join(info, rep_info_mem.ReportID == info.ReportID) \
.join(rep_info, rep_info.ID == rep_info_mem.ReportID) \
.filter(*conditions).distinct()
return res
def items(cls, descriptor, muids):
sess = db.session()
N = cls.primary_mapper
A = cls.asses_mapper
AI = cls.asses_ind_mapper
AC = cls.asses_crit_mapper
pk = cls._get_mapper_pk(N)
print 'Started to query data!'
filters = []
filters.append(N.MarineUnitID.in_(muids))
# TODO should we filter by cls.param_topics?
filters.append(N.Topic != 'InfoGaps')
# TODO should we filter by crit_topics?
crit_topics = getattr(cls, 'crit_topics', None)
if crit_topics:
filters.append(A.Topic.in_(crit_topics))
# Acidification does not have '_Assessment' tables
if A:
q = sess.query(N, A, AI, AC)\
.select_from(N)\
.filter(*filters)\
.outerjoin(A)\
.outerjoin(AI)\
.outerjoin(AC)\
.order_by(pk)
print 'Started to setup data!'
print q.count()
for tup in q:
print 'Will yield one item!'
yield cls(descriptor, *tup)
# special case for Acidification
else:
q = sess.query(N) \
.select_from(N) \
.filter(*filters) \
.order_by(pk)
print 'Started to setup data!'
print q.count()
for tup in q:
print 'Will yield one item!'
yield cls(descriptor, *(tup, None, None, None))
def get_monitored_elements(countryids):
MS = sql.MSFD11MONSub
EM = sql.MSFD11Q9aElementMonitored
SP = sql.MSFD11SubProgramme
sess = db.session()
q = sess.query(EM)\
.filter(EM.SubProgramme == SP.ID)\
.filter(SP.ID == MS.SubProgramme)\
.filter(MS.MemberState.in_(countryids))
return q.all()
def __get_a10_2018_targets_from_table(self, ok_ges_ids, muids):
T = sql2018.ART10TargetsTarget
MU = sql2018.ART10TargetsMarineUnit
t_MRU = T.ART10_Targets_MarineUnit
G = sql2018.ART10TargetsTargetGESComponent
sess = db.session()
q = sess \
.query(T) \
.filter(t_MRU.has(MU.MarineReportingUnit.in_(muids))) \
.join(G) \
.filter(G.GESComponent.in_(ok_ges_ids))
res = [x for x in q]
return res
def get_data_from_view_Art8(self):
sess = db.session()
t = sql2018.t_V_ART8_GES_2018
conditions = [
t.c.Region.in_(self._countryregion_folder._subregions),
t.c.GESComponent.in_(self.all_descriptor_ids),
or_(t.c.Element.isnot(None), t.c.Criteria.isnot(None)),
]
# groupby IndicatorCode
q = sess\
.query(t)\
.filter(*conditions)\
.distinct()
res = [row for row in q]
return res
def _related_activities(self, rec):
# return []
sess = db.session() # TODO: concurent ongoing sessions
# will be a problem?
A = self.activity_mapper
AD = self.act_descr_mapper
fk = self._get_mapper_fk(AD)
pk = self._get_mapper_pk(rec)
res = sess.query(A.Activity) \
.join(AD) \
.filter(fk == pk) \
.distinct() \
.all()
related_activities = res and res[0] or []
return related_activities
def _muids_2018(country, region):
# this method needs "raw" access because the shapefile column slows things
t = sql2018.MarineReportingUnit
sess = db.session()
q = sess\
.query(t.MarineReportingUnitId, t.nameTxtInt, t.Description)\
.filter(
t.CountryCode == country,
t.Region == region,
# t.MarineReportingUnitId.isnot(None),
# t.localId.isnot(None), # TODO: this suits NL, check others
)
res = [MarineReportingUnit(m.MarineReportingUnitId,
m.nameTxtInt or m.Description)
for m in q]
return sorted(res)
def get_features_pressures_data(self):
sess = db.session()
esa_mru = sql2018.ART8ESAMarineUnit
rep_info = sql2018.ReportedInformation
esa_feat = sql2018.ART8ESAFeature
esa_uses = sql2018.ART8ESAUsesActivity
esa_uses_p = sql2018.ART8ESAUsesActivitiesPressure
columns = [
rep_info.CountryCode, esa_mru.MarineReportingUnit,
esa_feat.Feature, esa_uses_p.PressureCode
]
country_codes = self.country_code
if not hasattr(country_codes, '__iter__'):
country_codes = [country_codes]
conditions = [
rep_info.Schema == 'ART8_ESA',
rep_info.CountryCode.in_(country_codes)
]
res = sess.query(*columns) \
.join(rep_info, esa_mru.IdReportedInformation == rep_info.Id) \
.join(esa_feat, esa_feat.IdMarineUnit == esa_mru.Id) \
.join(esa_uses, esa_uses.IdFeature == esa_feat.Id) \
.join(esa_uses_p, esa_uses_p.IdUsesActivities == esa_uses.Id) \
.filter(*conditions).distinct()
out = defaultdict(set)
self.features_pressures_data = [x for x in res]
for row in res:
activ_feat = row.Feature
press_code = row.PressureCode
out[activ_feat].add(press_code)
return out
def _query_mru(self):
sess = db.session()
return sess.query(sql2018.MarineReportingUnit).options(defer('SHAPE'))
def items(cls, descriptor, muids):
sess = db.session()
N = cls.primary_mapper
P = cls.pres_mapper
A = cls.ast_mapper
I = cls.indic_mapper
C = cls.crit_mapper
# M = cls.metadata_table
pk = cls._get_mapper_pk(N)
# fk = cls._get_table_fk(M)
# m_q = sess.query(M) \
# .join(N, pk == fk) \
# .filter(M.c.Topic == 'Assessment',
# M.c.MarineUnitID.in_(muids))\
# .distinct()
print 'Started to query data!'
if P:
# .filter(A.Topic == cls.ast_topic)
a_q = sess.query(A).join(N).subquery()
a_A = aliased(A, alias=a_q, adapt_on_names=True)
# .filter(A.Topic.in_(cls.criteria_types))\
c_q = sess.query(C).join(A).subquery()
c_A = aliased(C, alias=c_q, adapt_on_names=True)
# TODO how to filter by topics
# .filter(N.Topic.in_(cls.ast_topic + cls.criteria_types))\
q = sess.query(N, P, a_A, I, c_A)\
.select_from(N) \
.filter(N.Topic != 'InfoGaps')\
.filter(N.MarineUnitID.in_(muids)) \
.outerjoin(a_A)\
.outerjoin(I)\
.outerjoin(c_A)\
.outerjoin(P) \
.distinct()\
.order_by(N.ReportingFeature, pk)
# asd = q.statement.compile(compile_kwargs={"literal_binds": True})
print 'Started to setup data!'
print q.count()
for item in q:
print 'Will yield one item!'
yield cls(descriptor, *item)
# A8aPhysical only has the primary mapper, needs different query
else:
q = sess.query(N)\
.select_from(N)\
.filter(N.Topic != 'InfoGaps')\
.filter(N.MarineUnitID.in_(muids)) \
.order_by(N.Topic, pk)
print q.count()
for item in q:
yield cls(descriptor, *(item, None, None, None, None))
def get_data_from_view_Art8(self):
sess = db.session()
t = sql2018.t_V_ART8_GES_2018
descr_class = get_descriptor(self.descriptor)
all_ids = list(descr_class.all_ids())
if self.descriptor.startswith('D1.'):
all_ids.append('D1')
# muids = [x.id for x in self.muids]
conditions = [
t.c.CountryCode == self.country_code,
# t.c.Region == self.country_region_code,
# t.c.MarineReportingUnit.in_(muids), #
t.c.GESComponent.in_(all_ids)
]
# Handle the case of Romania that submitted duplicate data,
# where Element is empty, but Criteria has data
if self.country_code != 'RO':
conditions.append(
or_(t.c.Element.isnot(None), t.c.Criteria.isnot(None)))
else:
conditions.append(t.c.Element.isnot(None))
if self.country_code != 'DK':
conditions.insert(1, t.c.Region == self.country_region_code)
else:
# Handle the case of Denmark that have submitted a lot of
# information under the DK-TOTAL MRU, which doesn't have a region
# attached.
conditions.insert(
1,
or_(t.c.Region == 'NotReported',
t.c.Region == self.country_region_code))
orderby = [
getattr(t.c, x) for x in self._get_order_cols_Art8(self.descriptor)
]
# groupby IndicatorCode
q = sess\
.query(t)\
.filter(*conditions)\
.order_by(*orderby)\
.distinct()
# For the following countries filter data by features
# for other countries return all data
country_filters = ('BE', )
if self.country_code not in country_filters:
return q
ok_features = set([f.name for f in get_features(self.descriptor)])
out = []
for row in q:
if not self.descriptor.startswith('D1.'):
out.append(row)
continue
feats = set((row.Feature, ))
if feats.intersection(ok_features):
out.append(row)
return out
