def init_connection(self):
if self.state is None:
istsos.debug("Initializing cursor for aiopg connection")
self.state = yield from setting.get_state()
if self.cur is None:
self.context_manager = (yield from self.state.pool.cursor())
self.cur = self.context_manager._cur
def process(self, request):
"""Check the insertObservation consistency
"""
istsos.debug("Check the insertObservation consistency")
offering = request['offerings'][0]
observation = request["observation"]
# ==================================================================
# in istSOS if a systemType has been declared, then the observation
# structure should reflect the declared systemType, following
# the INSPIRE O&M design patterns
# http://inspire.ec.europa.eu/documents/Data_Specifications/D2.9_O&M_Guidelines_v2.0rc3.pdf
# ==================================================================
typedef = ('http://www.opengis.net/def/'
'samplingFeatureType/OGC-OM/2.0/')
# ===============================
# = INSPIRE SpecimenObservation =
# ===============================
# Feature of interest type must be of type SF_Specimen
if (offering["systemType"] == "insitu-fixed-specimen"
and observation["foi_type"] != "%sSF_Specimen" % typedef):
raise Exception("SpecimenObservation design pattern requires "
"SF_Specimen featureOfInterest type")
# result of a specimen must be single phenomenonTime instant otherwise
# SpecimenTimeSeriesObservation should be used
if "timeInstant" not in observation["phenomenonTime"]:
raise Exception("SpecimenObservation design pattern rwequires "
"a phenomenonTime of type timeInstant")
if len(observation["result"]) > 1:
raise Exception("SpecimenObservation design pattern rwequires "
"a single observation record")
def commit(self):
if self._begin is False:
raise Exception("begin must be called first")
if self._commit is False:
istsos.debug("Committing transaction")
yield from self.cur.execute("COMMIT;")
self._commit = True
def process(self, request):
""" @todo docstring
"""
offeringFilter = None
if request.is_get_observation():
if request['method'] == 'GET':
offeringsFilter = request.get_parameter('offering')
if offeringsFilter is not None:
tmpl = OfferingsFilter.get_template()
offeringsFilter = offeringsFilter.split(',')
if len(offeringsFilter) > 0:
tmpl['offerings'] = offeringsFilter
offeringFilter = OfferingsFilter(json_source=tmpl)
elif request.is_insert_observation():
if request['method'] == 'POST':
offeringFilter = OfferingsFilter(
json_source={
"offerings": [
request.get_xml().find('.//sos_2_0:offering',
request.ns).text.strip()
]
})
if offeringFilter is not None:
request.set_filter(offeringFilter)
istsos.debug("Offerings filter: %s" % offeringFilter["offerings"])
else:
istsos.debug("Offering filter NOT set")
def rollback(self):
if self.cur is None or self._begin is False:
return
if self._rollback is False:
istsos.debug("Rolling back transaction")
yield from self.cur.execute("ROLLBACK;")
self._rollback = True
def process(self, request):
dbmanager = yield from self.init_connection()
yield from self.begin()
cur = dbmanager.cur
foi = request['featureOfInterest']
# Check if foi with given identifier already exists
yield from cur.execute(
"""
SELECT EXISTS(
SELECT 1
FROM fois
WHERE identifier = %s
) AS exists;
""", (foi['identifier'], ))
rec = yield from cur.fetchone()
if rec[0] is True:
raise InvalidParameterValue(
"indentifier", ("Feature of interest indentifier"
" '%s' already inserted" % foi['identifier']))
if foi['type'] == setting._SAMPLING_POINT:
istsos.debug("Creating a SAMPLING_POINT")
yield from self.create_sampling_point(cur, foi)
yield from self.commit()
def __get_proxy(istsos_package, action_module, **kwargs):
from istsos import setting
import importlib
state = yield from setting.get_state()
fileName = action_module[0].lower() + action_module[1:]
module = 'istsos.%s.%s.%s' % (
istsos_package,
state.config["loader"]["type"],
fileName
)
istsos.debug("Importing %s.%s" % (module, action_module))
try:
m = importlib.import_module(module)
except Exception:
module = 'istsos.%s.%s' % (
istsos_package,
fileName
)
m = importlib.import_module(module)
m = getattr(m, action_module)
if kwargs is not None:
return m(**kwargs)
return m()
def process(self, request):
"""Depending on the selected procedures call its specific retriever to
query the relatives observed data.
:param dict state: must contain an object with the queried procedures
"""
if 'offerings' not in request:
# A request can also lead to an empty response
return
# @todo check if this helps
if False: # len(request['offerings']) > 1:
istsos.debug("Running retrieval in parallel")
funcs = [
self.__get_data(offering, request)
for offering in request['offerings']
]
yield from asyncio.gather(*funcs)
else:
istsos.debug("Running retrieval sequentially")
if request.get_filter(
"responseFormat") in setting._responseFormat['vega']:
yield from self.__get_kvp(request['offerings'], request)
elif request.get_filter(
"responseFormat") in setting._responseFormat['array']:
yield from self.__get_array(request['offerings'], request)
elif request.get_filter(
"responseFormat") in setting._responseFormat['array2']:
yield from self.__get_array_2(request['offerings'], request)
else:
for offering in request['offerings']:
yield from self.__get_data(offering, request)
def __init__(self, **kwargs):
super(CompositeAction, self).__init__(**kwargs)
istsos.debug(
"Constructing %s.%s" % (
self.__module__,
self.__class__.__name__
)
)
self.actions = []
def begin(self):
if self.cur is None:
yield from self.init_cursor()
if self._begin is False:
istsos.debug("Beginning transaction")
yield from self.cur.execute("BEGIN;")
self._begin = True
else:
istsos.debug("Transation already started")
def process(self, request):
"""Check the insertObservation consistency
"""
istsos.debug("Check the insertObservation consistency")
offering = request['offerings'][0]
observation = request["observation"]
observation_types = []
for observation_type in offering['observation_type']:
observation_types.append(observation_type['definition'])
observation_types_usage_check = list(observation_types)
observable_properties = []
for observable_property in offering['observable_property']:
observable_properties.append(observable_property['definition'])
# Checking the observation type usage. W\ istSOS all the observation
# types declared with the insertSensor must be used on each
# insertObservation
for observation_type in observation['type']:
if observation_type not in observation_types:
raise Exception("observedProperty (%s) is not observed by "
"procedure %s." %
(observedProperty, observation['procedure']))
elif observation_type in observation_types_usage_check:
observation_types_usage_check.pop(
observation_types_usage_check.index(observation_type))
# Checking the observed property usage. W\ istSOS all the observation
# property declared with the insertSensor must be used on each
# insertObservation
for observedProperty in observation['observedProperty']:
if observedProperty not in observable_properties:
raise Exception("observedProperty (%s) is not observed by "
"procedure %s." %
(observedProperty, observation['procedure']))
observable_properties.pop(
observable_properties.index(observedProperty))
# Checking if an observable property is omitted
if len(observable_properties) > 0:
raise Exception(
"With a insertObservation operation all the observable "
"properties must be used. Missing: %s." %
(", ".join(observable_properties)))
# After all the observations are looped check if the procedure
# have omitted the usage of one or more observation type
if len(observation_types_usage_check) > 0:
raise Exception(
"With a insertObservation operation all the observation "
"types must be used. Missing: %s." %
(", ".join(observation_types_usage_check)))
def execute(self, request):
istsos.debug("Executing: %s" % self.__class__.__name__)
start = time.time()
try:
yield from self.before(request)
yield from self.process(request)
yield from self.after(request)
yield from self.close_connection()
except Exception as ex:
yield from self.on_exception(request, ex)
self.time = time.time() - start
self.update_observers(request)
def commit(self):
"""Commit can be called only once and only by root action
"""
root = self.get_root()
if root is None:
istsos.debug("%s is committing now" % self.__class__.__name__)
yield from self.dbmanager.commit()
self.commit_requested = False
else:
istsos.debug("Commit will be executed at the chain's end by %s" %
root.__class__.__name__)
root.commit_requested = True
def execute(self, request):
start = time.time()
istsos.debug("Executing %s" % self.__class__.__name__)
try:
yield from self.before(request)
yield from self.process(request)
for action in self.actions:
yield from action.execute(request)
yield from self.after(request)
if self.commit_requested:
yield from self.commit()
yield from self.close_connection()
except Exception as ex:
yield from self.on_exception(request, ex)
# raise ex # propagate the exception
self.time = time.time() - start
self.update_observers(request)
def get_plugin(plugin, name, **kwargs):
print('GET_PLUGIN')
print(plugin)
print(name)
import importlib
fileName = name[0].lower() + name[1:]
module = 'istsos.plugins.%s.%s.%s' % (plugin, fileName, name)
istsos.debug("Importing Plugin %s: %s.%s" % (plugin, fileName, name))
try:
m = importlib.import_module(module)
except Exception:
module = 'istsos.plugins.%s.%s' % (plugin, fileName)
m = importlib.import_module(module)
m = getattr(m, name)
print(m)
if kwargs is not None:
return m(**kwargs)
return m()
def __download_file(self, request, data, headers=None):
if request.get_filter("download_file") is not None:
if 'file_name' in request.get_filter("download_file"):
file_name=request.get_filter("download_file")['file_name']
else:
file_name=request.get_filter("offerings")
if 'location' in request.get_filter("download_file"):
download_location=request.get_filter("download_file")['location']
else:
download_location='istsos/plugins/unit_con_pint/download_file/'
file_detail = """%s%s.csv""" % (download_location, file_name)
f = csv.writer(open(file_detail, "w"))
if headers is not None:
f.writerow(headers)
for x in data:
f.writerow(x)
debug_detail = """%s.csv download location is %s""" % (file_name, download_location)
istsos.debug(debug_detail)
def process(self, request):
""" @todo docstring
"""
if request.is_insert_sensor():
# Preparing data dictionary
data = Offering.get_template()
# If procedure description is an sensorML 1.0.1 then check if the
# procedure identifier is given.
for identifier in request.get_xml().iterfind(
'.//sml_1_0_1:identifier', request.ns):
if identifier.get('name') == 'uniqueID':
value = identifier.find('.//sml_1_0_1:value', request.ns)
data['procedure'] = value.text.strip()
break
istsos.debug("Procedure uniqueID: %s" % data['procedure'])
# If procedure description is an sensorML 1.0.1 then check if the
# offering identifier is given.
for capability in request.get_xml().iterfind(
'.//sml_1_0_1:capabilities', request.ns):
if capability.get('name') == 'offering':
value = capability.find('.//swe_1_0_1:value', request.ns)
data['name'] = value.text.strip()
if capability.get('name') == 'featuresOfInterest':
value = capability.find('.//swe_1_0_1:value', request.ns)
data['foi_name'] = value.text.strip()
istsos.debug("Foi name: %s" % data['foi_name'])
istsos.debug("Offering uniqueID: %s" % data['name'])
# Reading and adding the Observable Properties(s)
for observableProperty in request.get_xml().iterfind(
'.//swes_2_0:observableProperty', request.ns):
data['observable_property'].append(
{"definition": observableProperty.text.strip()})
istsos.debug("Observed properties: %s" %
len(data['observable_property']))
# Reading and adding the Observation Type(s)
for observationType in request.get_xml().iterfind(
'.//sos_2_0:observationType', request.ns):
data['observation_type'].append(
{"definition": observationType.text.strip()})
istsos.debug("Observation types: %s" %
len(data['observation_type']))
# Reading and setting the feature of interest type
foi_type = request.get_xml().find(
'.//sos_2_0:featureOfInterestType', request.ns)
if foi_type is not None:
data['foi_type'] = foi_type.text.strip()
istsos.debug("Feature of Interest Type: %s" %
data['foi_type'].replace(setting._foidef, ''))
"""for classifier in request.get_xml().iterfind(
'.//sml_1_0_1:classifier', request.ns):
if classifier.get('name') == 'systemType':
value = classifier.find('.//sml_1_0_1:value', request.ns)
data['systemType'] = value.text.strip()
break
if data['systemType'] is None:
# Guess from other configuration elements
if setting._GEOMETRY_OBSERVATION in data['observation_type'] \
and setting._SAMPLING_CURVE == data['foi_type']:
data['systemType'] = setting._INSITU_MOBILE_POINT
elif setting._GEOMETRY_OBSERVATION not in data[
'observation_type'] \
and setting._SAMPLING_POINT == data['foi_type']:
data['systemType'] = setting._INSITU_MOBILE_POINT
elif setting._SAMPLING_SPECIMEN == data['foi_type']:
data['systemType'] = setting._INSITU_FIXED_SPECIMEN
else:
data['systemType'] = setting._INSITU_FIXED_POINT
istsos.debug(
"System Type: %s" % data['systemType'])"""
request['offering'] = Offering(json_source=data)
def __get_kvp(self, offerings, request):
dbmanager = yield from self.init_connection()
cur = dbmanager.cur
op_filter = request.get_filter('observedProperties')
tables = {}
columns = []
for offering in request['offerings']:
tName = "_%s" % offering['name'].lower()
if offering.is_complex():
tables[tName] = []
for op in offering['observable_properties']:
if op['type'] == setting._COMPLEX_OBSERVATION:
continue
else:
# observedProperty filters are applied here excluding
# the observed properties columns from the query
if op_filter is not None and (
op['definition'] not in op_filter):
continue
columns.append(op['column'])
# columns_qi.append('%s_qi' % op['column'])
tables[tName].append(op['column'])
elif offering.is_array():
raise Exception("Not implemented yet")
else:
tables[tName] = []
for op in offering['observable_properties']:
# observedProperty filters are applied here excluding
# the observed properties columns from the query
if op_filter is not None and (
op['definition'] not in op_filter):
continue
columns.append(op['column'])
# columns_qi.append('%s_qi' % op['column'])
tables[tName].append(op['column'])
unions = []
unionSelect = []
jsonKeys = []
unionColumns = []
for idx in range(0, len(columns)):
unionSelect.append(
"SUM(c%s)::text as c%s" % (idx, idx)
)
unionColumns.append(
"NULL::double precision as c%s" % (idx)
)
jsonKeys.append("""
"%s": ' || COALESCE(c%s, 'null') || '
""" % (
columns[idx],
idx
))
unionSelect = ", ".join(unionSelect)
temporal = []
where = []
params = []
if request.get_filters() is not None:
keys = list(request.get_filters())
for key in keys:
fltr = request.get_filters()[key]
if key == 'temporal':
if fltr['fes'] == 'during':
temporal.append("""
begin_time >= %s::timestamp with time zone
AND
end_time <= %s::timestamp with time zone
""")
params.extend(fltr['period'])
elif fltr['fes'] == 'equals':
temporal.append("""
begin_time = end_time
AND
begin_time = %s::timestamp with time zone
""")
params.append(fltr['instant'])
where.append(
"(%s)" % (' OR '.join(temporal))
)
for table in tables.keys():
off_cols = tables[table]
cols = unionColumns.copy()
for col in off_cols:
cols[
columns.index(col)
] = unionColumns[columns.index(col)].replace(
"NULL::double precision",
col
)
uSql = """
SELECT
end_time, %s
FROM
data.%s
""" % (
", ".join(cols), table
)
if len(where) > 0:
uSql += "WHERE %s" % (
'AND'.join(where)
)
unions.append("(%s)" % uSql)
jsonSql = """
SELECT array_to_json(
array_agg(('{
"e": "' || to_char(
end_time, 'YYYY-MM-DD"T"HH24:MI:SSZ')
|| '",
%s
}')::json)
)
FROM
""" % (
", ".join(jsonKeys),
)
sql = """
SET enable_seqscan=false;
SET SESSION TIME ZONE '+00:00';
%s
(
SELECT end_time, %s
FROM (
%s
) a
GROUP BY end_time
ORDER BY end_time
) b
""" % (
jsonSql,
unionSelect,
" UNION ".join(unions)
)
# print('Length of Uninons')
# print(len(unions))
# print(unions)
istsos.debug(
(
yield from cur.mogrify(sql, tuple(params*len(unions)))
).decode("utf-8")
)
# print('observation.py')
# print(sql)
# yield from cur.execute(sql, tuple(params*2)
yield from cur.execute(sql, tuple(params*len(unions)))
rec = yield from cur.fetchone()
# print(rec)
# print('This is successful')
request['observations'] = rec[0]
# recs = yield from cur.fetchall()
istsos.debug("Data is fetched!")
def add(self, action):
istsos.debug("Adding %s.%s" %
(action.__module__, action.__class__.__name__))
self.actions.append(action)
action.set_parent(self)
def __get_array_2(self, offerings, request):
dbmanager = yield from self.init_connection()
cur = dbmanager.cur
op_filter = request.get_filter('observedProperties')
tables = {}
columns = []
headers = [{
"type": "datetime",
"name": "Phenomenon Time",
"column": "e"
}]
for offering in request['offerings']:
tName = "_%s" % offering['name'].lower()
if offering.is_complex():
tables[tName] = []
for op in offering['observable_properties']:
if op['type'] == setting._COMPLEX_OBSERVATION:
continue
else:
# observedProperty filters are applied here excluding
# the observed properties columns from the query
if op_filter is not None and (op['definition']
not in op_filter):
continue
columns.append(op['column'])
# columns_qi.append('%s_qi' % op['column'])
tables[tName].append(op['column'])
headers.append({
"type": "number",
"name": op['name'],
"definition": op['definition'],
"offering": offering['name'],
"uom": op['uom'],
"column": op['column']
})
elif offering.is_array():
raise Exception("Not implemented yet")
else:
tables[tName] = []
for op in offering['observable_properties']:
# observedProperty filters are applied here excluding
# the observed properties columns from the query
if op_filter is not None and (op['definition']
not in op_filter):
continue
columns.append(op['column'])
# columns_qi.append('%s_qi' % op['column'])
tables[tName].append(op['column'])
headers.append({
"type": "number",
"name": op['name'],
"definition": op['definition'],
"offering": offering['name'],
"uom": op['uom'],
"column": op['column']
})
unions = []
unionSelect = []
jsonKeys = [
"array_agg(to_char(end_time, 'YYYY-MM-DD\"T\"HH24:MI:SSZ'))"
]
unionColumns = []
for idx in range(0, len(columns)):
unionSelect.append("SUM(c%s) as c%s" % (idx, idx))
unionColumns.append("NULL::double precision as c%s" % (idx))
jsonKeys.append("array_agg(c%s)" % (idx))
unionSelect = ", ".join(unionSelect)
temporal = []
where = []
params = []
if request.get_filters() is not None:
keys = list(request.get_filters())
for key in keys:
fltr = request.get_filters()[key]
if key == 'temporal':
if fltr['fes'] == 'during':
temporal.append("""
begin_time >= %s::timestamp with time zone
AND
end_time <= %s::timestamp with time zone
""")
params.extend(fltr['period'])
elif fltr['fes'] == 'equals':
temporal.append("""
begin_time = end_time
AND
begin_time = %s::timestamp with time zone
""")
params.append(fltr['instant'])
where.append("(%s)" % (' OR '.join(temporal)))
for table in tables.keys():
off_cols = tables[table]
cols = unionColumns.copy()
for col in off_cols:
cols[columns.index(col)] = unionColumns[columns.index(
col)].replace("NULL::double precision", col)
uSql = """
SELECT
end_time, %s
FROM
data.%s
""" % (", ".join(cols), table)
if len(where) > 0:
uSql += "WHERE %s" % ('AND'.join(where))
unions.append("(%s)" % uSql)
jsonSql = """
SELECT %s
FROM
""" % (", ".join(jsonKeys), )
sql = """
SET enable_seqscan=false;
SET SESSION TIME ZONE '+00:00';
%s
(
SELECT end_time, %s
FROM (
%s
) a
GROUP BY end_time
ORDER BY end_time
) b
""" % (jsonSql, unionSelect, " UNION ".join(unions))
# istsos.debug(
# (
# yield from cur.mogrify(sql, tuple(params*len(unions)))
# ).decode("utf-8")
# )
yield from cur.execute(sql, tuple(params * len(unions)))
rec = yield from cur.fetchone()
request['observations'] = {}
for idx in range(0, len(headers)):
header = headers[idx]
request['observations'][header['column']] = rec[idx]
request['headers'] = headers
istsos.debug("Data is fetched!")
def __get_data(self, offering, request):
dbmanager = yield from self.init_connection()
cur = dbmanager.cur
table_name = "data._%s" % offering['name'].lower()
columns = []
# columns_qi = []
op_filter = request.get_filter('observedProperties')
observation = Observation.get_template({
"offering":
offering['name'],
"procedure":
offering['procedure']
})
if offering.is_complex():
observation["type"] = setting._COMPLEX_OBSERVATION
op = offering.get_complex_observable_property()
observation["observedProperty"] = \
ObservedPropertyComplex.get_template({
"def": op['definition'],
"name": op['name'],
"type": op['type'],
"uom": op['uom']
})
for op in offering['observable_properties']:
if op['type'] == setting._COMPLEX_OBSERVATION:
continue
else:
# observedProperty filters are applied here excluding
# the observed properties columns from the query
if op_filter is not None and (op['definition']
not in op_filter):
continue
observation["observedProperty"]['fields'].append(
ObservedProperty.get_template({
"def": op['definition'],
"name": op['name'],
"type": op['type'],
"uom": op['uom']
}))
columns.append(op['column'])
# columns_qi.append('%s_qi' % op['column'])
elif offering.is_array():
raise Exception("Not implemented yet")
else:
for op in offering['observable_properties']:
observation["type"] = op['type']
# observedProperty filters are applied here excluding
# the observed properties columns from the query
if op_filter is not None and (op['definition']
not in op_filter):
continue
observation["observedProperty"] = \
ObservedProperty.get_template({
"def": op['definition'],
"name": op['name'],
"type": op['type'],
"uom": op['uom']
})
columns.append(op['column'])
# columns_qi.append('%s_qi' % op['column'])
observation["phenomenonTime"] = {
"timePeriod": {
"begin": "",
"end": ""
}
}
if request.get_filter(
"responseFormat") in setting._responseFormat['vega']:
fastSql = """
SELECT array_to_json(
array_agg(('{
"o": "%s",
"b": "' || to_char(
begin_time, 'YYYY-MM-DD"T"HH24:MI:SS+02:00')
|| '",
"e": "' || to_char(
end_time, 'YYYY-MM-DD"T"HH24:MI:SS+02:00')
|| '",
"r": "' || to_char(
result_time, 'YYYY-MM-DD"T"HH24:MI:SS+02:00')
|| '",
"a": "' || %s || '"
}')::json)
)
FROM (
""" % (
offering['name'],
columns[0],
)
else:
fastSql = """
SELECT
array_to_json(
array_agg(('{
"offering": "%s",
"procedure": "%s",
"type": %s,
"featureOfInterest": "ciao",
"phenomenonTime": {
"timePeriod": {
"begin": "' || begin_time || '",
"end": "' || end_time || '"
}
},
"resultTime": {
"timeInstant": {
"instant": "' || result_time || '"
}
},
"result": "' || %s || '",
"observedProperty": %s
}')::json)
)
FROM (
""" % (offering['name'], offering['procedure'],
json.dumps(observation["type"]), columns[0],
json.dumps(observation["observedProperty"]))
sql = """
SELECT
begin_time,
end_time,
result_time,
%s""" % (", ".join(columns)) + """
FROM %s
""" % table_name
temporal = []
where = []
params = []
if request.get_filters() is not None:
keys = list(request.get_filters())
for key in keys:
fltr = request.get_filters()[key]
if key == 'temporal':
if fltr['fes'] == 'during':
temporal.append("""
begin_time >= %s::timestamp with time zone
AND
end_time <= %s::timestamp with time zone
""")
params.extend(fltr['period'])
elif fltr['fes'] == 'equals':
temporal.append("""
begin_time = end_time
AND
begin_time = %s::timestamp with time zone
""")
params.append(fltr['instant'])
where.append("(%s)" % (' OR '.join(temporal)))
if len(where) > 0:
sql += "WHERE %s" % ('AND'.join(where))
sql = """
SET enable_seqscan=false;
SET SESSION TIME ZONE '+02:00';
%s
%s
ORDER BY begin_time ) t
""" % (fastSql, sql)
istsos.debug((yield from cur.mogrify(sql,
tuple(params))).decode("utf-8"))
yield from cur.execute(sql, tuple(params))
rec = yield from cur.fetchone()
request['observations'] += rec[0]
# recs = yield from cur.fetchall()
istsos.debug("Data is fetched!")
def process(self, request):
"""Load all the offerings relative to the given filter.
"""
dbmanager = yield from self.init_connection()
cur = dbmanager.cur
sql = """
SELECT DISTINCT
offerings.id,
offering_name,
procedure_name,
foi_type,
sampled_foi,
data_table_exists,
pt_begin,
pt_end,
rt_begin,
rt_end,
config
FROM
offerings,
off_obs_prop,
observed_properties
WHERE
id_opr = observed_properties.id
AND
id_off = offerings.id
"""
where = []
if request.get_filters() is not None:
keys = list(request.get_filters())
for key in keys:
if key == 'specimen':
where.append("foi_type = '%s'" %
setting._SAMPLING_SPECIMEN)
if key == 'observedProperties' and \
len(request.get_filter(key)) > 0:
ops = []
for op in request.get_filter(key):
ops.append((yield from
cur.mogrify("observed_properties.def = %s",
(op, ))).decode("utf-8"))
where.append("(%s)" % " OR ".join(ops))
if len(where) > 0:
sql += "AND %s" % ('\nAND '.join(where))
istsos.debug((yield from cur.mogrify(sql)).decode("utf-8"))
yield from cur.execute(sql)
recs = yield from cur.fetchall()
for res in recs:
off = Offering.get_template({
'id': res[0],
'offering': res[1],
'procedure': res[2],
'foi_type': res[3],
'sampled_foi': res[4],
'config': res[10]
})
table = res[5]
if res[6] is not None and res[7] is not None:
off["phenomenon_time"] = {
"begin": res[6].isoformat(),
"end": res[7].isoformat()
}
if res[8] is not None and res[9] is not None:
off["result_time"] = {
"begin": res[8].isoformat(),
"end": res[9].isoformat()
}
yield from cur.execute(
"""
SELECT
observation_type
FROM
off_obs_type
WHERE
id_off = %s;""", (res[0], ))
observation_types = yield from cur.fetchall()
for observation_type in observation_types:
off['observation_types'].append(observation_type[0])
if table:
yield from cur.execute(
"""
SELECT
off_obs_prop.id,
observed_properties.name,
observed_properties.def,
uoms.name,
observation_type
FROM
off_obs_prop
INNER JOIN observed_properties
ON id_opr = observed_properties.id
LEFT JOIN uoms
ON id_uom = uoms.id
WHERE
id_off = %s
ORDER BY
off_obs_prop.id
""", (res[0], ))
r_obs = yield from cur.fetchall()
for obs_prop in r_obs:
op = ObservableProperty.get_template({
"id":
obs_prop[0],
"name":
obs_prop[1],
"definition":
obs_prop[2],
"uom":
obs_prop[3],
# "type": obs_prop[3]
})
if obs_prop[4] is not None:
op['type'] = obs_prop[4]
off['observable_properties'].append(ObservableProperty(op))
request['offeringsList'].append(Offering(off))
def after(self, request):
response = etree.XML("""<sos:GetObservationResponse
xmlns:sos="http://www.opengis.net/sos/2.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:swe="http://www.opengis.net/swe/2.0"
xmlns:swes="http://www.opengis.net/swes/2.0"
xmlns:gml="http://www.opengis.net/gml/3.2"
xmlns:ogc="http://www.opengis.net/ogc"
xmlns:om="http://www.opengis.net/om/2.0"
xmlns:xlink="http://www.w3.org/1999/xlink">
</sos:GetObservationResponse>""")
istsos.debug("Preparing %s observations" %
len(request['observations']))
if len(request['observations']) > 0:
ns = request.ns
data = etree.SubElement(
response,
'{%s}observationData' % ns['sos_2_0'],
)
oid = 0
for observation in request['observations']:
oid += 1
# Preparing default metadata elements
omObs = etree.SubElement(data,
'{%s}OM_Observation' % ns['om_2_0'])
omObs.set("{%s}id" % ns['gml_3_2'], str(oid))
etree.SubElement(omObs, '{%s}type' % ns['om_2_0']).set(
"{%s}href" % ns['xlink'], observation['type'])
# Adding om:phenomenonTime
phenomenonTime = etree.SubElement(
omObs, '{%s}phenomenonTime' % ns['om_2_0'])
if 'timeInstant' in observation['phenomenonTime']:
timeInstantId = "p%s" % oid
# timeResultId = "r%s" % oid
timeInstant = etree.SubElement(
phenomenonTime, '{%s}TimeInstant' % ns['gml_3_2'])
etree.SubElement(
timeInstant,
'{%s}timePosition' % ns['gml_3_2']).text = observation[
'phenomenonTime']['timeInstant']['instant']
if observation[
'phenomenonTime']['timeInstant']['instant'] == \
observation[
'resultTime']['timeInstant']['instant']:
timeInstant.set("{%s}id" % ns['gml_3_2'],
timeInstantId)
etree.SubElement(omObs,
'{%s}resultTime' % ns['om_2_0']).set(
"{%s}href" % ns['xlink'],
'#%s' % timeInstantId)
else:
# Adding om:resultTime
resultTime = etree.SubElement(
omObs, '{%s}resultTime' % ns['om_2_0'])
timeInstant = etree.SubElement(
resultTime, '{%s}TimeInstant' % ns['gml_3_2'])
# timeInstant.set(
# "{%s}id" % ns['gml_3_2'], timeInstantId)
etree.SubElement(
timeInstant, '{%s}timePosition' %
ns['gml_3_2']).text = observation['resultTime'][
'timeInstant']['instant']
else:
timePeriod = etree.SubElement(
phenomenonTime, '{%s}TimePeriod' % ns['gml_3_2'])
# timePeriod.set(
# "{%s}id" % ns['gml_3_2'], timeInstantId)
etree.SubElement(
timePeriod, '{%s}beginPosition' %
ns['gml_3_2']).text = observation['phenomenonTime'][
'timePeriod']['begin']
etree.SubElement(
timePeriod, '{%s}endPosition' % ns['gml_3_2']
).text = observation['phenomenonTime']['timePeriod']['end']
# Adding om:resultTime
resultTime = etree.SubElement(
omObs, '{%s}resultTime' % ns['om_2_0'])
timeInstant = etree.SubElement(
resultTime, '{%s}TimeInstant' % ns['gml_3_2'])
# timeInstant.set(
# "{%s}id" % ns['gml_3_2'], timeInstantId)
etree.SubElement(
timeInstant,
'{%s}timePosition' % ns['gml_3_2']).text = observation[
'resultTime']['timeInstant']['instant']
# Adding om:observedProperty
etree.SubElement(omObs, '{%s}procedure' % ns['om_2_0']).set(
"{%s}href" % ns['xlink'], observation["procedure"])
# Adding om:observedProperty
etree.SubElement(omObs,
'{%s}observedProperty' % ns['om_2_0']).set(
"{%s}href" % ns['xlink'],
observation["observedProperty"]["def"])
# Adding om:featureOfInterest
etree.SubElement(omObs,
'{%s}featureOfInterest' % ns['om_2_0']).set(
"{%s}href" % ns['xlink'],
observation["featureOfInterest"])
# Adding om:result
omresult = etree.SubElement(omObs, '{%s}result' % ns['om_2_0'])
if observation['type'] == setting._COMPLEX_OBSERVATION:
omresult.set(
"{%s}type" % ns['xsi'],
setting.get_observation_type(
setting._COMPLEX_OBSERVATION)['type'])
dataRecord = etree.SubElement(
omresult, '{%s}DataRecord' % ns['swe_2_0'])
opFields = observation.get_field_list()
for idx in range(0, len(opFields)):
opField = opFields[idx]
field = etree.SubElement(dataRecord,
'{%s}field' % ns['swe_2_0'])
quantity = etree.SubElement(
field, '{%s}Quantity' % ns['swe_2_0'])
quantity.set("definition", opField["def"])
etree.SubElement(quantity,
'{%s}uom' % ns['swe_2_0']).set(
"code", opField['uom'])
etree.SubElement(
quantity, '{%s}value' % ns['swe_2_0']
).text = "%s" % observation["result"][idx]
elif observation['type'] == setting._ARRAY_OBSERVATION:
pass
else:
omresult.set("uom", observation["observedProperty"]["uom"])
omresult.set(
"{%s}type" % ns['xsi'],
setting.get_observation_type(
observation["observedProperty"]['type'])['type'])
omresult.text = str(observation["result"])
# request['response'] = self.get_classic_response(request)
# request['response'] = self.get_array_response(request)
request['response'] = (
'<?xml version="1.0" encoding="UTF-8"?>\n%s') % etree.tostring(
response, encoding='unicode', method='xml')
def close(self):
if self.cur is None:
raise Exception("Cursor is not opened")
istsos.debug("Closing database connection")
self.context_manager.__exit__()
def add_field(self, offering, observedProperty, cur):
istsos.debug("Adding field: %s" % observedProperty['def'])
# Getting offering's observable property
observable_property = offering.get_observable_property(
observedProperty['def'])
# Get id from off_obs_prop table
id_obp = observable_property['id']
uom = observedProperty["uom"]
# If uom is given, check if exists, otherwise insert
# the new unit of measure into the uoms table
if uom is not None:
# Updating cache object
observable_property['uom'] = uom
# query the uom
yield from cur.execute(
"""
SELECT id
FROM uoms
WHERE name = %s
""", (uom, ))
rec = yield from cur.fetchone()
if rec is None:
# Uom does not exist, insert the new uom
yield from cur.execute(
"""
INSERT INTO uoms(name)
VALUES (%s) RETURNING id;
""", (uom, ))
rec = yield from cur.fetchone()
id_uom = rec[0]
istsos.debug("uom: %s#%s" % (uom, id_uom))
# Now update the configuration into the
# off_obs_prop table, if the UOM is given
yield from cur.execute(
"""
UPDATE off_obs_prop
SET
observation_type=%s,
col_name=%s,
id_uom=%s
WHERE id=%s;
""", (observedProperty['type'], "_%s" % id_obp, id_uom, id_obp))
# uom is not given, id_uom in off_obs_prop will be null
else:
yield from cur.execute(
"""
UPDATE off_obs_prop
SET
observation_type=%s,
col_name=%s
WHERE id=%s;
""", (observedProperty['type'], "_%s" % id_obp, id_obp))
# Adding missing columns in the measures table
# of this offering
sqlType = None
if observedProperty['type'] in [
setting._CATEGORY_OBSERVATION, setting._TEXT_OBSERVATION
]:
sqlType = "character varying"
elif observedProperty['type'] == setting._COUNT_OBSERVATION:
sqlType = "integer"
elif observedProperty['type'] == setting._MESAUREMENT_OBSERVATION:
sqlType = "double precision"
elif observedProperty['type'] == setting._TRUTH_OBSERVATION:
sqlType = "boolean"
elif observedProperty['type'] == setting._GEOMETRY_OBSERVATION:
sqlType = "geometry"
elif observedProperty['type'] == setting._COMPLEX_OBSERVATION:
sqlType = None
else:
raise Exception("Observation type '%s' unknown" %
observedProperty['type'])
if sqlType is not None:
yield from cur.execute(
"""
ALTER TABLE data._%s
ADD COLUMN _%s %s;
ALTER TABLE data._%s
ADD COLUMN _%s_qi integer;
""" %
(offering['name'], id_obp, sqlType, offering['name'], id_obp))
# Updating missing parameters
observable_property['column'] = '_%s' % id_obp
observable_property['type'] = observedProperty['type']
def after(self, request):
if "offering" in request and "id" not in request['offering']:
istsos.debug(
"OfferingCreator shall set the id in the Offering entity, "
"but it looks like it is not."
)
def __get_array(self, offerings, request):
# print("To PRINT REQUEST AT OBSERVATION")
# print(request[0]['offerings']['observable_properties'])
# To_unit=request['json']['in_unit']
# print("################################3")
ConvertUnit=''
dbmanager = yield from self.init_connection()
cur = dbmanager.cur
op_filter = request.get_filter('observedProperties')
# print('Print Unit of observations line 250')
# print(op_filter)
tables = {}
columns = []
headers = [{
"type": "time",
"name": "Phenomenon Time",
"column": "datetime"
}]
for offering in request['offerings']:
tName = "_%s" % offering['name'].lower()
if offering.is_complex():
tables[tName] = []
for op in offering['observable_properties']:
if op['type'] == setting._COMPLEX_OBSERVATION:
continue
else:
# observedProperty filters are applied here excluding
# the observed properties columns from the query
if op_filter is not None and (
op['definition'] not in op_filter):
continue
columns.append(op['column'])
# columns_qi.append('%s_qi' % op['column'])
tables[tName].append(op['column'])
ConvertUnit=op['uom']
headers.append({
"type": "number",
"name": op['name'],
"definition": op['definition'],
"offering": offering['name'],
"uom": op['uom']
})
elif offering.is_array():
raise Exception("Not implemented yet")
else:
tables[tName] = []
for op in offering['observable_properties']:
# observedProperty filters are applied here excluding
# the observed properties columns from the query
if op_filter is not None and (
op['definition'] not in op_filter):
continue
columns.append(op['column'])
# columns_qi.append('%s_qi' % op['column'])
tables[tName].append(op['column'])
ConvertUnit=op['uom']
headers.append({
"type": "number",
"name": op['name'],
"definition": op['definition'],
"offering": offering['name'],
"uom": op['uom']
})
# print('IT Is Unit IN OFFERING')
# print(ConvertUnit)
unions = []
unionSelect = []
jsonKeys = []
unionColumns = []
# for idx in range(0, len(columns)):
# unionSelect.append(
# "SUM(c%s)::text as c%s" % (idx, idx)
# )
# unionColumns.append(
# "NULL::double precision"
# )
# jsonKeys.append("COALESCE(c%s, 'null')" % (idx))
# for idx in range(0, len(columns)):
# unionSelect.append(
# "SUM(c%s)::text as c%s" % (idx, idx)
# )
# unionColumns.append(
# "NULL::double precision as c%s" % (idx)
# )
# jsonKeys.append("COALESCE(c%s, 'null')" % (idx))
for idx in range(0, len(columns)):
unionSelect.append(
"SUM(c%s)::text as c%s" % (idx, idx)
)
unionColumns.append(
"NULL::double precision as c%s" % (idx)
)
jsonKeys.append("COALESCE(c%s, 'null')" % (idx))
unionSelect = ", ".join(unionSelect)
temporal = []
where = []
params = []
if request.get_filters() is not None:
keys = list(request.get_filters())
for key in keys:
fltr = request.get_filters()[key]
if key == 'temporal':
if fltr['fes'] == 'during':
temporal.append("""
begin_time >= %s::timestamp with time zone
AND
end_time <= %s::timestamp with time zone
""")
params.extend(fltr['period'])
elif fltr['fes'] == 'equals':
temporal.append("""
begin_time = end_time
AND
begin_time = %s::timestamp with time zone
""")
params.append(fltr['instant'])
where.append(
"(%s)" % (' OR '.join(temporal))
)
# print('Print Unit of Measurement')
# print(headers)
for table in tables.keys():
off_cols = tables[table]
cols = unionColumns.copy()
# print('Print col in observations')
# print(cols)
# print(off_cols)
for col in off_cols:
# ConvertScript="""np.ting from(select SUBSTRING(CAST(tmp.num as varchar),'[0-9]+') as ting from(select %s *'m'::unit@ 'mm' as num)as tmp)as np"""% (", ",col)
# ConvertScript="""
# c0.ting as c0
# from
# (
# select SUBSTRING(CAST(tmp.num as varchar),'[0-9]+') as ting
# from
# (
# select """+col+ """*'m'::unit@ 'mm' as num from data._belin
# )as tmp)"""
##################################
# ConvertScript="""np.ting as c0
# from
# (
# select SUBSTRING(CAST(tmp.num as varchar),'[0-9]+') as ting
# from
# (
# select """+col+ """*'m'::unit@ 'mm' as num
# )as tmp) as np"""
# # ConvertScript="np.ting from(select SUBSTRING(CAST(tmp.num as varchar),'[0-9]+') as ting from(select" +col+ "*'m'::unit@ 'mm' as num)as tmp)as np"
# print('Printing ConvertScript')
# print(ConvertScript)
###################################
# cols[
# columns.index(col)
# ] = unionColumns[columns.index(col)].replace(
# "NULL::double precision", ConvertScript
# )
####################################3
# convert_unit="""%s*'%s'::unit@@'%s' """%(col,ConvertUnit,To_unit)
# print('Print convert query for postgresql-unit')
# print(convert_unit)
# cols[
# columns.index(col)
# ] = unionColumns[columns.index(col)].replace(
# "NULL::double precision",
# convert_unit
# )
#############################
# cols[
# columns.index(col)
# ] = unionColumns[columns.index(col)].replace(
# "NULL::double precision",
# col+"*'m'::unit@@'mm' "
# )
if 'in_unit' in request['json']:
To_unit=request['json']['in_unit']
convert_unit="""%s*'%s'::unit@@'%s' """%(col,ConvertUnit,To_unit)
# print('Print convert query for postgresql-unit')
# print(convert_unit)
cols[
columns.index(col)
] = unionColumns[columns.index(col)].replace(
"NULL::double precision",
convert_unit
)
else:
cols[
columns.index(col)
] = unionColumns[columns.index(col)].replace(
"NULL::double precision",
col
)
# print('Print col in observations 1')
# print(cols)
# print(off_cols)
# uSql = """
# SELECT
# end_time, %s
# FROM
# data.%s
# """ % (
# ", ".join(cols), table
# )
uSql = """
SELECT
end_time, %s
FROM
data.%s
""" % (
", ".join(cols), table
)
# print('Query Printing uSql')
# print(uSql)
# uSql = """
# SELECT
# end_time, %s '*' %s ::unit@ %s
# FROM
# data.%s
# """ % (
# ", ".join(cols), ConvertUnit, To_unit, table
# )
if len(where) > 0:
uSql += "WHERE %s" % (
'AND'.join(where)
)
unions.append("(%s)" % uSql)
# print('Query Printing uSql')
# print(uSql)
jsonSql = """
SELECT array_agg(
ARRAY[
to_char(end_time, 'YYYY-MM-DD"T"HH24:MI:SSZ'),
%s
]
)
FROM
""" % (
", ".join(jsonKeys),
)
# print('Query Printing jsonSql')
# print(jsonSql)
# print('Query Printing unionSelect')
# print(unionSelect)
# print('union')
# print(unions)
# sql = """
# SET enable_seqscan=false;
# SET SESSION TIME ZONE '+00:00';
# %s
# (
# SELECT end_time, %s
# FROM (
# %s
# ) a
# GROUP BY end_time
# ORDER BY end_time
# ) b
# """ % (
# jsonSql,
# unionSelect,
# " UNION ".join(unions)
# )
sql = """
SET enable_seqscan=false;
SET SESSION TIME ZONE '+00:00';
%s
(
SELECT end_time, %s
FROM (
%s
) a
GROUP BY end_time
ORDER BY end_time
) b
""" % (
jsonSql,
unionSelect,
" UNION ".join(unions)
)
# sql = """
# SET enable_seqscan=false;
# SET SESSION TIME ZONE '+00:00';
# %s
# (
# SELECT end_time, %s*%s::unit@%s
# FROM (
# %s
# ) a
# GROUP BY end_time
# ORDER BY end_time
# ) b
# """ % (
# jsonSql,
# unionSelect,
# ConvertUnit,
# To_unit,
# " UNION ".join(unions)
# )
istsos.debug(
(
yield from cur.mogrify(sql, tuple(params*len(unions)))
).decode("utf-8")
)
# istsos.debug(
# (
# yield from cur.mogrify(sql, tuple(params*2))
# ).decode("utf-8")
# )
# print("Observation.py")
# print(sql)
yield from cur.execute(sql, tuple(params*len(unions)))
# yield from cur.execute(sql, tuple(params*0))
rec = yield from cur.fetchone()
request['observations'] = rec[0]
request['headers'] = headers
# recs = yield from cur.fetchall()
istsos.debug("Data is fetched!")
def close_connection(self):
root = self.get_root()
if root is None and self.dbmanager is not None:
istsos.debug("%s is closing now" % self.__class__.__name__)
yield from self.dbmanager.close()
self.dbmanager = None
def after(self, request):
offering = request['offerings'][0]
if len(request['offerings'])==0:
raise Exception(
"Offering \"%s\" not registered" % self.data['offering'])
if offering['foi_type'] == setting._SAMPLING_SPECIMEN:
raise Exception(
"Offering type \"speciment\" not yet supported")
if not offering['fixed']:
raise Exception(
"Not fixed Offering not yet supported")
columns = []
for op in offering['observable_properties']:
if not op['type'] == setting._COMPLEX_OBSERVATION:
columns.extend([
op['column'],
"%s_qi" % op['column']
])
bp = ep = None
if offering['phenomenon_time'] is not None:
bp = istsos.str2date(
offering['phenomenon_time']['timePeriod']['begin']
)
ep = istsos.str2date(
offering['phenomenon_time']['timePeriod']['end']
)
obsCnt = 1
if setting._COMPLEX_OBSERVATION in offering['observation_types']:
obsCnt = len(offering['observable_properties']) - 1
dbmanager = yield from self.init_connection()
cur = dbmanager.cur
rows = self.data['observations']
values = []
for row in rows:
try:
sampling_time = row.pop(0)
sampling_time_dt = istsos.str2date(sampling_time)
except Exception as dtex:
raise Exception(
"Procedure %s, Sampling time (%s) "
"wrong format" % (
offering['name'], sampling_time
)
)
params = [
str(uuid.uuid1()).replace('-', ''),
sampling_time,
sampling_time,
sampling_time
]
# Check time consistency
if offering['phenomenon_time'] is not None:
# If the end position exists the new measures
# must be after
if sampling_time_dt < ep:
# non blocking exception: skip row
istsos.debug("Skipping observation: %s" % row)
continue
if len(row) != obsCnt:
istsos.debug(
"Observations count missmatch (%s!=%s)" % (
len(row), obsCnt
)
)
continue
params = params + row
values.append(
(
yield from cur.mogrify(
(
'(%s, %s, %s, %s, ' +
', '.join(
["%s, 100"] * obsCnt
) + ')'
),
tuple(params)
)
).decode("utf-8")
)
if len(values)>0:
yield from self.begin()
print(
(
yield from cur.mogrify(
("""
INSERT INTO data._%s(
obs_id,
begin_time,
end_time,
result_time,
%s
)
""" % (
offering['name'].lower(),
", ".join(columns)
)) + (
" VALUES %s" % ", ".join(values)
)
)
).decode("utf-8")
)
yield from cur.execute(
("""
INSERT INTO data._%s(
obs_id,
begin_time,
end_time,
result_time,
%s
)
""" % (
offering['name'].lower(),
", ".join(columns)
)) + (
" VALUES %s" % ", ".join(values)
)
)
if offering['phenomenon_time'] is not None:
yield from cur.execute("""
UPDATE public.offerings
SET
pt_end=%s::TIMESTAMPTZ
WHERE id = %s;
""", (sampling_time, offering['id']))
else:
yield from cur.execute("""
UPDATE public.offerings
SET
pt_end=%s::TIMESTAMPTZ,
pt_begin=%s::TIMESTAMPTZ
WHERE id = %s;
""", (sampling_time, sampling_time, offering['id']))
yield from self.commit()
request['response'] = Response(
json_source=Response.get_template()
)
