Mercurial > hg > ChinaGisRestApi
diff RestDbGisApi.py @ 43:562717546168
refactoring...
RestDbGisApi and
RestDbJsonStore inherit from
RestDbInterface
author | casties |
---|---|
date | Thu, 02 Sep 2010 13:17:23 +0200 |
parents | |
children | c6c47034d2a4 |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/RestDbGisApi.py Thu Sep 02 13:17:23 2010 +0200 @@ -0,0 +1,363 @@ +''' +Created on 2.9.2010 + +@author: casties, fknauft +''' + +from OFS.Folder import Folder +from Products.PageTemplates.PageTemplateFile import PageTemplateFile +from Products.ZSQLExtend import ZSQLExtend +import logging +import re +import json +import time + +from RestDbInterface import * + + +gisToSqlTypeMap = { + "text": "text", + "number": "numeric", + "id": "text", + "gis_id": "text", + "coord_lat": "numeric", + "coord_lon": "numeric" + } + +class RestDbGisApi(RestDbInterface): + """Object for RESTful GIS database queries + path schema: /db/{schema}/{table}/ + omitting table gives a list of schemas + omitting table and schema gives a list of schemas + """ + + meta_type="RESTgis" + + # data templates + GIS_schema_table = PageTemplateFile('zpt/GIS_schema_table', globals()) + + def checkTableMetaPermission(self,action,schema,table,user=None): + """returns if the requested action on the table is allowed""" + logging.debug("checktablemetapermissions action=%s schema=%s table=%s user=%s"%(action,schema,table,user)) + if user is None: + user = self.REQUEST.get('AUTHENTICATED_USER',None) + logging.debug("user=%s"%user) + # TODO: what now? + return True + + def setTableMetaTypes(self,schema,table,fields): + """sets the GIS meta information for table""" + logging.debug("settablemetatypes schema=%s, table=%s, fields=%s"%(schema,table,fields)) + gisIdField = None + latField = None + lonField = None + for f in fields: + t = f['type'] + if t == 'gis_id': + gisIdField = f['name'] + elif t == 'coord_lat': + latField = f['name'] + elif t == 'coord_lon': + lonField = f['name'] + + res = self.executeSQL("select * from public.metadata where tablename=%s", (table,)) + if len(res['rows']) > 0: + # meta record exists + if gisIdField is not None: + self.executeSQL('update public.metadata set "attribute with gis_id" = %s where tablename = %s', (gisIdField,table), hasResult=False) + + else: + # new meta record + if gisIdField is not None: + self.executeSQL('insert into public.metadata ("tablename", "attribute with gis_id") values (%s, %s)', (table,gisIdField), hasResult=False) + + + def showTable(self,resultFormat='XML',schema='public',table=None,REQUEST=None,RESPONSE=None): + """returns PageTemplate with tables""" + logging.debug("showtable") + if REQUEST is None: + REQUEST = self.REQUEST + + # should be cross-site accessible + if RESPONSE is None: + RESPONSE = self.REQUEST.RESPONSE + + RESPONSE.setHeader('Access-Control-Allow-Origin', '*') + + # GIS gets special treatment + if resultFormat=="GIS": + id = REQUEST.get('id',[]) + doc = REQUEST.get('doc',None) + return self.showGoogleMap(schema=schema,table=table,id=id,doc=doc) + + elif resultFormat=="KML_URL": + id = REQUEST.get('id',[]) + doc = REQUEST.get('doc',None) + return self.getKmlUrl(schema=schema,table=table,id=id,doc=doc) + + # everything else has its own template + pt = getattr(self.template, '%s_schema_table'%resultFormat, None) + if pt is None: + return "ERROR!! template %s_schema_table not found"%resultFormat + + data = self.getTable(schema,table) + return pt(data=data,tablename=table) + + + def createEmptyTable(self,schema,table,fields): + """create a table with the given fields + returns list of created fields""" + logging.debug("createEmptyTable") + sqlFields = [] + for f in fields: + if isinstance(f,dict): + # {name: XX, type: YY} + name = sqlName(f['name']) + type = f['type'] + sqltype = gisToSqlTypeMap[type] + + else: + # name only + name = sqlName(f) + type = 'text' + sqltype = 'text' + + sqlFields.append({'name':name, 'type':type, 'sqltype':sqltype}) + + if self.checkTableMetaPermission("create", schema, table): + self.executeSQL('drop table if exists "%s"."%s"'%(schema,table),hasResult=False) + fieldString = ", ".join(['"%s" %s'%(f['name'],f['sqltype']) for f in sqlFields]) + sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString) + logging.debug("createemptytable: SQL=%s"%sqlString) + self.executeSQL(sqlString,hasResult=False) + self.setTableMetaTypes(schema,table,sqlFields) + return sqlFields + else: + logging.warning("create table not allowed!") + # throw exception? + return None + + + + # Methods for GoogleMaps creation + def showGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None): + logging.debug("showGoogleMap") + data = self.getDataForGoogleMap(schema,table,id,doc) + kmlFileName=self.getKMLname(data=data,table=table) + initializeStringForGoogleMaps="""onload=\"initialize(\'http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kmlFileName+"""\')\""""#+str(data) + initializeStringForGoogleMaps=initializeStringForGoogleMaps.replace("None","0") + googleMap_page=self.htmlHead()+str(self.getGoogleMapString(kml=initializeStringForGoogleMaps)) + return googleMap_page + + def getKmlUrl(self,schema='chgis',table='mpdl',id=[],doc=None): + logging.debug("getKmlUrl") + data = self.getDataForGoogleMap(schema,table,id,doc) + kml=self.getKMLname(data=data,table=table) + baseUrl = self.absolute_url() + return "%s/daten/%s"%(baseUrl,kml) + + def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None): + logging.debug("getDataForGoogleMap") + qstr="SELECT * FROM "+schema+"."+table + try: + if id!=[]: + qstr=qstr+" WHERE " + for id_item in id.split(","): + if table=='mpdl': + qstr=qstr+" mpdl_xmlsource_id = '"+id_item+ "' OR" + else: + qstr=qstr+" cast(id as text) LIKE '"+id_item+ "' OR" + qstr=str(qstr).rsplit(" ",1)[0] #to remove last " and " + data=self.ZSQLSimpleSearch(qstr) + return data + except: + return qstr + + def getKMLname(self,data=[],table=""): + logging.debug("getKMLname") + #session=context.REQUEST.SESSION + kml4Marker="<kml xmlns=\'http://www.opengis.net/kml/2.2\'><Document><Style id=\'marker_icon\'><IconStyle><scale>15</scale><Icon><href>http://chinagis.mpiwg-berlin.mpg.de/chinagis/images/dot_red.png</href></Icon></IconStyle></Style>\n" + initializeStringForGoogleMaps="" + #doLine=container.getVar('doLine') + # Mapping a set of points from table-based SQL-query: + if data!=None: + try: + SQL='SELECT "attribute with gis_id" FROM public.metadata WHERE tablename = %s' + res = self.executeSQL(SQL, (table,)) + gisIDattribute = res['rows'][0][0] + except: + return "table not registered within metadata" + + for dataset in data: + try: + xCoord=getattr(dataset,'longitude') + yCoord=getattr(dataset,'latitude') + except: + try: + xCoord=getattr(dataset,'x_coord') + yCoord=getattr(dataset,'y_coord') + except: + #try: + gisID=getattr(dataset,gisIDattribute) + coords=self.getPoint4GISid(gisID) + if coords!=None: + xCoord=coords[0] + yCoord=coords[1] + # except: + # return "no coordinates found" + + if float(xCoord)!=0: + if float(yCoord)!=0: + kml4Marker=kml4Marker+"<Placemark>" + kml4Marker=kml4Marker+"<description> <![CDATA[<b>" + for values in dataset: + if values != (None, None): + if str(values).find('name')>-1: + kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n" + continue + elif str(values).find('place')>-1: + kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n" + continue + + kml4Marker=kml4Marker+str(values)+": " + attribute_string=str(values).replace("'","__Apostroph__") + attribute_string=str(attribute_string).replace('"','__DoubleApostroph__') + attribute_string=str(attribute_string).replace(';','__$$__') + attribute_string=str(attribute_string).replace('&','&') + if str(attribute_string).find('http')>-1: + attribute_string='<A HREF=' + str(attribute_string) + ' target=_blank>' + str(attribute_string) + '</A>' + kml4Marker=kml4Marker+attribute_string+"</a><br>\n" + + kml4Marker=kml4Marker+"]]></description>\n" + kml4Marker=kml4Marker+"<styleURL>#marker_icon</styleURL>\n" + kml4Marker=kml4Marker+"<Point>" + + kml4Marker=kml4Marker+"<coordinates>"+str(xCoord)+","+str(yCoord)+",0</coordinates>\n" + kml4Marker=kml4Marker+"</Point>\n" + kml4Marker=kml4Marker+"</Placemark>\n" + + kml4Marker=kml4Marker+"</Document>\n</kml>" + kmlFileName="marker"+str(time.time())+".kml" + + #kml4Marker=str(kml4Marker).replace('&','$$') + #kml4Marker=str(kml4Marker).replace(';','__$$__') + #kml4Marker=str(kml4Marker).replace('#','__SHARP__') + isLoadReady='false' + while isLoadReady=='false': + isLoadReady=self.RESTwrite2File(self.daten,kmlFileName,kml4Marker) + + return kmlFileName + + def getGoogleMapString(self,kml): + logging.debug("getGoogleMapString") + printed= '<body %s> '%kml +"""\n <div id="map_canvas" style="width: 98%; height: 95%"> </div> \n </body>" \n </html>""" + return printed + + def getPoint4GISid(self,gis_id): + j=0 + coords=(0,0) + if gis_id != None: + while (True): + j=j+1 + if (j>100): # FJK: just to prevent endless loops + break + if (gis_id.isdigit()): # FJK: regular exit from while-loop + break + else: + gis_id=gis_id.strip('abcdefghijklmnopqrstuvwxyz_') # FJK: to strip all letters + gis_id=gis_id.strip() # FJK: to strip all whitespaces + resultpoint = [0,0] + results = None + try: + if int(gis_id)>0: + SQL="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id LIKE cast("+ str(gis_id) +" as text);" + results=self.ZSQLSimpleSearch(SQL) + #print results + if results != None: + for result in results: + resultpoint=[getattr(result,str('x_coord')),getattr(result,str('y_coord'))] + if resultpoint !=[0,0]: + return resultpoint + else: + coords=self.getCoordsFromREST_gisID(joinid) + SQL="INSERT INTO chgis.chgis_coords (gis_id,x_coord,y_coord) VALUES (" +gis_id+ "," +coords[0][1]+ "," +coords[0][0]+ "); ANALYZE chgis.chgis_coords;" + returnstring=self.ZSQLSimpleSearch(SQL) + return coords[0] + except: + return "gis_id not to interpretable:"+str(gis_id) + else: + return coords[0] + + def getCoordsFromREST_gisID(self,gis_id): + coordlist=[] + i=0 + while (i<5 and coordlist==[]): + + urlresponse=container.urlFunctions.zUrlopenParseString(container.urlFunctions.zUrlopenRead("http://chgis.hmdc.harvard.edu/xml/id/"+gis_id)) + baseDocElement=container.urlFunctions.zUrlopenDocumentElement(urlresponse) + childnodes=container.urlFunctions.zUrlopenChildNodes(baseDocElement) + itemnodes=container.urlFunctions.zUrlopenGetElementsByTagName(baseDocElement,'item') + + for i in range(0,container.urlFunctions.zUrlopenLength(itemnodes)): + itemnode=container.urlFunctions.zUrlopenGetItem(itemnodes,i) + itemspatialnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemnode,'spatial') + for j in range(0,container.urlFunctions.zUrlopenLength(itemspatialnodes)): + coord=[] + itemspatialnode= container.urlFunctions.zUrlopenGetItem(itemspatialnodes,j) + itemspatiallatnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_latitude') + for k in range(0,container.urlFunctions.zUrlopenLength(itemspatiallatnodes)): + itemspatiallatnode= container.urlFunctions.zUrlopenGetItem(itemspatiallatnodes,k) + coord.append(container.urlFunctions.zUrlopenGetTextData(itemspatiallatnode)) + itemspatiallngnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_longitude') + for k in range(0,container.urlFunctions.zUrlopenLength(itemspatiallngnodes)): + itemspatiallngnode= container.urlFunctions.zUrlopenGetItem(itemspatiallngnodes,k) + coord.append(container.urlFunctions.zUrlopenGetTextData(itemspatiallngnode)) + coordlist.append(coord) + gis_id= "_"+gis_id + return coordlist + +# End for GoogleMaps creation + + def RESTwrite2File(self,datadir, name,text): + logging.debug("RESTwrite2File datadir=%s name=%s"%(datadir,name)) + try: + import cStringIO as StringIO + except: + import StringIO + + # make filehandle from string + textfile = StringIO.StringIO(text) + fileid=name + if fileid in datadir.objectIds(): + datadir.manage_delObjects(fileid) + fileInZope=datadir.manage_addFile(id=fileid,file=textfile) + return "Write successful" + + def manage_editRestDbGisApi(self, title=None, connection_id=None, + REQUEST=None): + """Change the object""" + if title is not None: + self.title = title + + if connection_id is not None: + self.connection_id = connection_id + + #checkPermission=getSecurityManager().checkPermission + REQUEST.RESPONSE.redirect('manage_main') + + +manage_addRestDbGisApiForm=PageTemplateFile('zpt/addRestDbGisApi',globals()) + +def manage_addRestDbGisApi(self, id, title='', label='', description='', + createPublic=0, + createUserF=0, + REQUEST=None): + """Add a new object with id *id*.""" + + ob=RestDbGisApi(str(id),title) + self._setObject(id, ob) + + #checkPermission=getSecurityManager().checkPermission + REQUEST.RESPONSE.redirect('manage_main') + +