Mercurial > hg > ChinaGisRestApi
view 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 source
''' 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')