Mercurial > hg > ChinaGisRestApi
changeset 76:e0a79d926902
Path for kml included
| author | fknauft |
|---|---|
| date | Tue, 19 Oct 2010 12:56:34 +0200 |
| parents | 2f4c427dec44 |
| children | 9c66c0ab395c |
| files | RestDbGisApi.py RestDbInterface.py zpt/KML_schema_table.zpt |
| diffstat | 3 files changed, 495 insertions(+), 380 deletions(-) [+] |
line wrap: on
line diff
--- a/RestDbGisApi.py Wed Sep 29 21:09:44 2010 +0200 +++ b/RestDbGisApi.py Tue Oct 19 12:56:34 2010 +0200 @@ -282,7 +282,11 @@ return kmlData - + def getKmlData4Path(self, schema, table, ids=None, gisIdField=None, latField=None, lonField=None, doLine=False): + if doLine: + return here.getKmlData(schema=schema, table=table,); + return None; + def getKMLname(self,data=[],table=""): logging.debug("getKMLname") #session=context.REQUEST.SESSION @@ -429,6 +433,91 @@ # End for GoogleMaps creation + + def getKMLname(self,data=[],table=""): + logging.debug("getKMLname") + kml4Marker=self.kml_header() + initializeStringForGoogleMaps="" + gisID="" + xCoord=0 + yCoord=0 + SQL="" + kmlFileName="marker"+str(time.time())+".kml" + # 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 LIKE %r" % (table) + gisIDattribute=self.ZSQLSimpleSearch(SQL) + 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: + continue2getPoint4GISid="" + try: + if float(xCoord)==0 and float(yCoord)==0: + gisID=getattr(dataset,getattr(gisIDattribute[0],'attribute with gis_id')) + coords=self.getPoint4GISid(gisID) + if coords!=None: + xCoord=coords[0] + yCoord=coords[1] + except: + kml4Marker=kml4Marker + "No Coordinates for:"+gisID +"\n" + try: + markerTitel + if float(xCoord)!=0: + if float(yCoord)!=0: + kml4Marker+="\n<Placemark>\n" + kml4Marker+="<description>\n<![CDATA[<b>" + for values in dataset: + if values != (None, None): + if str(values).find('name')>-1: + markerTitel="<name>%s</name>\n" % (values[1]) + continue + elif str(values).find('place')>-1: + markerTitel="<name>%s</name>\n" % (values[1]) + 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=%r target=_blank>Link to Document</A>' % (attribute_string) + kml4Marker+=attribute_string+"</a><br>\n" + + kml4Marker+="]]>\n</description>\n" + kml4Marker+="<styleUrl>#msn_dot_red</styleUrl>\n" + kml4Marker+="<Point>" + + kml4Marker+="<coordinates>%s,%s,0</coordinates>" % (xCoord,yCoord) + kml4Marker+="</Point>\n" + kml4Marker+= markerTitel + kml4Marker+="</Placemark>\n" + except: + kml4Marker +="Was not able to create this marker: %s : %s, %s \n"%(gisID,xCoord,yCoord) + + kml4Marker=kml4Marker+"</Document>\n</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 RESTwrite2File(self,datadir, name,text): logging.debug("RESTwrite2File datadir=%s name=%s"%(datadir,name)) try:
--- a/RestDbInterface.py Wed Sep 29 21:09:44 2010 +0200 +++ b/RestDbInterface.py Tue Oct 19 12:56:34 2010 +0200 @@ -8,9 +8,6 @@ from Products.PageTemplates.PageTemplateFile import PageTemplateFile from Products.ZSQLExtend import ZSQLExtend import logging -import re -import psycopg2 -import json import time from zope.interface import implements @@ -23,43 +20,6 @@ from Products.ZSQLMethods.SQL import SQLConnectionIDs -def getTextFromNode(node): - """get the cdata content of a XML node""" - if node is None: - return "" - - if isinstance(node, list): - nodelist = node - else: - nodelist=node.childNodes - - rc = "" - for node in nodelist: - if node.nodeType == node.TEXT_NODE: - rc = rc + node.data - return rc - -def sqlName(s,lc=True): - """returns restricted ASCII-only version of string""" - if s is None: - return "" - - # all else -> "_" - s = re.sub(r'[^A-Za-z0-9_]','_',s) - if lc: - return s.lower() - - return s - -gisToSqlTypeMap = { - "text": "text", - "number": "numeric", - "id": "text", - "gis_id": "text", - "coord_lat": "numeric", - "coord_lon": "numeric" - } - class RestDbInterface(Folder): """Object for RESTful database queries path schema: /db/{schema}/{table}/ @@ -83,25 +43,8 @@ HTML_index = PageTemplateFile('zpt/HTML_index', globals()) HTML_schema = PageTemplateFile('zpt/HTML_schema', globals()) HTML_schema_table = PageTemplateFile('zpt/HTML_schema_table', globals()) - JSONHTML_index = PageTemplateFile('zpt/JSONHTML_index', globals()) - JSONHTML_schema = PageTemplateFile('zpt/JSONHTML_schema', globals()) - JSONHTML_schema_table = PageTemplateFile('zpt/JSONHTML_schema_table', globals()) - # JSON_* templates are scripts - def JSON_index(self): - """JSON index function""" - self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json") - json.dump(self.getListOfSchemas(), self.REQUEST.RESPONSE) - def JSON_schema(self,schema): - """JSON index function""" - self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json") - json.dump(self.getListOfTables(schema), self.REQUEST.RESPONSE) - - def JSON_schema_table(self,schema,table): - """JSON index function""" - self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json") - json.dump(self.getTable(schema, table), self.REQUEST.RESPONSE) - + def __init__(self, id, title, connection_id=None): """init""" @@ -111,17 +54,11 @@ self.connection_id = connection_id # create template folder self.manage_addFolder('template') - + # create data folder + #self.manage_addFolder('daten') - def getRestDbUrl(self): - """returns url to the RestDb instance""" - return self.absolute_url() - - def getJsonString(self,object): - """returns a JSON formatted string from object""" - return json.dumps(object) - def getCursor(self,autocommit=True): + def getCursor(self): """returns fresh DB cursor""" conn = getattr(self,"_v_database_connection",None) if conn is None: @@ -145,59 +82,30 @@ raise IOError("No database connection! (%s)"%str(e)) cursor = conn.getcursor() - if autocommit: - # is there a better version to get to the connection? - cursor.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) - return cursor - def getFieldNameMap(self,fields): - """returns a dict mapping field names to row indexes""" - map = {} - i = 0 - for f in fields: - map[f[0]] = i - i += 1 - - return map - - def executeSQL(self, query, args=None, hasResult=True, autocommit=True): + def executeSQL(self, query, *args): """execute query with args on database and return all results. result format: {"fields":fields, "rows":data}""" - logging.debug("executeSQL query=%s args=%s"%(query,args)) - cur = self.getCursor(autocommit=autocommit) - if args is not None: - # make sure args is a list - if isinstance(args,basestring): - args = (args,) - + cur = self.getCursor() cur.execute(query, args) # description of returned fields fields = cur.description - if hasResult: - # get all data in an array - data = cur.fetchall() - cur.close() - #logging.debug("fields: %s"%repr(fields)) - logging.debug("rows: %s"%repr(data)) - return {"fields":fields, "rows":data} - else: - cur.close() - return None + # get all data in an array + data = cur.fetchall() + cur.close() + return {"fields":fields, "rows":data} + def publishTraverse(self,request,name): """change the traversal""" # get stored path path = request.get('restdb_path', []) logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path)) - if name in ("index_html", "PUT"): + if name == "index_html": # end of traversal - if request.get("method") == "POST" and request.get("action",None) == "PUT": - # fake PUT by POST with action=PUT - name = "PUT" - - return getattr(self, name) + return self.index_html #TODO: should we check more? else: # traverse @@ -217,138 +125,80 @@ # continue traversing return self - - + def index_html(self,REQUEST,RESPONSE): """index method""" # ReST path was stored in request path = REQUEST.get('restdb_path',[]) # type and format are real parameter - resultFormat = REQUEST.get('format','HTML').upper() - queryType = REQUEST.get('type',None) + format = REQUEST.get('format','HTML').upper() + type = REQUEST.get('type',None) - logging.debug("index_html path=%s resultFormat=%s queryType=%s"%(path,resultFormat,queryType)) + # id and doc are used for GoogleMaps content + id = REQUEST.get('id',[]) + doc = REQUEST.get('doc',None) + + logging.debug("index_html path=%s format=%s type=%s"%(path,format,type)) + RESPONSE.setHeader('Access-Control-Allow-Origin', '*') - if queryType is not None: - # non-empty queryType -- look for template - pt = getattr(self.template, "%s_%s"%(resultFormat,queryType), None) + if type is not None: + # non-empty type -- look for template + pt = getattr(self.template, "%s_%s"%(format,type), None) if pt is not None: - return pt(format=resultFormat,type=queryType,path=path) + return pt(format=format,type=type,path=path) if len(path) == 1: # list of schemas - return self.showListOfSchemas(resultFormat=resultFormat) + return self.showListOfSchemas(format=format) elif len(path) == 2: # list of tables - return self.showListOfTables(resultFormat=resultFormat,schema=path[1]) + return self.showListOfTables(format=format,schema=path[1]) elif len(path) == 3: + # GIS + if format=="GIS": + return self.showGoogleMap(schema=path[1],table=path[2],id=id,doc=doc) + if format=="KML_URL": + return self.getKmlUrl(schema=path[1],table=path[2],id=id,doc=doc) # table - if REQUEST.get("method") == "POST" and REQUEST.get("create_table_file",None) is not None: - # POST to table to check - return self.checkTable(resultFormat=resultFormat,schema=path[1],table=path[2]) - # else show table - return self.showTable(resultFormat=resultFormat,schema=path[1],table=path[2]) + return self.showTable(format=format,schema=path[1],table=path[2]) # don't know what to do return str(REQUEST) - def PUT(self, REQUEST, RESPONSE): - """ - Implement WebDAV/HTTP PUT/FTP put method for this object. - """ - logging.debug("RestDbInterface PUT") - #logging.debug("req=%s"%REQUEST) - #self.dav__init(REQUEST, RESPONSE) - #self.dav__simpleifhandler(REQUEST, RESPONSE) - # ReST path was stored in request - path = REQUEST.get('restdb_path',[]) - if len(path) == 3: - schema = path[1] - tablename = path[2] - file = REQUEST.get("create_table_file",None) - if file is None: - RESPONSE.setStatus(400) - return - fields = None - fieldsStr = REQUEST.get("create_table_fields",None) - logging.debug("put with schema=%s table=%s file=%s fields=%s"%(schema,tablename,file,repr(fieldsStr))) - if fieldsStr is not None: - # unpack fields - fields = [{"name":n, "type": t} for (n,t) in [f.split(":") for f in fieldsStr.split(",")]] - - ret = self.createTableFromXML(schema, tablename, file, fields) - # return the result as JSON - format = REQUEST.get("format","JSON") - if format == "JSON": - RESPONSE.setHeader("Content-Type", "application/json") - json.dump(ret, RESPONSE) - - elif format == "JSONHTML": - RESPONSE.setHeader("Content-Type", "text/html") - RESPONSE.write("<html>\n<body>\n<pre>") - json.dump(ret, RESPONSE) - RESPONSE.write("</pre>\n</body>\n</html>") - - else: - # 400 Bad Request - RESPONSE.setStatus(400) - return - - def showTable(self,resultFormat='XML',schema='public',table=None,REQUEST=None,RESPONSE=None): + def showTable(self,format='XML',schema='public',table=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', '*') + pt = getattr(self.template, '%s_schema_table'%format, None) + if pt is None: + return "ERROR!! template %s_schema_table not found"%format - # 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(schema=schema,table=table) + data = self.getTable(schema,table) + return pt(data=data,tablename=table) + def getTable(self,schema='public',table=None,username='guest'): """return table data""" logging.debug("gettable") - data = self.executeSQL('select * from "%s"."%s"'%(schema,table)) + data = self.executeSQL("select * from %s.\"%s\" "%(schema,table)) return data - def hasTable(self,schema='public',table=None,username='guest'): - """return if table exists""" - logging.debug("hastable") - data = self.executeSQL('select 1 from information_schema.tables where table_schema=%s and table_name=%s',(schema,table)) - ret = bool(data['rows']) - return ret - - def showListOfTables(self,resultFormat='XML',schema='public',REQUEST=None,RESPONSE=None): + def showListOfTables(self,format='XML',schema='public'): """returns PageTemplate with list of tables""" logging.debug("showlistoftables") - # should be cross-site accessible - if RESPONSE is None: - RESPONSE = self.REQUEST.RESPONSE - RESPONSE.setHeader('Access-Control-Allow-Origin', '*') - - pt = getattr(self.template, '%s_schema'%resultFormat, None) + pt = getattr(self.template, '%s_schema'%format, None) if pt is None: - return "ERROR!! template %s_schema not found"%resultFormat + return "ERROR!! template %s_schema not found"%format - #data = self.getListOfTables(schema) - return pt(schema=schema) + data = self.getListOfTables(schema) + return pt(data=data,schema=schema) def getListOfTables(self,schema='public',username='guest'): """return list of tables""" logging.debug("getlistoftables") # get list of fields and types of db table - qstr="""SELECT c.relname AS tablename FROM pg_catalog.pg_class c + qstr="""select c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid)""" @@ -356,203 +206,360 @@ data=self.executeSQL(qstr) return data - def showListOfSchemas(self,resultFormat='XML',REQUEST=None,RESPONSE=None): + def showListOfSchemas(self,format='XML'): """returns PageTemplate with list of schemas""" logging.debug("showlistofschemas") - # should be cross-site accessible - if RESPONSE is None: - RESPONSE = self.REQUEST.RESPONSE - RESPONSE.setHeader('Access-Control-Allow-Origin', '*') - - pt = getattr(self.template, '%s_index'%resultFormat, None) + pt = getattr(self.template, '%s_index'%format, None) if pt is None: - return "ERROR!! template %s_index not found"%resultFormat + return "ERROR!! template %s_index not found"%format - #data = self.getListOfSchemas() - return pt() + data = self.getListOfSchemas() + return pt(data=data) def getListOfSchemas(self,username='guest'): """return list of schemas""" logging.debug("getlistofschemas") # TODO: really look up schemas - data={'fields': (('schemas',),), 'rows': [('public',),]} + data={'fields': (('schemas',),), 'rows': [('public','chgis','mpdl'),]} return data - - def checkTable(self,resultFormat,schema,table,REQUEST=None,RESPONSE=None): - """check the table. - returns valid data fields and table name.""" - if REQUEST is None: - REQUEST = self.REQUEST - RESPONSE = REQUEST.RESPONSE - - file = REQUEST.get("create_table_file",None) - res = self.checkTableFromXML(schema, table, file) - logging.debug("checkTable result=%s"%repr(res)) - # return the result as JSON - if resultFormat == "JSON": - RESPONSE.setHeader("Content-Type", "application/json") - json.dump(res, RESPONSE) - - elif resultFormat == "JSONHTML": - RESPONSE.setHeader("Content-Type", "text/html") - RESPONSE.write("<html>\n<body>\n<pre>") - json.dump(res, RESPONSE) - RESPONSE.write("</pre>\n</body>\n</html>") - - else: - return "ERROR: invalid resultFormat" - def checkTableFromXML(self,schema,table,data,REQUEST=None,RESPONSE=None): - """check the table with the given XML data. - returns valid data fields and table name.""" - logging.debug("checkTableFromXML schema=%s table=%s"%(schema,table)) - # clean table name - tablename = sqlName(table) - tableExists = self.hasTable(schema, table) - if data is None: - fieldNames = [] - else: - # get list of field names from upload file - fields = self.importExcelXML(schema,tablename,data,fieldsOnly=True) - - res = {'tablename': tablename, 'table_exists': tableExists} - res['fields'] = fields - return res - - 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}) - - 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 + # Methods for GoogleMaps creation + def showGoogleMap(self,schema='public',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=str(self.htmlHead())+str(self.getGoogleMapString(initializeStringForGoogleMaps)) + return googleMap_page + + def kml_header(self): + return """<?xml version=\"1.0\" encoding=\"UTF-8\"?> + <kml + xmlns=\"http://www.opengis.net/kml/2.2\" + xmlns:gx=\"http://www.google.com/kml/ext/2.2\" + xmlns:kml=\"http://www.opengis.net/kml/2.2\" + xmlns:atom=\"http://www.w3.org/2005/Atom\">" + <Document> + <open>1</open> + <Style id="sh_dot_red"> + <IconStyle> + <scale>1.2</scale> + <Icon> + <href>http://chinagis.mpiwg-berlin.mpg.de/chinagis/images/dot_red.png</href> + </Icon> + </IconStyle> + <ListStyle> + </ListStyle> + </Style> + <Style id="sn_dot_red"> + <IconStyle> + <Icon> + <href>http://chinagis.mpiwg-berlin.mpg.de/chinagis/images/dot_red.png</href> + </Icon> + </IconStyle> + <ListStyle> + </ListStyle> + </Style> + <StyleMap id="msn_dot_red"> + <Pair> + <key>normal</key> + <styleUrl>#sn_dot_red</styleUrl> + </Pair> + <Pair> + <key>highlight</key> + <styleUrl>#sh_dot_red</styleUrl> + </Pair> + </StyleMap> +""" - def createTableFromXML(self,schema,table,data, fields=None): - """create or replace a table with the given XML data""" - logging.debug("createTableFromXML schema=%s table=%s data=%s fields=%s"%(schema,table,data,fields)) - tablename = sqlName(table) - self.importExcelXML(schema, tablename, data, fields) - return {"tablename": tablename} - - def importExcelXML(self,schema,table,xmldata,fields=None,fieldsOnly=False): - ''' - Import XML file in Excel format into the table - @param table: name of the table the xml shall be imported into - ''' - from xml.dom.pulldom import parseString,parse - namespace = "urn:schemas-microsoft-com:office:spreadsheet" - containerTagName = "Table" - rowTagName = "Row" - colTagName = "Cell" - dataTagName = "Data" - xmlFields = [] - sqlFields = [] - numFields = 0 - sqlInsert = None - - logging.debug("import excel xml") - - ret="" - if isinstance(xmldata, str): - logging.debug("importXML reading string data") - doc=parseString(xmldata) - else: - logging.debug("importXML reading file data") - doc=parse(xmldata) + def htmlHead(self): + return """ <!DOCTYPE html> +<html> + <head> + <meta http-equiv="content-type" content="text/html; charset=utf-8"> + <meta http-Equiv="Cache-Control" Content="no-cache"> + <meta http-Equiv="Pragma" Content="no-cache"> + <meta http-Equiv="Expires" Content="0"> + <link rel="stylesheet" type="text/css" + href="../../../../mpiwgstyles.css"> + <style type="text/css"> + html { height: 100% } + body { height: 100%; margin: 0px; padding: 0px } + #map_canvas { height: 100% } + </style> + <style type="text/css"> + td {height:10; font-size:12;} + th {height:10; font-size:12;} + b {height:10; font-size:12;} + h4 {height:10; font-size:15;} + </style> + <style type="text/css"> + .tooltip { + background-color:#ffffff; + font-weight:bold; + border:2px #006699 solid; + } + </style> + + + <script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=false"> </script> + + <script type="text/javascript"> + var map = null; + function initialize(data) { + kmlURL=initialize.arguments[0]; + var myMapOpt = { + zoom: 5, + center: new google.maps.LatLng(29,116), + mapTypeId: google.maps.MapTypeId.TERRAIN + }; + var map = new google.maps.Map(document.getElementById("map_canvas"), myMapOpt); + var myLayerOpt = { + map: map, + preserveViewport: false + }; + var newLayer=new google.maps.KmlLayer(kmlURL, myLayerOpt); + } + </script> + </head> """ + + def getKmlUrl(self,schema='public',table='mpdl',id=[],doc=None): + logging.debug("getKmlUrl") + data = self.getDataForGoogleMap(schema,table,id,doc) + kml=self.getKMLname(data=data,table=table) + return """http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kml + + def getDataForGoogleMap(self,schema='public',table='mpdl',id=[],doc=None): + logging.debug("getDataForGoogleMap") + qstr="SELECT * FROM "+schema+".\""+table+"\"" + data={} + try: + if id!=[]: + qstr=qstr+" WHERE " + for id_item in id.split(","): + if schema=='mpdl' or table.find('mpdl')>-1: + 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 " OR " + data=self.ZSQLSimpleSearch(qstr) + return data + except: + return qstr - cnt = 0 - while True: - node=doc.getEvent() + def getAttributeList(self, table=""): + attributeList={} + if table != "": + sql_for_attributes="SELECT column_name FROM information_schema.columns WHERE table_name = '"+ str(table) + "' AND column_name NOT LIKE 'the_geom'" + # print sql_for_attributes + results=self.ZSQLSimpleSearch(sql_for_attributes) + # print results + for result in results: + if attributeList=={}: + attributeList=[getattr(result,"column_name")] + else: + attributeList.append(getattr(result,"column_name")) + return attributeList - if node is None: - break - - else: - #logging.debug("tag=%s"%node[1].localName) - if node[1].localName is not None: - tagName = node[1].localName.lower() + + def getKMLname(self,data=[],table=""): + logging.debug("getKMLname") + kml4Marker=self.kml_header() + initializeStringForGoogleMaps="" + gisID="" + xCoord=0 + yCoord=0 + SQL="" + unknownPointCounter=0 + kmlFileName="marker"+str(time.time())+".kml" + attributeList=self.getAttributeList(table) + # 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 LIKE %r" % (table) + gisIDattribute=self.ZSQLSimpleSearch(SQL) + except: + return "table not registered within metadata" + for dataset in data: + try: + xCoord=getattr(dataset,'longitude') + yCoord=getattr(dataset,'latitude') + if str(xCoord)=='None': + xCoord=0 + if str(yCoord)=='None': + yCoord=0 + except: + try: + xCoord=getattr(dataset,'x_coord') + yCoord=getattr(dataset,'y_coord') + if str(xCoord)=='None': + xCoord=0 + if str(yCoord)=='None': + yCoord=0 + except: + continue2getPoint4GISid="" + try: + if float(xCoord)==0 and float(yCoord)==0: + gisID=getattr(dataset,getattr(gisIDattribute[0],'attribute with gis_id')) + coords=self.getPoint4GISid(gisID) + if coords!=None: + xCoord=coords[0] + yCoord=coords[1] + except: + kml4Marker=kml4Marker + "No Coordinates for:"+gisID +", "+ str(xCoord)+", "+ str(xCoord)+"\n" + markerTitel="" + kml4Marker+="\n<Placemark>\n" + kml4Marker+="<description>\n<![CDATA[<b>" + markerTitel="<name>%s</name>\n" % (dataset[2]) + + i=0 + for value in dataset: + + # if values != (None, None): + # if str(values).find('name')>-1: + # markerTitel="<name>%s</name>\n" % (values[1]) + # continue + # elif str(values).find('place')>-1: + # markerTitel="<name>%s</name>\n" % (values[1]) + # continue + + # kml4Marker=kml4Marker+": "+str(value) + value_string=str(value).replace("'","__Apostroph__") + value_string=str(value_string).replace('"','__DoubleApostroph__') + value_string=str(value_string).replace(';','__$$__') + value_string=str(value_string).replace('&','&') + if str(value_string).find('http')>-1: + value_string='<A HREF=%r target=_blank>Link to Document</A>' % (value_string) + kml4Marker+=attributeList[i]+": "+value_string+"</a><br>\n" + i+=1 + + kml4Marker+="]]>\n</description>\n" + kml4Marker+="<styleUrl>#msn_dot_red</styleUrl>\n" + kml4Marker+="<Point>" + try: + if float(xCoord)!=0 and float(yCoord)!=0: + kml4Marker+="<coordinates>%s,%s,0</coordinates>" % (xCoord,yCoord) else: - # ignore non-tag nodes - continue - - if tagName == rowTagName.lower(): - # start of row - doc.expandNode(node[1]) - cnt += 1 - if cnt == 1: - # first row -- field names - names=node[1].getElementsByTagNameNS(namespace, dataTagName) - for name in names: - fn = getTextFromNode(name) - xmlFields.append({'name':sqlName(fn),'type':'text'}) - - if fieldsOnly: - # return just field names - return xmlFields - - # create table - if fields is None: - fields = xmlFields - - sqlFields = self.createEmptyTable(schema, table, fields) - numFields = len(sqlFields) - fieldString = ", ".join(['"%s"'%f['name'] for f in sqlFields]) - valString = ", ".join(["%s" for f in sqlFields]) - sqlInsert = 'insert into "%s"."%s" (%s) values (%s)'%(schema,table,fieldString,valString) - #logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert) - - else: - # following rows are data - colNodes=node[1].getElementsByTagNameNS(namespace, colTagName) - data = [] - hasData = False - for colNode in colNodes: - dataNodes=colNode.getElementsByTagNameNS(namespace, dataTagName) - if len(dataNodes) > 0: - val = getTextFromNode(dataNodes[0]) - hasData = True - else: - val = None + unknownPointCounter+=1 + kml4Marker+="<coordinates>%s,%s,0</coordinates>" % (125,25+int(unknownPointCounter)*0.4) + markerTitel="<name>Un-referenced Location!</name>\n" + + except: + unknownPointCounter+=1 + kml4Marker+="<coordinates>%s,%s,0</coordinates>" % (125,25+int(unknownPointCounter)*0.4) + markerTitel="<name>Un-referenced Location!</name>\n" + kml4Marker+="</Point>\n" + kml4Marker+= markerTitel + kml4Marker+="</Placemark>\n" + + kml4Marker=kml4Marker+"</Document>\n</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 - data.append(val) - - if not hasData: - # ignore empty rows - continue - - # fix number of data fields - if len(data) > numFields: - del data[numFields:] - elif len(data) < numFields: - missFields = numFields - len(data) - data.extend(missFields * [None,]) - - logging.debug("importexcel sqlinsert=%s data=%s"%(sqlInsert,data)) - self.executeSQL(sqlInsert, data, hasResult=False) - - return cnt - + 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: + gis_id_orig=gis_id + 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(gis_id_orig) + if len(coords)>0: + try: + SQL="INSERT INTO chgis.chgis_coords (gis_id,x_coord,y_coord) VALUES (%s,%s,%s); ANALYZE chgis.chgis_coords;"%(gis_id,coords[1],coords[0]) + returnstring=self.ZSQLSimpleSearch(SQL) + return coords + except: + SQL="INSERT INTO chgis.chgis_coords (gis_id,x_coord,y_coord) VALUES (%s,%s,%s); ANALYZE chgis.chgis_coords;"%(gis_id,coords[0][1],coords[0][0]) + returnstring=self.ZSQLSimpleSearch(SQL) + return coords[0] + #except: + # return "no coords found" + + else: + return coords[0] + + def getCoordsFromREST_gisID(self,gis_id): + coordlist=[] + h=0 + while (h<5 and coordlist==[]): + h+=1 + urlresponse=self.urlFunctions.zUrlopenParseString(self.urlFunctions.zUrlopenRead("http://chgis.hmdc.harvard.edu/xml/id/"+gis_id)) + baseDocElement=self.urlFunctions.zUrlopenDocumentElement(urlresponse) + childnodes=self.urlFunctions.zUrlopenChildNodes(baseDocElement) + itemnodes=self.urlFunctions.zUrlopenGetElementsByTagName(baseDocElement,'item') + itemspatialnodes=[] + for i in range(0,self.urlFunctions.zUrlopenLength(itemnodes)): + itemnode=self.urlFunctions.zUrlopenGetItem(itemnodes,i) + itemspatialnodes=self.urlFunctions.zUrlopenGetElementsByTagName(itemnode,'spatial') + if itemspatialnodes!=[]: + for j in range(0,self.urlFunctions.zUrlopenLength(itemspatialnodes)): + coord=[] + itemspatialnode= self.urlFunctions.zUrlopenGetItem(itemspatialnodes,j) + itemspatiallatnodes=self.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_latitude') + for k in range(0,self.urlFunctions.zUrlopenLength(itemspatiallatnodes)): + itemspatiallatnode= self.urlFunctions.zUrlopenGetItem(itemspatiallatnodes,k) + coord.append(self.urlFunctions.zUrlopenGetTextData(itemspatiallatnode)) + itemspatiallngnodes=self.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_longitude') + for k in range(0,self.urlFunctions.zUrlopenLength(itemspatiallngnodes)): + itemspatiallngnode= self.urlFunctions.zUrlopenGetItem(itemspatiallngnodes,k) + coord.append(self.urlFunctions.zUrlopenGetTextData(itemspatiallngnode)) + coordlist.append(coord) + gis_id= "_"+gis_id + return coordlist + +# End for GoogleMaps creation + + def RESTwrite2File(self,datadir, name,text): +# try: + fileid=name + if fileid in datadir.objectIds(): + datadir.manage_delObjects(fileid) + newfile=open(name,'w') + newfile.write(text) + newfile.close() + file4Read=open(name,'r') + fileInZope=datadir.manage_addFile(id=fileid,file=file4Read) + return "Write successful" +# except: +# return "Could not write" + + def manage_editRestDbInterface(self, title=None, connection_id=None, REQUEST=None): """Change the object""" @@ -581,3 +588,4 @@ REQUEST.RESPONSE.redirect('manage_main') +
--- a/zpt/KML_schema_table.zpt Wed Sep 29 21:09:44 2010 +0200 +++ b/zpt/KML_schema_table.zpt Tue Oct 19 12:56:34 2010 +0200 @@ -1,6 +1,6 @@ <?xml version="1.0" encoding="utf-8"?> <kml xmlns="http://www.opengis.net/kml/2.2" xmlns:tal="http://xml.zope.org/namespaces/tal" - tal:define="schema options/schema; table options/table; data python:here.getKmlData(schema=schema,table=table);"> + tal:define="schema options/schema; table options/table; data python:here.getKmlData(schema=schema,table=table);path python:here.getKmlData4Path(schema=schema,table=table)"> <Document> <Style id="marker_icon"> <IconStyle> @@ -10,6 +10,12 @@ </Icon> </IconStyle> </Style> + <Style id="redLine"> + <LineStyle> + <color>red</color> + <width>4</width> + </LineStyle> + </Style> <Placemark tal:repeat="place data"> <description tal:content="place/description"><![CDATA[<b>ERD-0815: ERD-0815</a><br> Jingshi: Jingshi</a><br> @@ -23,5 +29,17 @@ <coordinates tal:content="string:${place/coord_x},${place/coord_y},${place/coord_z}">116.38,39.92,0</coordinates> </Point> </Placemark> + <Placemark> + <name>Path</name> + <description>Red line visualizes path between locations</description> + <styleUrl>#redLine</styleUrl> + <LineString> + <extrude>1</extrude> + <tessellate>1</tessellate> + <altitudeMode>absolute</altitudeMode> + <coordinates tal:repeat="place path" tal:content="string:${place/coord_x},${place/coord_y},${place/coord_z}">116.38,39.92,0</coordinates> + </LineString> + </Placemark> + </Document> </kml>