# HG changeset patch
# User casties
# Date 1285787384 -7200
# Node ID 2f4c427dec4423af0b06ec9d22d287ee6600373a
# Parent  54940a99f12d120acb47109d2188ed13808af047
wrangled kml construction into template and method
kml currently constructed live

diff -r 54940a99f12d -r 2f4c427dec44 RestDbGisApi.py
--- a/RestDbGisApi.py	Mon Sep 20 15:39:58 2010 +0200
+++ b/RestDbGisApi.py	Wed Sep 29 21:09:44 2010 +0200
@@ -24,6 +24,15 @@
                    "coord_lon": "numeric"
                    }
 
+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(RestDbInterface):
     """Object for RESTful GIS database queries
     path schema: /db/{schema}/{table}/
@@ -35,13 +44,15 @@
 
     # data templates
     GIS_schema_table = PageTemplateFile('zpt/GIS_schema_table', globals())
+    KML_schema_table = PageTemplateFile('zpt/KML_schema_table', globals())
+    
     # and scripts
     def KML_URL_schema_table(self,schema,table):
         """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.getKmlUrl(schema=schema,table=table,id=id,doc=doc)
+        return self.getLiveKmlUrl(schema=schema,table=table,id=id,doc=doc)
 
 
     def checkTableMetaPermission(self,action,schema,table,user=None):
@@ -136,17 +147,6 @@
             return None
     
     
-
-    # Methods for GoogleMaps creation
-#    def showGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None):
-#        logging.debug("showGoogleMap")
-#        data = self.getDataForGoogleMap(schema,table,id,doc)
-#        kmlFileName=self.getKMLname(data=data,table=table)
-#        initializeStringForGoogleMaps="""onload=\"initialize(\'http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kmlFileName+"""\')\""""#+str(data)
-#        initializeStringForGoogleMaps=initializeStringForGoogleMaps.replace("None","0")
-#        googleMap_page=self.htmlHead()+str(self.getGoogleMapString(kml=initializeStringForGoogleMaps))
-#        return googleMap_page
-
     def getKmlUrl(self,schema='chgis',table='mpdl',id=[],doc=None):
         logging.debug("getKmlUrl")
         data = self.getDataForGoogleMap(schema,table,id,doc)
@@ -154,11 +154,16 @@
         baseUrl = self.absolute_url()
         return "%s/daten/%s"%(baseUrl,kml)
 
-    def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None):
+    def getLiveKmlUrl(self,schema='chgis',table='mpdl',id=None,doc=None):
+        logging.debug("getLiveKmlUrl")
+        baseUrl = self.absolute_url()
+        return "%s/db/%s/%s?format=KML"%(baseUrl,schema,table)
+
+    def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=None,doc=None):
         logging.debug("getDataForGoogleMap")
         qstr="SELECT * FROM "+schema+"."+table
         try:
-            if id!=[]:
+            if id is not None:
                 qstr=qstr+" WHERE "
                 for id_item in id.split(","):
                     if table=='mpdl':
@@ -171,6 +176,113 @@
         except:
             return qstr
 
+    def getKmlData(self, schema, table, ids=None, gisIdField=None, latField=None, lonField=None):
+        """returns data structure for KML template"""
+        logging.debug("getKMLdata")
+        # Mapping a set of points from table-based SQL-query:
+        qstr='SELECT * FROM "%s"."%s"'%(schema,table)
+        idList = None
+        if ids is not None:
+            qstr += ' WHERE '
+            if table=='mpdl':
+                qstr += 'mpdl_xmlsource_id IN ('
+            else:
+                qstr += 'CAST(id AS text) IN ('
+                
+            idList = ids.split(",")
+            qstr += ','.join(['%s' for i in idList])
+            qstr += ')'
+
+        data = self.executeSQL(qstr,idList)
+        
+        fieldMap = self.getFieldNameMap(data['fields'])
+        
+        if (gisIdField is None) and (latField is None or lonField is None):
+            # no fields given - choose automagically
+            # gis id in metadata first
+            SQL='SELECT "attribute with gis_id" FROM public.metadata WHERE tablename = %s'
+            res = self.executeSQL(SQL, (table,))
+            if len(res['rows']) > 0:
+                gisIdField = res['rows'][0][0]
+            else:
+                logging.warning("no entry in metadata table for table %s"%table)
+                # 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")
+                    return None
+        
+        # convert field names to row indexes
+        gisIdIdx = fieldMap.get(gisIdField,None)
+        latIdx = fieldMap.get(latField,None)
+        lonIdx = fieldMap.get(lonField,None)
+        logging.debug("gisidfield=%s idx=%s"%(gisIdField,gisIdIdx))
+        
+        # convert data
+        kmlData = []
+        for dataset in data['rows']:
+            if gisIdIdx is not None:
+                gisID = dataset[gisIdIdx]
+                coords=self.getPoint4GISid(gisID)
+                if coords!=None:
+                    xCoord=coords[0]
+                    yCoord=coords[1]
+                    
+            elif latIdx is not None:
+                xCoord = dataset[lonIdx]
+                yCoord = dataset[latIdx]
+                
+            else:
+                logging.error("getKMLdata unable to find position")
+                return None
+                    
+            if float(xCoord) == 0.0:
+                continue
+            
+            if float(yCoord) == 0.0:
+                continue
+
+            kmlPlace = {}
+            
+            # description
+            desc = ''
+            i = -1
+            for value in dataset:
+                i += 1
+                name = data['fields'][i][0]
+                logging.debug("value=%s"%value)
+                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)
+                    if val.find('http')>-1:
+                        val ='<a href="' + val + '" target="_blank">' + val + '</a>'
+                        
+                    desc += kmlEncode(val)
+                    desc += '<br/>\n'
+                      
+            kmlPlace['description'] = "<![CDATA[%s]]>"%desc
+            kmlPlace['icon'] = '#marker_icon'
+            kmlPlace['coord_x'] = str(xCoord)
+            kmlPlace['coord_y'] = str(yCoord)
+            kmlPlace['coord_z'] = '0'
+            kmlData.append(kmlPlace)
+    
+        #logging.debug("kmlData=%s"%(repr(kmlData)))
+        return kmlData
+        
+
+
     def getKMLname(self,data=[],table=""):
         logging.debug("getKMLname")
         #session=context.REQUEST.SESSION
@@ -209,6 +321,7 @@
                         kml4Marker=kml4Marker+"<Placemark>"
                         kml4Marker=kml4Marker+"<description> <![CDATA[<b>"
                         for values in dataset:
+                            #logging.debug("values=%s"%repr(values))
                             if values != (None, None):
                                 if str(values).find('name')>-1:
                                     kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n"
diff -r 54940a99f12d -r 2f4c427dec44 RestDbInterface.py
--- a/RestDbInterface.py	Mon Sep 20 15:39:58 2010 +0200
+++ b/RestDbInterface.py	Wed Sep 29 21:09:44 2010 +0200
@@ -151,6 +151,16 @@
             
         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):
         """execute query with args on database and return all results.
         result format: {"fields":fields, "rows":data}"""
@@ -168,6 +178,8 @@
             # 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()
diff -r 54940a99f12d -r 2f4c427dec44 zpt/GIS_schema_table.zpt
--- a/zpt/GIS_schema_table.zpt	Mon Sep 20 15:39:58 2010 +0200
+++ b/zpt/GIS_schema_table.zpt	Wed Sep 29 21:09:44 2010 +0200
@@ -1,7 +1,7 @@
 <!DOCTYPE html>
 <html tal:define="root here/getRestDbUrl; layout python:request.get('layout','pre'); element_id python:request.get('element_id',None);
         schema options/schema; table options/table; id python:request.get('id',[]); doc python:request.get('doc',None);
-        kmlUrl python:here.getKmlUrl(schema=schema,table=table,id=id,doc=doc);
+        kmlUrl python:here.getLiveKmlUrl(schema=schema,table=table,id=id,doc=doc);
 ">
  <head>
     <meta http-equiv="content-type" content="text/html; charset=utf-8">
diff -r 54940a99f12d -r 2f4c427dec44 zpt/KML_schema_table.zpt
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/zpt/KML_schema_table.zpt	Wed Sep 29 21:09:44 2010 +0200
@@ -0,0 +1,27 @@
+<?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);">
+  <Document>
+    <Style id="marker_icon">
+      <IconStyle>
+        <scale>15</scale>
+        <Icon>
+          <href>http://chinagis.mpiwg-berlin.mpg.de/chinagis/images/dot_red.png</href>
+        </Icon>
+      </IconStyle>
+    </Style>
+    <Placemark tal:repeat="place data">
+      <description tal:content="place/description"><![CDATA[<b>ERD-0815: ERD-0815</a><br>
+Jingshi: Jingshi</a><br>
+nma_1898541: nma_1898541</a><br>
+Forbidden City: Forbidden City</a><br>
+1406: 1406</a><br>
+720000: 720000</a><br>
+]]></description>
+      <styleURL tal:content="place/icon">#marker_icon</styleURL>
+      <Point>
+        <coordinates tal:content="string:${place/coord_x},${place/coord_y},${place/coord_z}">116.38,39.92,0</coordinates>
+      </Point>
+    </Placemark>
+  </Document>
+</kml>