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