# HG changeset patch
# User fknauft
# Date 1287485794 -7200
# Node ID e0a79d926902e33b4f4cd7f7511dfc6e4e3acc5d
# Parent  2f4c427dec4423af0b06ec9d22d287ee6600373a
Path for kml included

diff -r 2f4c427dec44 -r e0a79d926902 RestDbGisApi.py
--- 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('&','&amp;')
+                        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:
diff -r 2f4c427dec44 -r e0a79d926902 RestDbInterface.py
--- 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('&','&amp;')
+                  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')
 
         
+
diff -r 2f4c427dec44 -r e0a79d926902 zpt/KML_schema_table.zpt
--- 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>