PySOS with Oracle (posting an email discussion)

[Bill]

Re-posting to user's list....

gisbert....@gkss.de wrote:
> Hi Bill,
> I played a little bit with config.py, sos.py and the GetCapabilty template. Now I get some results if I (or you) use
> http://kopc02.gkss.de/sos/sos.py
>
> It seems reasonable. Could you take a look on it and tell me what to do next.


These results for observedProperty seem a little funny, but I suspect
it's nothing you did:

('Pile_Jade1', 'Turbidity', 'Turbidity', None, 'Turbidity',
'urn:northsea:bodyofwater', 'FTU')

It seems like the entire tuple is being returned instead of an
individual value.

Would you mind pasting the contents of GetCapabilities.xml.template
into an email? I don't immediately see the problem on my end, and I
want to make sure we're looking at the same version.



Also, DescribeSensor calls appear to return a database error:

http://kopc02.gkss.de/sos/sos.py?request=DescribeSensor&SensorId=Pile_Jade1

Is there an Oracle connection class implemented?


> Best regards,
> Gisbert
> ____________________________________________________
> Gisbert Breitbach
> Institut für Küstenforschung, GKSS Forschungszentrum
> Tel.: +49 4152 87 1563
> Fax: +49 4152 87 4 1563
> E-Mail: Gisbert....@gkss.de
>
>
>
>
>
> Gisbert Breitbach/ko/gkss
> 13.11.2008 13:16
>
> To
> Bill Howe <ho...@stccmop.org>
> cc
> Luis Bermudez <berm...@sura.org>, Alex Jaramillo <jaram...@stccmop.org>, rolf.rie...@gkss.de
> Subject
> Re: Fwd: Participation at IOOS
>
>
>
>
>
> Hi Bill,
>
> Bill Howe <ho...@stccmop.org> wrote on 12.11.2008 19:08:31:
>
>> gisbert....@gkss.de wrote:
>>> Hi Bill,
>>> I created the views now.
>>> There are some differences. Until now we don't user standard names. I
> used
>>> our variable names as observedproperty and omitted the mmiuri column.
>> The "mmiuri" is a bit of misnomer. Your names need not actually be
>> mmiuris for PySOS to work, though it could help with interoperability
>> down the road.
>>
>> So, instead of leaving them null, populate that column with whatever
>> variable name you want to use.
>
>
> I populate it with the same content as observedproperty and variable.
>
>
>>> Also we have no description for the single variables.
>> Leave it blank for now, no problem.
>>
>>> For depth I took always the value zero. But this is not always true
> for
>>> example for the wind speed.
>> No problem.
>>
>>> And last until now I included only one measurement pile. But it should
> be
>>> no problem to add the other piles and buoys. Perhaps it will be a
> problem
>>> to include other measurements like Ferryboxes. But it should be
> solvable.
>> Sounds good!
>>
>> Do you want me to draft an Oracle connection object? I can't test it
>> without an oracle DB to hit, but it might help get you started.
>
> Yes that would be fine. I played around a little bit with pysos. For my URL
> http://kopc02.gkss.de/sos/sos.py
> I get an ORA-00933 error. The sos.py file which I use is included in this mail. Perhaps it is easier for you to find the needed corrections.
>
> Let me add one other possible problem. For this single pile we have 61 different variables which are not all useful for you. But there is no easy way to include only useful parameters. Also we have measured values nearly every 10 minutes since start. Is that a problem?
>
>
> Cheers,
> Gisbert
>
>
> from mod_python import apache, psp
> import sys, urllib, os
> import cx_Oracle
> import cStringIO
> import traceback
> os.environ["ORACLE_HOME"] = "/oracle/app/db_1"
>
> # If you see this in your browser, then # you need to configure Apache!
>
> # Uses mod_python's "publisher handler"
> # Make sure mod_python is installed, # and add this to httpd.conf:
>
> # <Directory /var/www/html/cmop/ws>
> # AddHandler mod_python .py
> # DirectoryIndex index.html index.py
> # PythonHandler mod_python.publisher
> # PythonDebug On
> # </Directory>
>
> # This may be replaced with more # sophisticated config file handling
>
> class SOSException(ValueError):
> exceptionreport = '''
> <ExceptionReport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="
> http://schemas.opengis.net/ows/1.0.0/owsExceptionReport.xsd" version="1.0.0" language="en">
> %s </ExceptionReport>
> '''
> exception = '''
> <Exception locator="service" exceptionCode="%s">
> %s </Exception>
> '''
> exceptiontext = '''<ExceptionText><![CDATA[
> %s
> ]]></ExceptionText>'''
>
> def __init__(self, code, msg, othermsgs = []):
> self.code = code
> self.msg = msg
> self.children = othermsgs
>
> def __str__(self):
> return self.ToXML()
>
> def __repr__(self):
> return self.ToXML()
>
> def AddText(self, text):
> self.children.append(text)
>
> def ToXML(self):
> exchildren = [self.exceptiontext % (c,) for c in self.children]
> extextlist = [self.exceptiontext % (self.msg,)] + exchildren
> extext = "\n".join(extextlist)
> body = self.exception % (self.code, extext)
> return self.exceptionreport % (body,)
>
> import config
> conf = config.config
>
> def info(msg): apache.log_error(msg, apache.APLOG_NOTICE)
>
> def debug(msg):
> apache.log_error(msg, apache.APLOG_NOTICE)
>
> def UnQuote(s):
> return urllib.unquote(s)
>
> def Quote(s):
> return urllib.quote(s, "/:")
>
> class Connection: # Change this class to use your preferred DB library
> def __init__(self, conf):
> # self.db = cx_Oracle.connect(conf['user'],conf['password'],cx_Oracle.makedsn(conf['host'],1521,conf['dbname']))
> self.db = cx_Oracle.connect("sosuser","sosuser",cx_Oracle.makedsn("kopc02.gkss.de",1521,"icon"))
> self.cursor=self.db.cursor()
> self.cursor.arraysize=50
>
> def getDicts(self,sql):
> response = self.cursor.execute(sql)
> return response.dictresult()
>
> def getTuples(self,sql):
> response = self.cursor.execute(sql)
> return response.getresult()
>
> def execCommand(self, sql):
> return self.cursor.execute(sql)
>
> # If you get a python error referencing pg_hba.conf, # then your permissions are not set up properly for postgres
> DB = Connection(conf)
>
> exceptionreport = '''
> <ExceptionReport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="
> http://schemas.opengis.net/ows/1.0.0/owsExceptionReport.xsd" version="1.0.0" language="en">
> <Exception locator="service" exceptionCode="%s">
> <ExceptionText><![CDATA[%s]]></ExceptionText>
> </Exception>
> </ExceptionReport>
> '''
>
> namespaces = {
> "xsi":"http://www.w3.org/2001/XMLSchema-instance", "swe":"http://www.opengis.net/swe/0", "gml":"http://www.opengis.net/gml", "sos":"http://www.opengis.net/sos/0", "om":"http://www.opengis.net/om", "ows":"http://www.opengeospatial.net/ows", "xlink":"http://www.w3.org/1999/xlink" }
>
> def getRequiredArgument(req, arg):
> a = getOptionalArgument(req, arg)
> if not a:
> raise TypeError("%s required" % (arg,))
> else:
> return a
>
> def urnKey(urn):
> # enforce the local urn prefix from the config file?
> urnprefix = conf["urnprefix"]
> #assert (urnprefix == urn[:len(urnprefix)])
> return urn.split(':')[-1]
>
> def getOptionalArgument(req, arg):
> a = req.form.get(arg, None)
> return a
>
> def FormatTime(attribute):
> return conf['timeformat'] % {'attribute':attribute}
>
> class TimeCondition:
> TItemplate = '''
> <gml:TimeInstant>
> %s
> </gml:TimeInstant>
>
> '''
> TPtemplate = '''
> <gml:TimePeriod gml:id="%s">
> %s
> %s
> </gml:TimePeriod>
> '''
>
> positiontemplate = '''<gml:%sPosition %s>%s</gml:%sPosition>'''
>
> indet = '''indeterminatePosition="%s"'''
>
> def __init__(self, eventTime, gmlid="DATA_TIME"):
> self.eventTime = eventTime
> self.gmlid = gmlid
>
> if self.eventTime:
> self.ParseEventTime()
> else:
> raise SOSException("eventTime specified, but no value received: '%s'" % (self.eventTime,))
>
> def ParseEventTime(self):
> ranges = [rng.split('/') for rng in self.eventTime.split(',')]
> self.instants = []
> self.periods = []
> for r in ranges:
> if len(r) == 2:
> self.periods.append(tuple(r))
> elif len(r) == 1:
> self.instants.append(r[0])
> else:
> raise SOSException("eventTime parameter should be a comma delimited list of time primitives, each of which can be of the form 'b/e' or 'i', where b, e, and i are times in ISO 8601. One element of this list, '%s' does not match this format." % ('/'.join(r),))
>
> def GMLPosition(self, type, time):
> if time == 'unknown' or time == 'now':
> itime = self.indet % (time,)
> time = ""
> elif (not time or time == 'None') and type == 'end':
> itime = self.indet % ('now',)
> time = ''
> elif time == 'None' or (not time):
> itime = self.indet % ('unknown',)
> time = ''
> else:
> itime = ""
> return self.positiontemplate % (type, itime, time, type)
>
> def AsGML(self):
> xml = ""
> for ti in self.instants:
> p = self.GMLPosition('time', ti)
> xml += self.TItemplate % (p,)
>
> for tp in self.periods:
> begin = self.GMLPosition('begin', tp[0])
> end = self.GMLPosition('end', tp[1])
> xml += self.TPtemplate % (self.gmlid, begin, end)
> return xml
>
> def AsSQL(self):
> periodstr = "(time BETWEEN '%s' AND '%s')"
> periodcond = " OR ".join([periodstr % r for r in self.periods]) or 'False'
> instantstr = "(time = '%s')"
> instantcond = " OR ".join([instantstr % r for r in self.instants]) or 'False'
> return " OR ".join([periodcond, instantcond])
>
> class TimeConditionLatest(TimeCondition):
> def __init__(self, offering, observedProperty):
> self.offering = offering
> self.observedProperty = observedProperty
> sql = conf['latesttime_query'] % (offering, observedProperty)
> maxt = DB.getTuples(sql)[0][0]
> if maxt:
> self.instants = [maxt]
> self.periods = []
> else:
> raise SOSException("NoData", "No data available for the requested ObservationOffering (%s) and observedProperty (%s)" % (offering, observedProperty))
>
> def index(req):
> #try:
> return restSOS(req)
> #except Exception, e:
> # req.content_type = "text/xml"
> # req.write("<error>Error: %s</error>" %(e,))
>
> def soapSOS(req):
> dispatch.AsHandler(modules=(sos,), request=req)
>
> class bufferProxy:
> # Adapted from
> # http://www.modpython.org/pipermail/mod_python/2004-November/016841.html
> # allows clearing output in case of error
> def __init__(self,outputBuffer):
> self.outputBuffer = outputBuffer
> def write(self,data,flush=None):
> self.outputBuffer.write(data)
>
> def restSOS(req):
> req.content_type = 'text/xml'
> req.headers_out['Cache-Control'] = 'no_cache'
> info("Received SOS Request: %s%s" % (req.hostname, req.unparsed_uri))
>
> # Adapted from
> # http://www.modpython.org/pipermail/mod_python/2004-November/016841.html
> # allows clearing output in case of error
> outputBuffer = cStringIO.StringIO()
> outputBufferProxy = bufferProxy(outputBuffer)
>
> oldReqWrite = req.write
>
> req.write = outputBufferProxy.write
>
> try:
> form = req.form
> if not form.has_key('request'):
> request = 'GetCapabilities'
> else:
> request = form['request']
>
> template = psp.PSP(req, filename = request+".template.xml")
> env = { 'req' : req }
> env.update(globals())
> env.update(conf)
> env.update(req.form)
> template.run(env)
>
> req.write = oldReqWrite
> result = outputBuffer.getvalue()
> outputBuffer.close()
> return result
>
> except SOSException, e:
> outputBuffer.seek(0)
> outputBuffer.truncate(0)
> req.write = oldReqWrite
> return e.ToXML()
>
> except Exception, e:
> outputBuffer.seek(0)
> outputBuffer.truncate(0)
> req.write = oldReqWrite
> othertext = traceback.format_exception(*sys.exc_info())
> return "%s" % (SOSException(e.__class__.__name__, e, othertext),)
>