Mercurial > hg > ChinaGisRestApi
annotate RestDbGisApi.py @ 266:c37e8c173136
Multilayer for Europeana4D
| author | fknauft |
|---|---|
| date | Wed, 25 Jan 2012 20:43:05 +0100 |
| parents | 52b1247140b7 |
| children | d1b43624cc63 9b7db308d2e6 |
| rev | line source |
|---|---|
| 43 | 1 ''' |
| 2 Created on 2.9.2010 | |
| 3 | |
| 4 @author: casties, fknauft | |
| 5 ''' | |
| 6 | |
| 7 from OFS.Folder import Folder | |
| 8 from Products.PageTemplates.PageTemplateFile import PageTemplateFile | |
| 9 from Products.ZSQLExtend import ZSQLExtend | |
| 10 import logging | |
| 11 import re | |
| 12 import time | |
| 61 | 13 import datetime |
| 247 | 14 import urlFunctions |
| 43 | 15 |
| 16 from RestDbInterface import * | |
| 17 | |
| 18 | |
| 55 | 19 def kmlEncode(s): |
| 20 """returns string encoded for displaying in KML attribute""" | |
| 21 res = s.replace("'","__Apostroph__") | |
| 22 res = res.replace('"','__DoubleApostroph__') | |
| 23 res = res.replace(';','__$$__') | |
| 24 res = res.replace('&','&') | |
| 25 return res | |
| 26 | |
| 27 | |
| 43 | 28 class RestDbGisApi(RestDbInterface): |
| 29 """Object for RESTful GIS database queries | |
| 30 path schema: /db/{schema}/{table}/ | |
| 31 omitting table gives a list of schemas | |
| 32 omitting table and schema gives a list of schemas | |
| 33 """ | |
| 34 | |
| 35 meta_type="RESTgis" | |
| 36 | |
| 55 | 37 |
| 44 | 38 # and scripts |
|
254
901c1f745d13
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
252
diff
changeset
|
39 def KML_URL_schema_table(self,schema,table, useTimestamp=True, args=None): |
| 44 | 40 """KML_URL table function""" |
| 41 self.REQUEST.RESPONSE.setHeader("Content-Type", "text/plain") | |
| 42 id = self.REQUEST.get('id',[]) | |
| 43 doc = self.REQUEST.get('doc',None) | |
| 245 | 44 # return self.getLiveKmlUrl(schema=schema,table=table, useTimestamp=useTimestamp) |
| 45 return self.getLiveKmlUrl(schema=schema,table=table, useTimestamp=False) | |
| 43 | 46 |
| 61 | 47 # |
| 48 # database methods | |
| 49 # | |
| 70 | 50 toSqlTypeMap = { |
| 51 "text": "text", | |
| 52 "number": "numeric", | |
| 53 "id": "text", | |
| 54 "gis_id": "text", | |
| 55 "coord_lat": "numeric", | |
|
252
efd2469d1722
geometry-column of tables will be displayed as string
fknauft
parents:
250
diff
changeset
|
56 "coord_lon": "numeric", |
| 263 | 57 "the_geom": "the_geom", |
| 58 "from_year":"text", | |
| 59 "until_year":"text" | |
| 70 | 60 } |
| 61 | |
| 60 | 62 def getTableOwner(self,schema,table): |
| 63 """returns the owner of the table""" | |
| 61 | 64 # what do we do with the schema? |
| 65 sql = 'select table_owner from public.gis_table_meta where table_name = %s' | |
| 66 res = self.executeSQL(sql,(table,)) | |
| 67 if len(res['rows']) > 0: | |
| 68 return res['rows'][0][0] | |
| 60 | 69 return None |
| 70 | |
| 71 def isAllowed(self,action,schema,table,user=None,owner=None): | |
| 43 | 72 """returns if the requested action on the table is allowed""" |
| 73 if user is None: | |
| 74 user = self.REQUEST.get('AUTHENTICATED_USER',None) | |
| 60 | 75 logging.debug("isAllowed action=%s schema=%s table=%s user=%s"%(action,schema,table,user)) |
| 76 if action == "create": | |
| 77 if user is not None and str(user) != 'Anonymous User': | |
| 78 # any authenticated user can create | |
| 79 return True | |
| 80 else: | |
| 81 return False | |
| 82 | |
| 83 if action == "update": | |
| 84 if owner is None: | |
| 85 owner = self.getTableOwner(schema,table) | |
| 61 | 86 logging.debug("isAllowed user=%s owner=%s"%(user,owner)) |
| 60 | 87 if user is not None and str(user) == str(owner): |
| 88 # update only your own table | |
| 89 return True | |
| 90 else: | |
| 91 return False | |
| 92 | |
| 62 | 93 # anything else is allowed |
| 43 | 94 return True |
| 95 | |
| 61 | 96 def setTableMetaTypes(self,schema,table,fields,user=None): |
| 43 | 97 """sets the GIS meta information for table""" |
| 61 | 98 if user is None: |
| 99 user = self.REQUEST.get('AUTHENTICATED_USER',None) | |
| 100 | |
| 101 logging.debug("settablemetatypes schema=%s, table=%s, fields=%s user=%s"%(schema,table,fields,user)) | |
| 43 | 102 |
| 61 | 103 today = datetime.date.today().isoformat() |
| 104 | |
| 105 res = self.executeSQL('select * from public.gis_table_meta where table_name = %s', (table,)) | |
| 43 | 106 if len(res['rows']) > 0: |
| 107 # meta record exists | |
| 61 | 108 sql = 'update public.gis_table_meta set table_owner=%s, table_modified=%s where table_name=%s' |
| 109 self.executeSQL(sql, (str(user),today,table), hasResult=False) | |
| 43 | 110 else: |
| 111 # new meta record | |
| 61 | 112 sql = 'insert into public.gis_table_meta (table_name,table_owner,table_created) values (%s,%s,%s)' |
| 113 self.executeSQL(sql, (table,str(user),today), hasResult=False) | |
| 114 | |
| 115 # update row info | |
| 116 sql = 'delete from public.gis_table_meta_rows where table_name=%s' | |
| 117 self.executeSQL(sql,(table,),hasResult=False) | |
| 118 sql = 'insert into public.gis_table_meta_rows (table_name,field_name,gis_type) values (%s,%s,%s)' | |
| 119 for f in fields: | |
| 120 t = f['type'] | |
| 121 fn = f['name'] | |
| 122 self.executeSQL(sql, (table,fn,t), hasResult=False) | |
| 43 | 123 |
| 124 | |
| 61 | 125 def getListOfUserTables(self,schema='public',username='guest'): |
| 126 """return list of tables""" | |
| 127 logging.debug("getlistofusertables") | |
| 128 # get list of db tables | |
| 100 | 129 # qstr = """SELECT t.table_name FROM information_schema.tables t, public.gis_table_meta m WHERE t.table_type = 'BASE TABLE' |
| 130 # AND t.table_schema = %s AND t.table_name = m.table_name ORDER BY 1""" | |
| 131 qstr = """SELECT t.table_name FROM information_schema.tables t, public.gis_table_meta m | |
| 132 WHERE t.table_schema = %s AND t.table_name = m.table_name ORDER BY 1""" | |
| 43 | 133 |
| 61 | 134 data=self.executeSQL(qstr,(schema,)) |
| 135 return data | |
| 136 | |
| 43 | 137 |
| 138 def createEmptyTable(self,schema,table,fields): | |
| 139 """create a table with the given fields | |
| 140 returns list of created fields""" | |
| 61 | 141 sqlFields = RestDbInterface.createEmptyTable(self,schema,table,fields) |
| 142 if sqlFields is not None: | |
| 43 | 143 self.setTableMetaTypes(schema,table,sqlFields) |
| 61 | 144 |
| 145 return sqlFields | |
| 43 | 146 |
| 147 | |
| 59 | 148 def getLiveKmlUrl(self,schema,table,useTimestamp=True,REQUEST=None): |
|
254
901c1f745d13
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
252
diff
changeset
|
149 return self.getLiveUrl(schema,table,useTimestamp,REQUEST) |
| 55 | 150 |
|
259
17b2c2dba0fd
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
258
diff
changeset
|
151 def getKmlData(self, schema, table, ids=None, sortBy=1, gisIdField=None, latField=None, lonField=None, geomField="point", colorField="red_big"): |
| 55 | 152 """returns data structure for KML template""" |
| 249 | 153 logging.debug("getKMLdata gid=%s lat=%s lon=%s sortBy=%s geom=%s color=%s"%(gisIdField,latField,lonField,sortBy,geomField,colorField)) |
| 148 | 154 if geomField is None: |
| 155 geomField="point" | |
| 156 if colorField is None: | |
| 157 colorField="red" | |
| 55 | 158 # Mapping a set of points from table-based SQL-query: |
| 159 qstr='SELECT * FROM "%s"."%s"'%(schema,table) | |
| 160 idList = None | |
| 161 if ids is not None: | |
| 162 qstr += ' WHERE ' | |
|
255
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
163 if schema=='mpdl': |
| 55 | 164 qstr += 'mpdl_xmlsource_id IN (' |
| 165 else: | |
| 166 qstr += 'CAST(id AS text) IN (' | |
| 167 | |
| 168 idList = ids.split(",") | |
| 169 qstr += ','.join(['%s' for i in idList]) | |
| 170 qstr += ')' | |
| 171 | |
| 58 | 172 if sortBy: |
| 173 # add sort clause | |
| 59 | 174 if sortBy == 1: |
| 175 qstr += ' ORDER BY 1' | |
| 249 | 176 elif sortBy == 'Default': |
| 177 qstr += ' ORDER BY 1' | |
|
250
d7ceeb8ccbd7
Layer gui reordered, point order can be defined, minor edit
fknauft
parents:
249
diff
changeset
|
178 elif sortBy == 'undefined': |
|
d7ceeb8ccbd7
Layer gui reordered, point order can be defined, minor edit
fknauft
parents:
249
diff
changeset
|
179 qstr += ' ORDER BY 1' |
| 59 | 180 else: |
| 181 # TODO: proper quoting for names | |
| 182 qstr += ' ORDER BY "%s"'%sortBy.replace('"','') | |
| 245 | 183 bad_luck=True |
| 184 bl_counter=0 | |
| 185 while (bad_luck): | |
| 186 try: | |
| 187 data = self.executeSQL(qstr,idList) | |
| 188 bad_luck=False | |
| 189 bl_counter=bl_counter+1 | |
| 190 except: | |
|
255
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
191 if (bl_counter<5): |
| 245 | 192 bad_luck=True |
| 193 else: | |
| 194 bad_luck=False | |
| 195 | |
| 55 | 196 fieldMap = self.getFieldNameMap(data['fields']) |
| 261 | 197 geomdata=None |
| 247 | 198 if (geomField!="point"): |
| 261 | 199 # first check if the file is registered as geo-dataset (which then should have an attribute "the_geom") |
| 200 requeststring="select f_geometry_column from geometry_columns where f_table_schema=%s and f_table_name=%s " | |
| 201 geocolumn_res=self.executeSQL(requeststring,(schema,table)) | |
| 202 if len(geocolumn_res['rows'])>0: | |
| 203 geocolumn=geocolumn_res['rows'][0][0] | |
| 204 try: | |
| 205 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! | |
| 247 | 206 geomdata=self.executeSQL(geomstr) |
| 261 | 207 teststr=geomdata.values()[1][0] |
| 208 if (teststr == (u'MULTIPOLYGON EMPTY',)): | |
| 209 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! | |
| 210 geomdata=self.executeSQL(geomstr) | |
| 211 | |
| 212 except: | |
| 213 try: | |
| 214 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! | |
| 215 geomdata=self.executeSQL(geomstr) | |
| 216 except: | |
| 217 geomdata=None | |
| 233 | 218 |
| 219 if (gisIdField is None) and (latField is None or lonField is None) and geomField=='point': | |
| 55 | 220 # no fields given - choose automagically |
| 261 | 221 sql = "SELECT field_name FROM public.gis_table_meta_rows WHERE table_name = %s and gis_type = %s" |
| 55 | 222 # gis id in metadata first |
| 61 | 223 res = self.executeSQL(sql, (table,'gis_id')) |
| 55 | 224 if len(res['rows']) > 0: |
| 225 gisIdField = res['rows'][0][0] | |
|
255
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
226 # latitude in metadata |
|
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
227 res = self.executeSQL(sql, (table,'coord_lat')) |
|
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
228 if len(res['rows']) > 0: |
|
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
229 latField = res['rows'][0][0] |
|
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
230 # longitude in metadata |
|
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
231 res = self.executeSQL(sql, (table,'coord_lon')) |
|
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
232 if len(res['rows']) > 0: |
|
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
233 lonField = res['rows'][0][0] |
| 93 | 234 |
| 233 | 235 if (gisIdField is None) and (latField is None or lonField is None) and geomField=='point': |
| 93 | 236 logging.warning("no entry in metadata table for table %s" % table) |
| 237 # still no fields - try field names | |
| 238 if 'latitude' in fieldMap and 'longitude' in fieldMap: | |
| 239 latField = 'latitude' | |
| 240 lonField = 'longitude' | |
| 241 elif 'x_coord' in fieldMap and 'y_coord' in fieldMap: | |
| 242 latField = 'x_coord' | |
| 243 lonField = 'y_coord' | |
| 244 else: | |
| 245 logging.error("getKMLdata unable to find position fields") | |
| 263 | 246 self.REQUEST.RESPONSE.write("Please define Position field") |
| 93 | 247 return None |
| 248 | |
| 55 | 249 |
| 250 # convert field names to row indexes | |
| 251 gisIdIdx = fieldMap.get(gisIdField,None) | |
| 252 latIdx = fieldMap.get(latField,None) | |
| 253 lonIdx = fieldMap.get(lonField,None) | |
| 117 | 254 the_geom=fieldMap.get("the_geom",None) |
| 55 | 255 logging.debug("gisidfield=%s idx=%s"%(gisIdField,gisIdIdx)) |
| 256 | |
| 257 # convert data | |
| 258 kmlData = [] | |
| 151 | 259 geom_list = {} |
| 261 | 260 # try: |
| 261 # if geomField=='poly' or geomField=='line': | |
| 262 # geom_list=geomdata.values()[1] | |
| 263 # except: | |
| 264 # return "no geomdata in RestDbGisApi Line 254" | |
| 265 | |
| 150 | 266 data_list=data['rows'] |
| 267 for k in range (len(data_list)): | |
| 268 dataset = data_list[k] | |
| 269 if len(geom_list)>k: | |
| 270 geom_value = geom_list[k] | |
| 271 | |
| 272 | |
| 261 | 273 if gisIdIdx != None and geomField!='point': |
| 274 gisID = dataset[gisIdIdx] | |
| 275 geomdata = self.getLineForChGisId(gisID) | |
| 276 | |
| 150 | 277 # for dataset in data['rows']: |
| 65 | 278 xCoord = 0.0 |
| 279 yCoord = 0.0 | |
|
255
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
280 if gisIdIdx != None and geomField=='point' : |
| 55 | 281 gisID = dataset[gisIdIdx] |
|
257
61525f0ca492
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
256
diff
changeset
|
282 if gisID != " " and gisID != " 0": |
|
255
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
283 coords=self.getPointForChGisId(gisID) |
|
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
284 if coords!=None: |
|
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
285 xCoord=coords[0] |
|
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
286 yCoord=coords[1] |
|
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
287 elif latIdx != None: |
|
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
288 xCoord = dataset[lonIdx] |
|
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
289 yCoord = dataset[latIdx] |
|
ec7b63319fad
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
254
diff
changeset
|
290 elif latIdx != None: |
| 55 | 291 xCoord = dataset[lonIdx] |
| 292 yCoord = dataset[latIdx] | |
| 293 | |
| 233 | 294 elif geomField=='point' : |
| 55 | 295 logging.error("getKMLdata unable to find position") |
| 296 return None | |
| 297 | |
| 233 | 298 if geomField=='point' : |
| 262 | 299 if type(xCoord).__name__=='string': |
| 300 if float(xCoord) == 0.0: | |
| 301 continue | |
| 302 else: | |
| 303 if xCoord == 0.0: | |
| 304 continue | |
| 305 if type(yCoord).__name__=='string': | |
| 306 if float(yCoord) == 0.0: | |
| 307 continue | |
| 308 else: | |
| 309 if yCoord == 0.0: | |
| 310 continue | |
| 55 | 311 |
| 312 kmlPlace = {} | |
| 313 | |
| 314 # description | |
| 315 desc = '' | |
| 150 | 316 |
| 262 | 317 timestring = '' |
| 264 | 318 beg_yr = '-9999' |
| 319 end_yr = '9999' | |
| 263 | 320 from_year = '' |
| 321 until_year = '' | |
| 322 sql = "SELECT field_name FROM public.gis_table_meta_rows WHERE table_name = %s and gis_type = %s" | |
| 323 # from_year and until_year in metadata first | |
| 264 | 324 try: |
| 325 res = self.executeSQL(sql, (table,'from_year')) | |
| 326 if len(res['rows']) > 0: | |
| 327 from_year = res['rows'][0][0] | |
| 328 except: | |
| 329 from_year = "from_year_dummy" | |
| 330 try: | |
| 331 res = self.executeSQL(sql, (table,'until_year')) | |
| 332 if len(res['rows']) > 0: | |
| 333 until_year = res['rows'][0][0] | |
| 334 except: | |
| 335 until_year = "until_year_dummy" | |
| 336 | |
| 150 | 337 for i in range (len(dataset)): |
| 338 value = dataset[i] | |
| 339 | |
| 55 | 340 name = data['fields'][i][0] |
| 58 | 341 #logging.debug("value=%s"%value) |
| 150 | 342 if name != 'the_geom': |
| 343 if value != None: | |
| 57 | 344 #if name.find('name')>-1: |
| 345 # desc += "<name>%s</name>\n"%value | |
| 346 # continue | |
| 347 #elif name.find('place')>-1: | |
| 348 # desc += "<name>%s</name>\n"%value | |
| 349 # continue | |
| 262 | 350 |
| 55 | 351 val = "%s: %s"%(name, value) |
| 187 | 352 value=unicode(value) |
| 263 | 353 if name == from_year: |
| 262 | 354 beg_yr= value |
| 263 | 355 if name == until_year: |
| 262 | 356 end_yr=value |
| 188 | 357 # If there is a link within the description data, create a valid href |
| 191 | 358 if value.find('http://')>-1: |
| 359 link_str_beg=value.find('http://') | |
| 360 link_str_end = -1 | |
| 361 link_str_end0=value.find(' ',link_str_beg) | |
| 362 link_str_end1=value.find('>',link_str_beg) | |
| 363 if link_str_end0 <link_str_end1: | |
| 364 link_str_end=link_str_end0 | |
| 365 else: | |
| 366 link_str_end=link_str_end1 | |
| 367 if link_str_end > -1: | |
| 368 link_str=value[link_str_beg:link_str_end] | |
| 207 | 369 val =name+": "+value[0:link_str_beg]+'<a href=' + link_str + '> ' + link_str.replace('http://','') + ' </a>' + value[link_str_end:] |
| 191 | 370 else: |
| 371 link_str=value[link_str_beg:] | |
| 207 | 372 val =name+': '+value[0:link_str_beg]+'<a href=' + link_str + '> ' + link_str.replace('http://','') + ' </a>' |
| 188 | 373 |
| 374 | |
| 57 | 375 #desc += kmlEncode(val) |
| 376 desc += val | |
| 55 | 377 desc += '<br/>\n' |
| 378 | |
| 57 | 379 #kmlPlace['description'] = "<![CDATA[%s]]>"%desc |
| 150 | 380 |
| 264 | 381 if end_yr!='9999': |
| 263 | 382 kmlPlace['TimeSpan0'] = '%s'%beg_yr |
| 383 kmlPlace['TimeSpan1'] = '%s'%end_yr | |
| 264 | 384 else: |
| 263 | 385 kmlPlace['TimeStamp'] = '%s'%beg_yr |
| 124 | 386 |
| 133 | 387 if geomField=='point': |
| 207 | 388 kmlPlace['description'] = "<![CDATA[%s]]>"%desc |
| 193 | 389 |
| 133 | 390 kmlPlace['icon'] = '#marker_icon_'+colorField |
| 391 kmlPlace['coord_x'] = str(xCoord) | |
| 392 kmlPlace['coord_y'] = str(yCoord) | |
|
258
ed99a2468264
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
257
diff
changeset
|
393 kmlPlace['coord_z'] = '50' |
| 134 | 394 kmlData.append(kmlPlace) |
| 261 | 395 if (geomField=='poly' or geomField=='line') and geomdata is not None: |
| 396 polys=str(geomdata).split('(') | |
| 144 | 397 aaa=len(polys) |
| 142 | 398 for poly in polys: |
| 150 | 399 kmlPlace = {} |
| 400 kmlPlace['description'] = desc | |
| 152 | 401 coords=poly.replace(')','').replace("'","").split(',') |
| 150 | 402 coord_string='' |
| 144 | 403 if len(coords)>1: |
| 146 | 404 for coord in coords: |
| 405 coord=coord.split(' ') | |
| 406 try: | |
| 407 x_coord=coord[0] | |
| 408 y_coord=coord[1] | |
| 409 except: | |
| 410 break | |
| 150 | 411 coord_string+=x_coord+','+y_coord+','+'0 ' |
| 412 if coord_string != '': | |
| 413 kmlPlace['LinearRing']=coord_string | |
| 245 | 414 kmlPlace['lineColor']='#'+colorField+'_'+geomField |
| 150 | 415 kmlData.append(kmlPlace) |
| 262 | 416 |
| 417 | |
| 55 | 418 #logging.debug("kmlData=%s"%(repr(kmlData))) |
| 419 return kmlData | |
| 58 | 420 |
| 421 def getPointForChGisId(self, gis_id): | |
|
260
e9a1ac7d2ab2
Bug resolved in request point_id from Harvard ChGIS
fknauft
parents:
259
diff
changeset
|
422 """returns coordinate pair for given gis_id""" # gets called by getKml |
| 59 | 423 def getPoint(id): |
| 247 | 424 str_gis_id=str(id).split('.')[0] |
| 425 sql="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id LIKE CAST('%s' AS text)"%str(str_gis_id) | |
| 426 # logging.error("sql:",sql) | |
| 427 # res = self.executeSQL(sql, (str(str_gis_id),)) | |
| 428 res = self.executeSQL(sql) | |
| 59 | 429 if len(res['rows']) > 0: |
| 430 return res['rows'][0] | |
| 247 | 431 else: |
| 432 #logging.error("error on sql:",sql%(str(str_gis_id),)) | |
| 433 return None | |
| 55 | 434 |
| 58 | 435 if gis_id is None or gis_id == "": |
| 436 return None | |
| 261 | 437 if len(str(gis_id)) < 4: |
|
260
e9a1ac7d2ab2
Bug resolved in request point_id from Harvard ChGIS
fknauft
parents:
259
diff
changeset
|
438 return None |
| 58 | 439 |
| 59 | 440 # try gis_id |
| 441 coords = getPoint(gis_id) | |
| 442 if coords is None: | |
| 58 | 443 # try to clean gis_id... |
| 247 | 444 gis_id_short = re.sub(r'[^0-9]','',gis_id) |
| 58 | 445 # try again |
| 247 | 446 coords = getPoint(gis_id_short) |
| 59 | 447 if coords is None: |
| 247 | 448 #logging.error("CH-GIS ID %s not found!"%str(gis_id_short)) |
| 449 | |
| 450 # this will ask the Harvard-Service for the Coords of this gis_id and write it into our coords-list | |
| 451 try: | |
| 452 coords=self.getCoordsFromREST_gisID(gis_id) | |
| 453 #logging.error("coords from REST"%str(coords)) | |
| 454 except: | |
| 455 logging.error("coords from REST did not work for "%str(gis_id)) | |
| 456 if coords[0] is None: | |
| 457 logging.error("CH-GIS ID %s not found in Harvard"%str(gis_id)) | |
| 458 else: | |
| 459 try: | |
| 460 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))" | |
| 461 SQL_analyze="ANALYZE chgis.chgis_coords" | |
| 462 self.executeSQL(SQL,(str(gis_id_short),str(coords[0][1]),str(coords[0][0])),False) | |
| 463 self.executeSQL(SQL_analyze,(),False) | |
| 464 except: | |
| 465 logging.error("Could not write into chgis_coords: ", str(gis_id_short)) | |
| 466 logging.error("coords[0][0]:"%coords[0][0]) | |
| 467 logging.error("coords[0][1]:"%coords[0][1]) | |
| 468 return coords[0] | |
| 469 coords_test = getPoint(gis_id) | |
| 470 #logging.error("SQL results now:", str(coords_test)) | |
| 471 return coords[0] | |
| 58 | 472 |
| 59 | 473 return coords |
| 474 | |
| 55 | 475 |
| 261 | 476 def getLineForChGisId(self, gis_id): |
| 477 """returns line/poly coordinates for given gis_id""" # gets called by getKml | |
| 478 def getLine(id): | |
| 479 str_gis_id=str(id).split('.')[0] | |
| 480 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) | |
| 481 # logging.error("sql:",sql) | |
| 482 # res = self.executeSQL(sql, (str(str_gis_id),)) | |
| 483 res = self.executeSQL(sql) | |
| 484 if len(res['rows']) > 0: | |
| 485 return res['rows'][0] | |
| 486 else: | |
| 487 #logging.error("error on sql:",sql%(str(str_gis_id),)) | |
| 488 return None | |
| 489 | |
| 490 if gis_id is None or gis_id == "": | |
| 491 return None | |
| 492 if len(str(gis_id)) < 4: | |
| 493 return None | |
| 494 | |
| 495 # try gis_id | |
| 496 line_coords = getLine(gis_id) | |
| 497 if line_coords is None: | |
| 498 # try to clean gis_id... | |
| 499 gis_id_short = re.sub(r'[^0-9]','',gis_id) | |
| 500 # try again | |
| 501 line_coords = getLine(gis_id_short) | |
| 502 if line_coords is None: | |
| 503 logging.error("CH-GIS ID %s not found!"%(gis_id_short)) | |
| 504 | |
| 505 | |
| 506 return line_coords | |
| 507 | |
| 59 | 508 ## legacy methods... |
| 509 | |
|
254
901c1f745d13
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
252
diff
changeset
|
510 def getKmlUrl(self,schema='chgis',table='mpdl',args={'doc':None,'id':None}): |
| 59 | 511 logging.debug("getKmlUrl") |
|
254
901c1f745d13
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
252
diff
changeset
|
512 id=args.get('id') |
|
901c1f745d13
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
252
diff
changeset
|
513 doc=args.get('doc') |
| 59 | 514 data = self.getDataForGoogleMap(schema,table,id,doc) |
| 515 kml=self.getKMLname(data=data,table=table) | |
| 516 baseUrl = self.absolute_url() | |
| 517 return "%s/daten/%s"%(baseUrl,kml) | |
| 518 | |
| 519 def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=None,doc=None): | |
| 520 logging.debug("getDataForGoogleMap") | |
| 521 qstr="SELECT * FROM "+schema+"."+table | |
| 522 try: | |
| 523 if id is not None: | |
| 524 qstr=qstr+" WHERE " | |
| 525 for id_item in id.split(","): | |
| 526 if table=='mpdl': | |
| 527 qstr=qstr+" mpdl_xmlsource_id = '"+id_item+ "' OR" | |
| 528 else: | |
| 529 qstr=qstr+" cast(id as text) LIKE '"+id_item+ "' OR" | |
| 530 qstr=str(qstr).rsplit(" ",1)[0] #to remove last " and " | |
| 531 data=self.ZSQLSimpleSearch(qstr) | |
| 532 return data | |
| 533 except: | |
| 534 return qstr | |
| 55 | 535 |
| 536 | |
| 43 | 537 def getKMLname(self,data=[],table=""): |
| 538 logging.debug("getKMLname") | |
| 539 #session=context.REQUEST.SESSION | |
| 109 | 540 kml4Marker="<kml xmlns=\'http://www.opengis.net/kml/2.2\'><Document>" |
| 541 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" | |
| 542 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" | |
| 543 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" | |
| 544 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" | |
| 545 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" | |
| 43 | 546 initializeStringForGoogleMaps="" |
| 547 #doLine=container.getVar('doLine') | |
| 548 # Mapping a set of points from table-based SQL-query: | |
| 549 if data!=None: | |
| 550 try: | |
| 551 SQL='SELECT "attribute with gis_id" FROM public.metadata WHERE tablename = %s' | |
| 552 res = self.executeSQL(SQL, (table,)) | |
| 553 gisIDattribute = res['rows'][0][0] | |
| 554 except: | |
| 555 return "table not registered within metadata" | |
| 556 | |
| 557 for dataset in data: | |
| 558 try: | |
| 559 xCoord=getattr(dataset,'longitude') | |
| 560 yCoord=getattr(dataset,'latitude') | |
| 561 except: | |
| 562 try: | |
| 563 xCoord=getattr(dataset,'x_coord') | |
| 564 yCoord=getattr(dataset,'y_coord') | |
| 565 except: | |
| 566 #try: | |
| 567 gisID=getattr(dataset,gisIDattribute) | |
| 568 coords=self.getPoint4GISid(gisID) | |
| 569 if coords!=None: | |
| 570 xCoord=coords[0] | |
| 571 yCoord=coords[1] | |
| 572 # except: | |
| 573 # return "no coordinates found" | |
| 574 | |
| 575 if float(xCoord)!=0: | |
| 576 if float(yCoord)!=0: | |
| 577 kml4Marker=kml4Marker+"<Placemark>" | |
| 578 kml4Marker=kml4Marker+"<description> <![CDATA[<b>" | |
| 579 for values in dataset: | |
| 55 | 580 #logging.debug("values=%s"%repr(values)) |
| 43 | 581 if values != (None, None): |
| 582 if str(values).find('name')>-1: | |
| 583 kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n" | |
| 584 continue | |
| 585 elif str(values).find('place')>-1: | |
| 586 kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n" | |
| 587 continue | |
| 588 | |
| 589 kml4Marker=kml4Marker+str(values)+": " | |
| 590 attribute_string=str(values).replace("'","__Apostroph__") | |
| 591 attribute_string=str(attribute_string).replace('"','__DoubleApostroph__') | |
| 592 attribute_string=str(attribute_string).replace(';','__$$__') | |
| 593 attribute_string=str(attribute_string).replace('&','&') | |
| 594 if str(attribute_string).find('http')>-1: | |
| 207 | 595 attribute_string='<A HREF=' + str(attribute_string) + ' target="_blank">' + str(attribute_string) + '</A>' |
| 43 | 596 kml4Marker=kml4Marker+attribute_string+"</a><br>\n" |
| 597 | |
| 598 kml4Marker=kml4Marker+"]]></description>\n" | |
| 117 | 599 kml4Marker=kml4Marker+"<styleURL>#marker_icon_red</styleURL>\n" |
| 43 | 600 kml4Marker=kml4Marker+"<Point>" |
| 601 | |
| 602 kml4Marker=kml4Marker+"<coordinates>"+str(xCoord)+","+str(yCoord)+",0</coordinates>\n" | |
| 603 kml4Marker=kml4Marker+"</Point>\n" | |
| 604 kml4Marker=kml4Marker+"</Placemark>\n" | |
| 605 | |
| 606 kml4Marker=kml4Marker+"</Document>\n</kml>" | |
| 607 kmlFileName="marker"+str(time.time())+".kml" | |
| 608 | |
| 609 #kml4Marker=str(kml4Marker).replace('&','$$') | |
| 610 #kml4Marker=str(kml4Marker).replace(';','__$$__') | |
| 611 #kml4Marker=str(kml4Marker).replace('#','__SHARP__') | |
| 612 isLoadReady='false' | |
| 613 while isLoadReady=='false': | |
| 614 isLoadReady=self.RESTwrite2File(self.daten,kmlFileName,kml4Marker) | |
| 615 | |
| 616 return kmlFileName | |
| 207 | 617 |
| 618 def trydatahas_key(self,data,index,key_string): | |
| 619 try: | |
| 620 return data[index].has_key(key_string) | |
| 621 except: | |
| 622 return 'false' | |
| 43 | 623 |
| 44 | 624 # def getGoogleMapString(self,kml): |
| 625 # logging.debug("getGoogleMapString") | |
| 626 # printed= '<body %s> '%kml +"""\n <div id="map_canvas" style="width: 98%; height: 95%"> </div> \n </body>" \n </html>""" | |
| 627 # return printed | |
| 43 | 628 |
| 629 def getPoint4GISid(self,gis_id): | |
| 630 j=0 | |
| 631 coords=(0,0) | |
| 632 if gis_id != None: | |
| 633 while (True): | |
| 634 j=j+1 | |
| 635 if (j>100): # FJK: just to prevent endless loops | |
| 636 break | |
| 637 if (gis_id.isdigit()): # FJK: regular exit from while-loop | |
| 638 break | |
| 639 else: | |
| 640 gis_id=gis_id.strip('abcdefghijklmnopqrstuvwxyz_') # FJK: to strip all letters | |
| 641 gis_id=gis_id.strip() # FJK: to strip all whitespaces | |
| 642 resultpoint = [0,0] | |
| 643 results = None | |
| 644 try: | |
| 645 if int(gis_id)>0: | |
| 646 SQL="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id LIKE cast("+ str(gis_id) +" as text);" | |
| 647 results=self.ZSQLSimpleSearch(SQL) | |
| 648 #print results | |
| 649 if results != None: | |
| 650 for result in results: | |
| 651 resultpoint=[getattr(result,str('x_coord')),getattr(result,str('y_coord'))] | |
| 652 if resultpoint !=[0,0]: | |
| 653 return resultpoint | |
| 654 else: | |
| 247 | 655 coords=self.getCoordsFromREST_gisID(gis_id) |
| 656 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;" | |
| 43 | 657 returnstring=self.ZSQLSimpleSearch(SQL) |
| 658 return coords[0] | |
| 659 except: | |
| 660 return "gis_id not to interpretable:"+str(gis_id) | |
| 661 else: | |
| 662 return coords[0] | |
| 663 | |
| 664 def getCoordsFromREST_gisID(self,gis_id): | |
| 247 | 665 gis_id=gis_id.strip() |
| 43 | 666 coordlist=[] |
| 667 i=0 | |
| 668 while (i<5 and coordlist==[]): | |
|
256
cb953cc153a6
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
255
diff
changeset
|
669 urlinfo=urlFunctions.zUrlopenInfo(self,"http://chgis.hmdc.harvard.edu/xml/id/"+gis_id) |
|
cb953cc153a6
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
255
diff
changeset
|
670 urlinfoLength=urlinfo.get('content-length') |
|
cb953cc153a6
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
255
diff
changeset
|
671 if int(urlinfoLength)<500: |
|
cb953cc153a6
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
255
diff
changeset
|
672 urlresponse=urlFunctions.zUrlopenRead(self,"http://chgis.hmdc.harvard.edu/xml/id/cts_"+gis_id) |
|
cb953cc153a6
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
255
diff
changeset
|
673 else: |
|
cb953cc153a6
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
255
diff
changeset
|
674 urlresponse=urlFunctions.zUrlopenRead(self,"http://chgis.hmdc.harvard.edu/xml/id/"+gis_id) |
|
cb953cc153a6
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
255
diff
changeset
|
675 urlresponseString=urlFunctions.zUrlopenParseString(self,urlresponse) |
|
cb953cc153a6
GIS-Links from MPDL-Documents now served by Mappit-Server
fknauft
parents:
255
diff
changeset
|
676 baseDocElement= urlFunctions.zUrlopenDocumentElement(self,urlresponseString) |
| 247 | 677 childnodes=urlFunctions.zUrlopenChildNodes(self,baseDocElement) |
| 678 itemnodes=urlFunctions.zUrlopenGetElementsByTagName(self,baseDocElement,'item') | |
| 679 itemspatialnodes=None | |
| 680 | |
| 681 for i in range(0,urlFunctions.zUrlopenLength(self,itemnodes)): | |
| 682 itemnode=urlFunctions.zUrlopenGetItem(self,itemnodes,i) | |
| 683 itemspatialnodes=urlFunctions.zUrlopenGetElementsByTagName(self,itemnode,'spatial') | |
| 684 if itemspatialnodes is not None: | |
| 685 for j in range(0,urlFunctions.zUrlopenLength(self,itemspatialnodes)): | |
| 686 coord=[] | |
| 687 itemspatialnode= urlFunctions.zUrlopenGetItem(self,itemspatialnodes,j) | |
| 688 itemspatiallatnodes=urlFunctions.zUrlopenGetElementsByTagName(self,itemspatialnode,'degrees_latitude') | |
| 689 for k in range(0,urlFunctions.zUrlopenLength(self,itemspatiallatnodes)): | |
| 690 itemspatiallatnode= urlFunctions.zUrlopenGetItem(self,itemspatiallatnodes,k) | |
| 691 coord.append(urlFunctions.zUrlopenGetTextData(self,itemspatiallatnode)) | |
| 692 itemspatiallngnodes=urlFunctions.zUrlopenGetElementsByTagName(self,itemspatialnode,'degrees_longitude') | |
| 693 for k in range(0,urlFunctions.zUrlopenLength(self,itemspatiallngnodes)): | |
| 694 itemspatiallngnode= urlFunctions.zUrlopenGetItem(self,itemspatiallngnodes,k) | |
| 695 coord.append(urlFunctions.zUrlopenGetTextData(self,itemspatiallngnode)) | |
| 696 coordlist.append(coord) | |
| 697 gis_id= "_"+gis_id | |
| 43 | 698 return coordlist |
| 699 | |
| 700 # End for GoogleMaps creation | |
| 701 | |
| 702 def RESTwrite2File(self,datadir, name,text): | |
| 703 logging.debug("RESTwrite2File datadir=%s name=%s"%(datadir,name)) | |
| 704 try: | |
| 705 import cStringIO as StringIO | |
| 706 except: | |
| 707 import StringIO | |
| 708 | |
| 709 # make filehandle from string | |
| 710 textfile = StringIO.StringIO(text) | |
| 711 fileid=name | |
| 712 if fileid in datadir.objectIds(): | |
| 713 datadir.manage_delObjects(fileid) | |
| 714 fileInZope=datadir.manage_addFile(id=fileid,file=textfile) | |
| 715 return "Write successful" | |
| 716 | |
| 717 def manage_editRestDbGisApi(self, title=None, connection_id=None, | |
| 718 REQUEST=None): | |
| 719 """Change the object""" | |
| 720 if title is not None: | |
| 721 self.title = title | |
| 722 | |
| 723 if connection_id is not None: | |
| 724 self.connection_id = connection_id | |
| 725 | |
| 726 #checkPermission=getSecurityManager().checkPermission | |
| 727 REQUEST.RESPONSE.redirect('manage_main') | |
| 728 | |
| 729 | |
| 730 manage_addRestDbGisApiForm=PageTemplateFile('zpt/addRestDbGisApi',globals()) | |
| 731 | |
| 732 def manage_addRestDbGisApi(self, id, title='', label='', description='', | |
| 733 createPublic=0, | |
| 734 createUserF=0, | |
| 735 REQUEST=None): | |
| 736 """Add a new object with id *id*.""" | |
| 737 | |
| 738 ob=RestDbGisApi(str(id),title) | |
| 739 self._setObject(id, ob) | |
| 740 | |
| 741 #checkPermission=getSecurityManager().checkPermission | |
| 742 REQUEST.RESPONSE.redirect('manage_main') | |
| 743 | |
| 247 | 744 |
| 745 | |
| 746 |