'''
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 RestDbInterface 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(RestDbInterface):
    """Object for RESTful GIS database queries
    path schema: /db/{schema}/{table}/
    omitting table gives a list of schemas
    omitting table and schema gives a list of schemas 
    """
    
    meta_type="RESTgis"

    
    # 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 = RestDbInterface.createEmptyTable(self,schema,table,fields)
        if sqlFields is not None:
            self.setTableMetaTypes(schema,table,sqlFields)

        return sqlFields
    
    
    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"):
        """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"'%(schema,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,schema,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,schema,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,schema,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'))
                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'))
                if len(res['rows']) > 0:
                    until_year = res['rows'][0][0]
            except:
                    until_year = "until_year_dummy"
            
            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)
                    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['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 getKmlUrl(self,schema='chgis',table='mpdl',args={'doc':None,'id':None}):
        logging.debug("getKmlUrl")
        id=args.get('id')
        doc=args.get('doc')
        data = self.getDataForGoogleMap(schema,table,id,doc)
        kml=self.getKMLname(data=data,table=table)
        baseUrl = self.absolute_url()
        return "%s/daten/%s"%(baseUrl,kml)

    def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=None,doc=None):
        logging.debug("getDataForGoogleMap")
        qstr="SELECT * FROM "+schema+"."+table
        try:
            if id is not None:
                qstr=qstr+" WHERE "
                for id_item in id.split(","):
                    if table=='mpdl':
                        qstr=qstr+" mpdl_xmlsource_id = '"+id_item+ "' OR"
                    else:
                        qstr=qstr+" cast(id as text) LIKE '"+id_item+ "' OR"
                qstr=str(qstr).rsplit(" ",1)[0] #to remove last " and "
            data=self.ZSQLSimpleSearch(qstr)
            return data
        except:
            return qstr


    def getKMLname(self,data=[],table=""):
        logging.debug("getKMLname")
        #session=context.REQUEST.SESSION
        kml4Marker="<kml xmlns=\'http://www.opengis.net/kml/2.2\'><Document>"
        kml4Marker+="<Style id=\'marker_icon_red\'><IconStyle><scale>15</scale><Icon><href>http://mappit.mpiwg-berlin.mpg.de/mappit/icons/dot_red.png</href></Icon></IconStyle></Style>\n"
        kml4Marker+="<Style id=\'marker_icon_black\'><IconStyle><scale>15</scale><Icon><href>http://mappit.mpiwg-berlin.mpg.de/mappit/icons/dot_black.png</href></Icon></IconStyle></Style>\n"
        kml4Marker+="<Style id=\'marker_icon_blue\'><IconStyle><scale>15</scale><Icon><href>http://mappit.mpiwg-berlin.mpg.de/mappit/icons/dot_blue.png</href></Icon></IconStyle></Style>\n"
        kml4Marker+="<Style id=\'marker_icon_green\'><IconStyle><scale>15</scale><Icon><href>http://mappit.mpiwg-berlin.mpg.de/mappit/icons/dot_green.png</href></Icon></IconStyle></Style>\n"
        kml4Marker+="<Style id=\'marker_icon_violett\'><IconStyle><scale>15</scale><Icon><href>http://mappit.mpiwg-berlin.mpg.de/mappit/icons/dot_violett.png</href></Icon></IconStyle></Style>\n"
        initializeStringForGoogleMaps=""
        #doLine=container.getVar('doLine')
        # Mapping a set of points from table-based SQL-query:
        if data!=None:
            try:
                SQL='SELECT "attribute with gis_id" FROM public.metadata WHERE tablename = %s'
                res = self.executeSQL(SQL, (table,))
                gisIDattribute = res['rows'][0][0]
            except:
                return "table not registered within metadata"
            
            for dataset in data:
                try:  
                    xCoord=getattr(dataset,'longitude')
                    yCoord=getattr(dataset,'latitude')
                except:
                    try:
                        xCoord=getattr(dataset,'x_coord')
                        yCoord=getattr(dataset,'y_coord')
                    except:
                        #try:
                        gisID=getattr(dataset,gisIDattribute)
                        coords=self.getPoint4GISid(gisID)
                        if coords!=None:
                            xCoord=coords[0]
                            yCoord=coords[1]
                        # except:
                        #     return "no coordinates found"
                        
                if float(xCoord)!=0:
                    if float(yCoord)!=0:
                        kml4Marker=kml4Marker+"<Placemark>"
                        kml4Marker=kml4Marker+"<description> <![CDATA[<b>"
                        for values in dataset:
                            #logging.debug("values=%s"%repr(values))
                            if values != (None, None):
                                if str(values).find('name')>-1:
                                    kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n"
                                    continue
                                elif str(values).find('place')>-1:
                                    kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n"
                                    continue
                        
                                kml4Marker=kml4Marker+str(values)+": "
                                attribute_string=str(values).replace("'","__Apostroph__")
                                attribute_string=str(attribute_string).replace('"','__DoubleApostroph__')
                                attribute_string=str(attribute_string).replace(';','__$$__')
                                attribute_string=str(attribute_string).replace('&','&amp;')
                                if str(attribute_string).find('http')>-1:
                                    attribute_string='<A HREF=' + str(attribute_string) + ' target="_blank">' + str(attribute_string) + '</A>'
                                kml4Marker=kml4Marker+attribute_string+"</a><br>\n"
                                  
                        kml4Marker=kml4Marker+"]]></description>\n"
                        kml4Marker=kml4Marker+"<styleURL>#marker_icon_red</styleURL>\n"
                        kml4Marker=kml4Marker+"<Point>"
                              
                        kml4Marker=kml4Marker+"<coordinates>"+str(xCoord)+","+str(yCoord)+",0</coordinates>\n"
                        kml4Marker=kml4Marker+"</Point>\n"
                        kml4Marker=kml4Marker+"</Placemark>\n"
        
            kml4Marker=kml4Marker+"</Document>\n</kml>"
            kmlFileName="marker"+str(time.time())+".kml"
        
            #kml4Marker=str(kml4Marker).replace('&','$$')
            #kml4Marker=str(kml4Marker).replace(';','__$$__')
            #kml4Marker=str(kml4Marker).replace('#','__SHARP__')
            isLoadReady='false'
            while isLoadReady=='false':
                isLoadReady=self.RESTwrite2File(self.daten,kmlFileName,kml4Marker)

        return kmlFileName
    
    def trydatahas_key(self,data,index,key_string):
        try:
            return data[index].has_key(key_string) 
        except:
            return 'false'

#    def getGoogleMapString(self,kml):
#        logging.debug("getGoogleMapString")
#        printed= '<body %s> '%kml +"""\n <div id="map_canvas" style="width: 98%; height: 95%"> </div> \n </body>" \n </html>"""
#        return printed
    
    def getPoint4GISid(self,gis_id):
        j=0
        coords=(0,0)
        if gis_id != None:
         while (True):
           j=j+1
           if (j>100):                                         # FJK: just to prevent endless loops
             break
           if (gis_id.isdigit()):                              # FJK: regular exit from while-loop
             break
           else:
             gis_id=gis_id.strip('abcdefghijklmnopqrstuvwxyz_') # FJK: to strip all letters 
             gis_id=gis_id.strip()                              # FJK: to strip all whitespaces
         resultpoint = [0,0]
         results = None
         try:
            if int(gis_id)>0:
                SQL="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id LIKE cast("+ str(gis_id) +" as text);"
                results=self.ZSQLSimpleSearch(SQL)
                #print results
                if results != None:
                    for result in results:
                        resultpoint=[getattr(result,str('x_coord')),getattr(result,str('y_coord'))]
                if resultpoint !=[0,0]: 
                    return resultpoint
            else:
                coords=self.getCoordsFromREST_gisID(gis_id)
                SQL="INSERT INTO chgis.chgis_coords (chgis_coords_pkey,gis_id,x_coord,y_coord) VALUES (default," +gis_id+ "," +coords[0][1]+ "," +coords[0][0]+ "); ANALYZE chgis.chgis_coords;"
                returnstring=self.ZSQLSimpleSearch(SQL)
                return coords[0]
         except:
            return "gis_id not to interpretable:"+str(gis_id)
        else:
            return coords[0]

    def getCoordsFromREST_gisID(self,gis_id):
        gis_id=gis_id.strip()
        coordlist=[]
        i=0
        while (i<5 and coordlist==[]):
            urlinfo=urlFunctions.zUrlopenInfo(self,"http://chgis.hmdc.harvard.edu/xml/id/"+gis_id)
            urlinfoLength=urlinfo.get('content-length')
            if int(urlinfoLength)<500:
                urlresponse=urlFunctions.zUrlopenRead(self,"http://chgis.hmdc.harvard.edu/xml/id/cts_"+gis_id)
            else:
                urlresponse=urlFunctions.zUrlopenRead(self,"http://chgis.hmdc.harvard.edu/xml/id/"+gis_id)
            urlresponseString=urlFunctions.zUrlopenParseString(self,urlresponse)
            baseDocElement= urlFunctions.zUrlopenDocumentElement(self,urlresponseString)
            childnodes=urlFunctions.zUrlopenChildNodes(self,baseDocElement)
            itemnodes=urlFunctions.zUrlopenGetElementsByTagName(self,baseDocElement,'item')
            itemspatialnodes=None
            
            for i in range(0,urlFunctions.zUrlopenLength(self,itemnodes)):
                itemnode=urlFunctions.zUrlopenGetItem(self,itemnodes,i)
                itemspatialnodes=urlFunctions.zUrlopenGetElementsByTagName(self,itemnode,'spatial')
            if itemspatialnodes is not None:
                for j in range(0,urlFunctions.zUrlopenLength(self,itemspatialnodes)):
                    coord=[]
                    itemspatialnode= urlFunctions.zUrlopenGetItem(self,itemspatialnodes,j)
                    itemspatiallatnodes=urlFunctions.zUrlopenGetElementsByTagName(self,itemspatialnode,'degrees_latitude')
                    for k in range(0,urlFunctions.zUrlopenLength(self,itemspatiallatnodes)):
                        itemspatiallatnode= urlFunctions.zUrlopenGetItem(self,itemspatiallatnodes,k)
                        coord.append(urlFunctions.zUrlopenGetTextData(self,itemspatiallatnode))
                itemspatiallngnodes=urlFunctions.zUrlopenGetElementsByTagName(self,itemspatialnode,'degrees_longitude')
                for k in range(0,urlFunctions.zUrlopenLength(self,itemspatiallngnodes)):
                    itemspatiallngnode= urlFunctions.zUrlopenGetItem(self,itemspatiallngnodes,k)
                    coord.append(urlFunctions.zUrlopenGetTextData(self,itemspatiallngnode))
                coordlist.append(coord)
                gis_id= "_"+gis_id
        return coordlist
       
# End for GoogleMaps creation

    def RESTwrite2File(self,datadir, name,text):
        logging.debug("RESTwrite2File datadir=%s name=%s"%(datadir,name))
        try:
            import cStringIO as StringIO
        except:
            import StringIO

        # make filehandle from string
        textfile = StringIO.StringIO(text)
        fileid=name
        if fileid in datadir.objectIds():
            datadir.manage_delObjects(fileid)
        fileInZope=datadir.manage_addFile(id=fileid,file=textfile)
        return "Write successful"
        
    def manage_editRestDbGisApi(self, title=None, connection_id=None,
                     REQUEST=None):
        """Change the object"""
        if title is not None:
            self.title = title
            
        if connection_id is not None:
            self.connection_id = connection_id
                
        #checkPermission=getSecurityManager().checkPermission
        REQUEST.RESPONSE.redirect('manage_main')

        
manage_addRestDbGisApiForm=PageTemplateFile('zpt/addRestDbGisApi',globals())

def manage_addRestDbGisApi(self, id, title='', label='', description='',
                     createPublic=0,
                     createUserF=0,
                     REQUEST=None):
        """Add a new object with id *id*."""
    
        ob=RestDbGisApi(str(id),title)
        self._setObject(id, ob)
        
        #checkPermission=getSecurityManager().checkPermission
        REQUEST.RESPONSE.redirect('manage_main')