Mercurial > hg > ChinaGisRestApi
view RestDbGisApi.py @ 278:4ade9b80b563 default tip
more cleanup.
descriptions work better now.
author | casties |
---|---|
date | Fri, 24 Feb 2012 16:41:30 +0100 |
parents | 55bc9972fb1b |
children |
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 time import datetime import urlFunctions from Products.ZDBInterface.WritableRestDbInterface import * def kmlEncode(s): """returns string encoded for displaying in KML attribute""" res = s.replace("'","__Apostroph__") res = res.replace('"','__DoubleApostroph__') res = res.replace(';','__$$__') res = res.replace('&','&') return res class RestDbGisApi(WritableRestDbInterface): """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()) KML_schema_table = PageTemplateFile('zpt/KML_schema_table', globals()) HTML_schema_usertables = PageTemplateFile('zpt/HTML_schema_usertables', globals()) # and scripts def KML_URL_schema_table(self,schema,table, useTimestamp=True, args=None): """KML_URL table function""" self.REQUEST.RESPONSE.setHeader("Content-Type", "text/plain") id = self.REQUEST.get('id',[]) doc = self.REQUEST.get('doc',None) # return self.getLiveKmlUrl(schema=schema,table=table, useTimestamp=useTimestamp) return self.getLiveKmlUrl(schema=schema,table=table, useTimestamp=False) # # database methods # toSqlTypeMap = { "text": "text", "number": "numeric", "id": "text", "gis_id": "text", "coord_lat": "numeric", "coord_lon": "numeric", "the_geom": "the_geom", "from_year":"text", "until_year":"text" } def getTableOwner(self,schema,table): """returns the owner of the table""" # what do we do with the schema? sql = 'select table_owner from public.gis_table_meta where table_name = %s' res = self.executeSQL(sql,(table,)) if len(res['rows']) > 0: return res['rows'][0][0] return None def isAllowed(self,action,schema,table,user=None,owner=None): """returns if the requested action on the table is allowed""" if user is None: user = self.REQUEST.get('AUTHENTICATED_USER',None) logging.debug("isAllowed action=%s schema=%s table=%s user=%s"%(action,schema,table,user)) if action == "create": if user is not None and str(user) != 'Anonymous User': # any authenticated user can create return True else: return False if action == "update": if owner is None: owner = self.getTableOwner(schema,table) logging.debug("isAllowed user=%s owner=%s"%(user,owner)) if user is not None and str(user) == str(owner): # update only your own table return True else: return False # anything else is allowed return True def setTableMetaTypes(self,schema,table,fields,user=None): """sets the GIS meta information for table""" if user is None: user = self.REQUEST.get('AUTHENTICATED_USER',None) logging.debug("settablemetatypes schema=%s, table=%s, fields=%s user=%s"%(schema,table,fields,user)) today = datetime.date.today().isoformat() res = self.executeSQL('select * from public.gis_table_meta where table_name = %s', (table,)) if len(res['rows']) > 0: # meta record exists sql = 'update public.gis_table_meta set table_owner=%s, table_modified=%s where table_name=%s' self.executeSQL(sql, (str(user),today,table), hasResult=False) else: # new meta record sql = 'insert into public.gis_table_meta (table_name,table_owner,table_created) values (%s,%s,%s)' self.executeSQL(sql, (table,str(user),today), hasResult=False) # update row info sql = 'delete from public.gis_table_meta_rows where table_name=%s' self.executeSQL(sql,(table,),hasResult=False) sql = 'insert into public.gis_table_meta_rows (table_name,field_name,gis_type) values (%s,%s,%s)' for f in fields: t = f['type'] fn = f['name'] self.executeSQL(sql, (table,fn,t), hasResult=False) def getListOfUserTables(self,schema='public',username='guest'): """return list of tables""" logging.debug("getlistofusertables") # get list of db tables # qstr = """SELECT t.table_name FROM information_schema.tables t, public.gis_table_meta m WHERE t.table_type = 'BASE TABLE' # AND t.table_schema = %s AND t.table_name = m.table_name ORDER BY 1""" qstr = """SELECT t.table_name FROM information_schema.tables t, public.gis_table_meta m WHERE t.table_schema = %s AND t.table_name = m.table_name ORDER BY 1""" data=self.executeSQL(qstr,(schema,)) return data def createEmptyTable(self,schema,table,fields): """create a table with the given fields returns list of created fields""" sqlFields = WritableRestDbInterface.createEmptyTable(self,schema,table,fields) if sqlFields is not None: self.setTableMetaTypes(schema,table,sqlFields) return sqlFields # TODO: move this def getAttributeNames(self,schema='public',table=None): return self.executeSQL("SELECT attname FROM pg_attribute, pg_class WHERE pg_class.oid = attrelid AND attnum>0 AND relname = %s", (table)) # TODO: move this def getAttributeTypes(self,schema='public',table=None): return self.executeSQL("SELECT field_name, gis_type FROM public.gis_table_meta_rows WHERE table_name = %s", (table)) # TODO: move back to inherited version def showTable(self,format='XML',schema='public',table=None,REQUEST=None,RESPONSE=None): """returns PageTemplate with tables""" logging.debug("showtable") if REQUEST is None: REQUEST = self.REQUEST queryArgs={'doc':None,'id':None} queryArgs['doc'] = REQUEST.get('doc') queryArgs['id'] = REQUEST.get('id') # should be cross-site accessible if RESPONSE is None: RESPONSE = self.REQUEST.RESPONSE RESPONSE.setHeader('Access-Control-Allow-Origin', '*') # everything else has its own template pt = getattr(self.template, '%s_schema_table'%format, REQUEST) if pt is None: return "ERROR!! template %s_schema_table not found at %s"%(format, self.template ) #data = self.getTable(schema,table) return pt(schema=schema,table=table,args=queryArgs) #TODO: move to parent class def getLiveUrl(self,schema,table,useTimestamp=True,REQUEST=None): if REQUEST is None: REQUEST = self.REQUEST logging.debug("getLiveUrl") baseUrl = self.absolute_url() timestamp = time.time() # filter parameters in URL and add to new URL params = [p for p in REQUEST.form.items() if p[0] not in ('format','timestamp')] params.append(('format','KML')) if useTimestamp: # add timestamp so URL changes every time params.append(('timestamp',timestamp)) paramstr = urllib.urlencode(params) return "%s/db/%s/%s?%s"%(baseUrl,schema,table,paramstr) # TODO: remove changes from parent version def getTable(self,schema='public',table=None,sortBy=1,username='guest'): """return table data""" logging.debug("gettable") attrNames=self.getAttributeNames(schema,table) attrTypes=self.getAttributeTypes(schema,table) attrString="" # try: for name in attrNames['rows']: logging.debug("name: %s"%name[0]) not_added=True if name[0] == "the_geom": #FJK: the table column is "the_geom" attrString=attrString+"ST_AsText("+name[0]+")," not_added=False break for a_iter in attrTypes['rows']: not_added = True logging.debug("attrTypes.field_name: %s"%a_iter[0]) if a_iter[0]==name[0]: logging.debug("attrTypes.gis_type: %s"%a_iter[1]) if a_iter[1] == "the_geom": #FJK: the table column is registered in gis_table_meta_rows as type "the_geom" attrString=attrString+"ST_AsText("+name[0]+")," not_added=False if not_added: if name[0].find('pg.dropped')==-1: attrString=attrString+name[0]+"," attrString=str(attrString).rsplit(",",1)[0] #to remove last "," if sortBy: data = self.executeSQL('select %s from "%s"."%s" order by %%s'%(attrString,sqlName(schema),sqlName(table)),(sortBy,)) else: data = self.executeSQL('select %s from "%s"."%s"'%(attrString,sqlName(schema),sqlName(table))) # except: # """ table does not exist """ # fields=self.get # self.createEmptyTable(schema, table, fields) return data def getLiveKmlUrl(self,schema,table,useTimestamp=True,REQUEST=None): return self.getLiveUrl(schema,table,useTimestamp,REQUEST) def getKmlData(self, schema, table, ids=None, sortBy=1, gisIdField=None, latField=None, lonField=None, geomField="point", colorField="red_big",from_year_name='from_year',until_year_name=''): """returns data structure for KML template""" logging.debug("getKMLdata gid=%s lat=%s lon=%s sortBy=%s geom=%s color=%s"%(gisIdField,latField,lonField,sortBy,geomField,colorField)) if geomField is None: geomField="point" if colorField is None: colorField="red" # Mapping a set of points from table-based SQL-query: qstr='SELECT * FROM "%s"."%s"'%(sqlName(schema),sqlName(table)) idList = None if ids is not None: qstr += ' WHERE ' if schema=='mpdl': qstr += 'mpdl_xmlsource_id IN (' else: qstr += 'CAST(id AS text) IN (' idList = ids.split(",") qstr += ','.join(['%s' for i in idList]) qstr += ')' if sortBy: # add sort clause if sortBy == 1: qstr += ' ORDER BY 1' elif sortBy == 'Default': qstr += ' ORDER BY 1' elif sortBy == 'undefined': qstr += ' ORDER BY 1' else: # TODO: proper quoting for names qstr += ' ORDER BY "%s"'%sortBy.replace('"','') bad_luck=True bl_counter=0 while (bad_luck): try: data = self.executeSQL(qstr,idList) bad_luck=False bl_counter=bl_counter+1 except: if (bl_counter<5): bad_luck=True else: bad_luck=False fieldMap = self.getFieldNameMap(data['fields']) geomdata=None if (geomField!="point"): # first check if the file is registered as geo-dataset (which then should have an attribute "the_geom") requeststring="select f_geometry_column from geometry_columns where f_table_schema=%s and f_table_name=%s " geocolumn_res=self.executeSQL(requeststring,(schema,table)) if len(geocolumn_res['rows'])>0: geocolumn=geocolumn_res['rows'][0][0] try: geomstr="select astext(st_simplify(transform(%s,4326),0.05)) from %s.%s"%(geocolumn,sqlName(schema),sqlName(table)) # the string variables have to be added here and not in executeSQL! geomdata=self.executeSQL(geomstr) teststr=geomdata.values()[1][0] if (teststr == (u'MULTIPOLYGON EMPTY',)): geomstr="select astext(st_simplify(transform(%s,4326),0.05)) from %s.%s"%(geocolumn,sqlName(schema),sqlName(table)) # the string variables have to be added here and not in executeSQL! geomdata=self.executeSQL(geomstr) except: try: geomstr="select chgis.astext(chgis.st_simplify(chgis.transform(%s,4326),0.05)) from %s.%s"%(geocolumn,sqlName(schema),sqlName(table)) # the string variables have to be added here and not in executeSQL! geomdata=self.executeSQL(geomstr) except: geomdata=None if (gisIdField is None) and (latField is None or lonField is None) and geomField=='point': # no fields given - choose automagically sql = "SELECT field_name FROM public.gis_table_meta_rows WHERE table_name = %s and gis_type = %s" # gis id in metadata first res = self.executeSQL(sql, (table,'gis_id')) if len(res['rows']) > 0: gisIdField = res['rows'][0][0] # latitude in metadata res = self.executeSQL(sql, (table,'coord_lat')) if len(res['rows']) > 0: latField = res['rows'][0][0] # longitude in metadata res = self.executeSQL(sql, (table,'coord_lon')) if len(res['rows']) > 0: lonField = res['rows'][0][0] if (gisIdField is None) and (latField is None or lonField is None) and geomField=='point': logging.warning("no entry in metadata table for table %s" % table) # still no fields - try field names if 'latitude' in fieldMap and 'longitude' in fieldMap: latField = 'latitude' lonField = 'longitude' elif 'x_coord' in fieldMap and 'y_coord' in fieldMap: latField = 'x_coord' lonField = 'y_coord' else: logging.error("getKMLdata unable to find position fields") self.REQUEST.RESPONSE.write("Please define Position field") return None # convert field names to row indexes gisIdIdx = fieldMap.get(gisIdField,None) latIdx = fieldMap.get(latField,None) lonIdx = fieldMap.get(lonField,None) the_geom=fieldMap.get("the_geom",None) logging.debug("gisidfield=%s idx=%s"%(gisIdField,gisIdIdx)) # convert data kmlData = [] geom_list = {} # try: # if geomField=='poly' or geomField=='line': # geom_list=geomdata.values()[1] # except: # return "no geomdata in RestDbGisApi Line 254" data_list=data['rows'] for k in range (len(data_list)): dataset = data_list[k] if len(geom_list)>k: geom_value = geom_list[k] if gisIdIdx != None and geomField!='point': gisID = dataset[gisIdIdx] geomdata = self.getLineForChGisId(gisID) # for dataset in data['rows']: xCoord = 0.0 yCoord = 0.0 if gisIdIdx != None and geomField=='point' : gisID = dataset[gisIdIdx] if gisID != " " and gisID != " 0": coords=self.getPointForChGisId(gisID) if coords!=None: xCoord=coords[0] yCoord=coords[1] elif latIdx != None: xCoord = dataset[lonIdx] yCoord = dataset[latIdx] elif latIdx != None: xCoord = dataset[lonIdx] yCoord = dataset[latIdx] elif geomField=='point' : logging.error("getKMLdata unable to find position") return None if geomField=='point' : if type(xCoord).__name__=='string': if float(xCoord) == 0.0: continue else: if xCoord == 0.0: continue if type(yCoord).__name__=='string': if float(yCoord) == 0.0: continue else: if yCoord == 0.0: continue kmlPlace = {} # description desc = '' timestring = '' beg_yr = '-9999' end_yr = '9999' from_year = '' until_year = '' sql = "SELECT field_name FROM public.gis_table_meta_rows WHERE table_name = %s and gis_type = %s" # from_year and until_year in metadata first try: res = self.executeSQL(sql, (table,from_year_name)) if len(res['rows']) > 0: from_year = res['rows'][0][0] except: from_year = "from_year_dummy" try: res = self.executeSQL(sql, (table,until_year_name)) if len(res['rows']) > 0: until_year = res['rows'][0][0] except: until_year = "until_year_dummy" #DW added for testing E4D with names from_year=from_year_name until_year=until_year_name logging.debug("from_year:"+from_year) logging.debug("until_year:"+until_year) for i in range (len(dataset)): value = dataset[i] name = data['fields'][i][0] #logging.debug("value=%s"%value) if name != 'the_geom': if value != None: #if name.find('name')>-1: # desc += "<name>%s</name>\n"%value # continue #elif name.find('place')>-1: # desc += "<name>%s</name>\n"%value # continue val = "%s: %s"%(name, value) logging.debug(name) value=unicode(value) if name == from_year: beg_yr= value if name == until_year: end_yr=value # If there is a link within the description data, create a valid href if value.find('http://')>-1: link_str_beg=value.find('http://') link_str_end = -1 link_str_end0=value.find(' ',link_str_beg) link_str_end1=value.find('>',link_str_beg) if link_str_end0 <link_str_end1: link_str_end=link_str_end0 else: link_str_end=link_str_end1 if link_str_end > -1: link_str=value[link_str_beg:link_str_end] val =name+": "+value[0:link_str_beg]+'<a href=' + link_str + '> ' + link_str.replace('http://','') + ' </a>' + value[link_str_end:] else: link_str=value[link_str_beg:] val =name+': '+value[0:link_str_beg]+'<a href=' + link_str + '> ' + link_str.replace('http://','') + ' </a>' #desc += kmlEncode(val) desc += val desc += '<br/>\n' #kmlPlace['description'] = "<![CDATA[%s]]>"%desc if end_yr!='9999': kmlPlace['TimeSpan0'] = '%s'%beg_yr kmlPlace['TimeSpan1'] = '%s'%end_yr else: kmlPlace['TimeStamp'] = '%s'%beg_yr if geomField=='point': #kmlPlace['description'] = "<![CDATA[%s]]>"%desc kmlPlace['description'] = desc kmlPlace['icon'] = '#marker_icon_'+colorField kmlPlace['coord_x'] = str(xCoord) kmlPlace['coord_y'] = str(yCoord) kmlPlace['coord_z'] = '50' kmlData.append(kmlPlace) if (geomField=='poly' or geomField=='line') and geomdata is not None: polys=str(geomdata).split('(') aaa=len(polys) for poly in polys: kmlPlace = {} kmlPlace['description'] = desc coords=poly.replace(')','').replace("'","").split(',') coord_string='' if len(coords)>1: for coord in coords: coord=coord.split(' ') try: x_coord=coord[0] y_coord=coord[1] except: break coord_string+=x_coord+','+y_coord+','+'0 ' if coord_string != '': kmlPlace['LinearRing']=coord_string kmlPlace['lineColor']='#'+colorField+'_'+geomField kmlData.append(kmlPlace) #logging.debug("kmlData=%s"%(repr(kmlData))) return kmlData def getPointForChGisId(self, gis_id): """returns coordinate pair for given gis_id""" # gets called by getKml def getPoint(id): str_gis_id=str(id).split('.')[0] sql="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id LIKE CAST('%s' AS text)"%str(str_gis_id) # logging.error("sql:",sql) # res = self.executeSQL(sql, (str(str_gis_id),)) res = self.executeSQL(sql) if len(res['rows']) > 0: return res['rows'][0] else: #logging.error("error on sql:",sql%(str(str_gis_id),)) return None if gis_id is None or gis_id == "": return None if len(str(gis_id)) < 4: return None # try gis_id coords = getPoint(gis_id) if coords is None: # try to clean gis_id... gis_id_short = re.sub(r'[^0-9]','',gis_id) # try again coords = getPoint(gis_id_short) if coords is None: #logging.error("CH-GIS ID %s not found!"%str(gis_id_short)) # this will ask the Harvard-Service for the Coords of this gis_id and write it into our coords-list try: coords=self.getCoordsFromREST_gisID(gis_id) #logging.error("coords from REST"%str(coords)) except: logging.error("coords from REST did not work for "%str(gis_id)) if coords[0] is None: logging.error("CH-GIS ID %s not found in Harvard"%str(gis_id)) else: try: SQL="INSERT INTO chgis.chgis_coords (gis_id,x_coord,y_coord) VALUES (CAST(%s AS text),CAST(%s AS numeric),CAST(%s AS numeric))" SQL_analyze="ANALYZE chgis.chgis_coords" self.executeSQL(SQL,(str(gis_id_short),str(coords[0][1]),str(coords[0][0])),False) self.executeSQL(SQL_analyze,(),False) except: logging.error("Could not write into chgis_coords: ", str(gis_id_short)) logging.error("coords[0][0]:"%coords[0][0]) logging.error("coords[0][1]:"%coords[0][1]) return coords[0] coords_test = getPoint(gis_id) #logging.error("SQL results now:", str(coords_test)) return coords[0] return coords def getLineForChGisId(self, gis_id): """returns line/poly coordinates for given gis_id""" # gets called by getKml def getLine(id): str_gis_id=str(id).split('.')[0] sql="SELECT astext(st_simplify(transform(the_geom,4326),0.05)) FROM chgis.chgis_linesandpolys WHERE gis_id LIKE CAST('%s' AS text)"%(str_gis_id) # logging.error("sql:",sql) # res = self.executeSQL(sql, (str(str_gis_id),)) res = self.executeSQL(sql) if len(res['rows']) > 0: return res['rows'][0] else: #logging.error("error on sql:",sql%(str(str_gis_id),)) return None if gis_id is None or gis_id == "": return None if len(str(gis_id)) < 4: return None # try gis_id line_coords = getLine(gis_id) if line_coords is None: # try to clean gis_id... gis_id_short = re.sub(r'[^0-9]','',gis_id) # try again line_coords = getLine(gis_id_short) if line_coords is None: logging.error("CH-GIS ID %s not found!"%(gis_id_short)) return line_coords ## legacy methods... def trydatahas_key(self,data,index,key_string): logging.debug("trying:"+key_string) try: return data[index].has_key(key_string) except: return 'false' # 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')