|
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 json
|
|
|
13 import time
|
|
|
14
|
|
|
15 from RestDbInterface import *
|
|
|
16
|
|
|
17
|
|
|
18 gisToSqlTypeMap = {
|
|
|
19 "text": "text",
|
|
|
20 "number": "numeric",
|
|
|
21 "id": "text",
|
|
|
22 "gis_id": "text",
|
|
|
23 "coord_lat": "numeric",
|
|
|
24 "coord_lon": "numeric"
|
|
|
25 }
|
|
|
26
|
|
|
27 class RestDbGisApi(RestDbInterface):
|
|
|
28 """Object for RESTful GIS database queries
|
|
|
29 path schema: /db/{schema}/{table}/
|
|
|
30 omitting table gives a list of schemas
|
|
|
31 omitting table and schema gives a list of schemas
|
|
|
32 """
|
|
|
33
|
|
|
34 meta_type="RESTgis"
|
|
|
35
|
|
|
36 # data templates
|
|
|
37 GIS_schema_table = PageTemplateFile('zpt/GIS_schema_table', globals())
|
|
|
38
|
|
|
39 def checkTableMetaPermission(self,action,schema,table,user=None):
|
|
|
40 """returns if the requested action on the table is allowed"""
|
|
|
41 logging.debug("checktablemetapermissions action=%s schema=%s table=%s user=%s"%(action,schema,table,user))
|
|
|
42 if user is None:
|
|
|
43 user = self.REQUEST.get('AUTHENTICATED_USER',None)
|
|
|
44 logging.debug("user=%s"%user)
|
|
|
45 # TODO: what now?
|
|
|
46 return True
|
|
|
47
|
|
|
48 def setTableMetaTypes(self,schema,table,fields):
|
|
|
49 """sets the GIS meta information for table"""
|
|
|
50 logging.debug("settablemetatypes schema=%s, table=%s, fields=%s"%(schema,table,fields))
|
|
|
51 gisIdField = None
|
|
|
52 latField = None
|
|
|
53 lonField = None
|
|
|
54 for f in fields:
|
|
|
55 t = f['type']
|
|
|
56 if t == 'gis_id':
|
|
|
57 gisIdField = f['name']
|
|
|
58 elif t == 'coord_lat':
|
|
|
59 latField = f['name']
|
|
|
60 elif t == 'coord_lon':
|
|
|
61 lonField = f['name']
|
|
|
62
|
|
|
63 res = self.executeSQL("select * from public.metadata where tablename=%s", (table,))
|
|
|
64 if len(res['rows']) > 0:
|
|
|
65 # meta record exists
|
|
|
66 if gisIdField is not None:
|
|
|
67 self.executeSQL('update public.metadata set "attribute with gis_id" = %s where tablename = %s', (gisIdField,table), hasResult=False)
|
|
|
68
|
|
|
69 else:
|
|
|
70 # new meta record
|
|
|
71 if gisIdField is not None:
|
|
|
72 self.executeSQL('insert into public.metadata ("tablename", "attribute with gis_id") values (%s, %s)', (table,gisIdField), hasResult=False)
|
|
|
73
|
|
|
74
|
|
|
75 def showTable(self,resultFormat='XML',schema='public',table=None,REQUEST=None,RESPONSE=None):
|
|
|
76 """returns PageTemplate with tables"""
|
|
|
77 logging.debug("showtable")
|
|
|
78 if REQUEST is None:
|
|
|
79 REQUEST = self.REQUEST
|
|
|
80
|
|
|
81 # should be cross-site accessible
|
|
|
82 if RESPONSE is None:
|
|
|
83 RESPONSE = self.REQUEST.RESPONSE
|
|
|
84
|
|
|
85 RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
|
|
|
86
|
|
|
87 # GIS gets special treatment
|
|
|
88 if resultFormat=="GIS":
|
|
|
89 id = REQUEST.get('id',[])
|
|
|
90 doc = REQUEST.get('doc',None)
|
|
|
91 return self.showGoogleMap(schema=schema,table=table,id=id,doc=doc)
|
|
|
92
|
|
|
93 elif resultFormat=="KML_URL":
|
|
|
94 id = REQUEST.get('id',[])
|
|
|
95 doc = REQUEST.get('doc',None)
|
|
|
96 return self.getKmlUrl(schema=schema,table=table,id=id,doc=doc)
|
|
|
97
|
|
|
98 # everything else has its own template
|
|
|
99 pt = getattr(self.template, '%s_schema_table'%resultFormat, None)
|
|
|
100 if pt is None:
|
|
|
101 return "ERROR!! template %s_schema_table not found"%resultFormat
|
|
|
102
|
|
|
103 data = self.getTable(schema,table)
|
|
|
104 return pt(data=data,tablename=table)
|
|
|
105
|
|
|
106
|
|
|
107 def createEmptyTable(self,schema,table,fields):
|
|
|
108 """create a table with the given fields
|
|
|
109 returns list of created fields"""
|
|
|
110 logging.debug("createEmptyTable")
|
|
|
111 sqlFields = []
|
|
|
112 for f in fields:
|
|
|
113 if isinstance(f,dict):
|
|
|
114 # {name: XX, type: YY}
|
|
|
115 name = sqlName(f['name'])
|
|
|
116 type = f['type']
|
|
|
117 sqltype = gisToSqlTypeMap[type]
|
|
|
118
|
|
|
119 else:
|
|
|
120 # name only
|
|
|
121 name = sqlName(f)
|
|
|
122 type = 'text'
|
|
|
123 sqltype = 'text'
|
|
|
124
|
|
|
125 sqlFields.append({'name':name, 'type':type, 'sqltype':sqltype})
|
|
|
126
|
|
|
127 if self.checkTableMetaPermission("create", schema, table):
|
|
|
128 self.executeSQL('drop table if exists "%s"."%s"'%(schema,table),hasResult=False)
|
|
|
129 fieldString = ", ".join(['"%s" %s'%(f['name'],f['sqltype']) for f in sqlFields])
|
|
|
130 sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString)
|
|
|
131 logging.debug("createemptytable: SQL=%s"%sqlString)
|
|
|
132 self.executeSQL(sqlString,hasResult=False)
|
|
|
133 self.setTableMetaTypes(schema,table,sqlFields)
|
|
|
134 return sqlFields
|
|
|
135 else:
|
|
|
136 logging.warning("create table not allowed!")
|
|
|
137 # throw exception?
|
|
|
138 return None
|
|
|
139
|
|
|
140
|
|
|
141
|
|
|
142 # Methods for GoogleMaps creation
|
|
|
143 def showGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None):
|
|
|
144 logging.debug("showGoogleMap")
|
|
|
145 data = self.getDataForGoogleMap(schema,table,id,doc)
|
|
|
146 kmlFileName=self.getKMLname(data=data,table=table)
|
|
|
147 initializeStringForGoogleMaps="""onload=\"initialize(\'http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kmlFileName+"""\')\""""#+str(data)
|
|
|
148 initializeStringForGoogleMaps=initializeStringForGoogleMaps.replace("None","0")
|
|
|
149 googleMap_page=self.htmlHead()+str(self.getGoogleMapString(kml=initializeStringForGoogleMaps))
|
|
|
150 return googleMap_page
|
|
|
151
|
|
|
152 def getKmlUrl(self,schema='chgis',table='mpdl',id=[],doc=None):
|
|
|
153 logging.debug("getKmlUrl")
|
|
|
154 data = self.getDataForGoogleMap(schema,table,id,doc)
|
|
|
155 kml=self.getKMLname(data=data,table=table)
|
|
|
156 baseUrl = self.absolute_url()
|
|
|
157 return "%s/daten/%s"%(baseUrl,kml)
|
|
|
158
|
|
|
159 def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None):
|
|
|
160 logging.debug("getDataForGoogleMap")
|
|
|
161 qstr="SELECT * FROM "+schema+"."+table
|
|
|
162 try:
|
|
|
163 if id!=[]:
|
|
|
164 qstr=qstr+" WHERE "
|
|
|
165 for id_item in id.split(","):
|
|
|
166 if table=='mpdl':
|
|
|
167 qstr=qstr+" mpdl_xmlsource_id = '"+id_item+ "' OR"
|
|
|
168 else:
|
|
|
169 qstr=qstr+" cast(id as text) LIKE '"+id_item+ "' OR"
|
|
|
170 qstr=str(qstr).rsplit(" ",1)[0] #to remove last " and "
|
|
|
171 data=self.ZSQLSimpleSearch(qstr)
|
|
|
172 return data
|
|
|
173 except:
|
|
|
174 return qstr
|
|
|
175
|
|
|
176 def getKMLname(self,data=[],table=""):
|
|
|
177 logging.debug("getKMLname")
|
|
|
178 #session=context.REQUEST.SESSION
|
|
|
179 kml4Marker="<kml xmlns=\'http://www.opengis.net/kml/2.2\'><Document><Style id=\'marker_icon\'><IconStyle><scale>15</scale><Icon><href>http://chinagis.mpiwg-berlin.mpg.de/chinagis/images/dot_red.png</href></Icon></IconStyle></Style>\n"
|
|
|
180 initializeStringForGoogleMaps=""
|
|
|
181 #doLine=container.getVar('doLine')
|
|
|
182 # Mapping a set of points from table-based SQL-query:
|
|
|
183 if data!=None:
|
|
|
184 try:
|
|
|
185 SQL='SELECT "attribute with gis_id" FROM public.metadata WHERE tablename = %s'
|
|
|
186 res = self.executeSQL(SQL, (table,))
|
|
|
187 gisIDattribute = res['rows'][0][0]
|
|
|
188 except:
|
|
|
189 return "table not registered within metadata"
|
|
|
190
|
|
|
191 for dataset in data:
|
|
|
192 try:
|
|
|
193 xCoord=getattr(dataset,'longitude')
|
|
|
194 yCoord=getattr(dataset,'latitude')
|
|
|
195 except:
|
|
|
196 try:
|
|
|
197 xCoord=getattr(dataset,'x_coord')
|
|
|
198 yCoord=getattr(dataset,'y_coord')
|
|
|
199 except:
|
|
|
200 #try:
|
|
|
201 gisID=getattr(dataset,gisIDattribute)
|
|
|
202 coords=self.getPoint4GISid(gisID)
|
|
|
203 if coords!=None:
|
|
|
204 xCoord=coords[0]
|
|
|
205 yCoord=coords[1]
|
|
|
206 # except:
|
|
|
207 # return "no coordinates found"
|
|
|
208
|
|
|
209 if float(xCoord)!=0:
|
|
|
210 if float(yCoord)!=0:
|
|
|
211 kml4Marker=kml4Marker+"<Placemark>"
|
|
|
212 kml4Marker=kml4Marker+"<description> <![CDATA[<b>"
|
|
|
213 for values in dataset:
|
|
|
214 if values != (None, None):
|
|
|
215 if str(values).find('name')>-1:
|
|
|
216 kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n"
|
|
|
217 continue
|
|
|
218 elif str(values).find('place')>-1:
|
|
|
219 kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n"
|
|
|
220 continue
|
|
|
221
|
|
|
222 kml4Marker=kml4Marker+str(values)+": "
|
|
|
223 attribute_string=str(values).replace("'","__Apostroph__")
|
|
|
224 attribute_string=str(attribute_string).replace('"','__DoubleApostroph__')
|
|
|
225 attribute_string=str(attribute_string).replace(';','__$$__')
|
|
|
226 attribute_string=str(attribute_string).replace('&','&')
|
|
|
227 if str(attribute_string).find('http')>-1:
|
|
|
228 attribute_string='<A HREF=' + str(attribute_string) + ' target=_blank>' + str(attribute_string) + '</A>'
|
|
|
229 kml4Marker=kml4Marker+attribute_string+"</a><br>\n"
|
|
|
230
|
|
|
231 kml4Marker=kml4Marker+"]]></description>\n"
|
|
|
232 kml4Marker=kml4Marker+"<styleURL>#marker_icon</styleURL>\n"
|
|
|
233 kml4Marker=kml4Marker+"<Point>"
|
|
|
234
|
|
|
235 kml4Marker=kml4Marker+"<coordinates>"+str(xCoord)+","+str(yCoord)+",0</coordinates>\n"
|
|
|
236 kml4Marker=kml4Marker+"</Point>\n"
|
|
|
237 kml4Marker=kml4Marker+"</Placemark>\n"
|
|
|
238
|
|
|
239 kml4Marker=kml4Marker+"</Document>\n</kml>"
|
|
|
240 kmlFileName="marker"+str(time.time())+".kml"
|
|
|
241
|
|
|
242 #kml4Marker=str(kml4Marker).replace('&','$$')
|
|
|
243 #kml4Marker=str(kml4Marker).replace(';','__$$__')
|
|
|
244 #kml4Marker=str(kml4Marker).replace('#','__SHARP__')
|
|
|
245 isLoadReady='false'
|
|
|
246 while isLoadReady=='false':
|
|
|
247 isLoadReady=self.RESTwrite2File(self.daten,kmlFileName,kml4Marker)
|
|
|
248
|
|
|
249 return kmlFileName
|
|
|
250
|
|
|
251 def getGoogleMapString(self,kml):
|
|
|
252 logging.debug("getGoogleMapString")
|
|
|
253 printed= '<body %s> '%kml +"""\n <div id="map_canvas" style="width: 98%; height: 95%"> </div> \n </body>" \n </html>"""
|
|
|
254 return printed
|
|
|
255
|
|
|
256 def getPoint4GISid(self,gis_id):
|
|
|
257 j=0
|
|
|
258 coords=(0,0)
|
|
|
259 if gis_id != None:
|
|
|
260 while (True):
|
|
|
261 j=j+1
|
|
|
262 if (j>100): # FJK: just to prevent endless loops
|
|
|
263 break
|
|
|
264 if (gis_id.isdigit()): # FJK: regular exit from while-loop
|
|
|
265 break
|
|
|
266 else:
|
|
|
267 gis_id=gis_id.strip('abcdefghijklmnopqrstuvwxyz_') # FJK: to strip all letters
|
|
|
268 gis_id=gis_id.strip() # FJK: to strip all whitespaces
|
|
|
269 resultpoint = [0,0]
|
|
|
270 results = None
|
|
|
271 try:
|
|
|
272 if int(gis_id)>0:
|
|
|
273 SQL="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id LIKE cast("+ str(gis_id) +" as text);"
|
|
|
274 results=self.ZSQLSimpleSearch(SQL)
|
|
|
275 #print results
|
|
|
276 if results != None:
|
|
|
277 for result in results:
|
|
|
278 resultpoint=[getattr(result,str('x_coord')),getattr(result,str('y_coord'))]
|
|
|
279 if resultpoint !=[0,0]:
|
|
|
280 return resultpoint
|
|
|
281 else:
|
|
|
282 coords=self.getCoordsFromREST_gisID(joinid)
|
|
|
283 SQL="INSERT INTO chgis.chgis_coords (gis_id,x_coord,y_coord) VALUES (" +gis_id+ "," +coords[0][1]+ "," +coords[0][0]+ "); ANALYZE chgis.chgis_coords;"
|
|
|
284 returnstring=self.ZSQLSimpleSearch(SQL)
|
|
|
285 return coords[0]
|
|
|
286 except:
|
|
|
287 return "gis_id not to interpretable:"+str(gis_id)
|
|
|
288 else:
|
|
|
289 return coords[0]
|
|
|
290
|
|
|
291 def getCoordsFromREST_gisID(self,gis_id):
|
|
|
292 coordlist=[]
|
|
|
293 i=0
|
|
|
294 while (i<5 and coordlist==[]):
|
|
|
295
|
|
|
296 urlresponse=container.urlFunctions.zUrlopenParseString(container.urlFunctions.zUrlopenRead("http://chgis.hmdc.harvard.edu/xml/id/"+gis_id))
|
|
|
297 baseDocElement=container.urlFunctions.zUrlopenDocumentElement(urlresponse)
|
|
|
298 childnodes=container.urlFunctions.zUrlopenChildNodes(baseDocElement)
|
|
|
299 itemnodes=container.urlFunctions.zUrlopenGetElementsByTagName(baseDocElement,'item')
|
|
|
300
|
|
|
301 for i in range(0,container.urlFunctions.zUrlopenLength(itemnodes)):
|
|
|
302 itemnode=container.urlFunctions.zUrlopenGetItem(itemnodes,i)
|
|
|
303 itemspatialnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemnode,'spatial')
|
|
|
304 for j in range(0,container.urlFunctions.zUrlopenLength(itemspatialnodes)):
|
|
|
305 coord=[]
|
|
|
306 itemspatialnode= container.urlFunctions.zUrlopenGetItem(itemspatialnodes,j)
|
|
|
307 itemspatiallatnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_latitude')
|
|
|
308 for k in range(0,container.urlFunctions.zUrlopenLength(itemspatiallatnodes)):
|
|
|
309 itemspatiallatnode= container.urlFunctions.zUrlopenGetItem(itemspatiallatnodes,k)
|
|
|
310 coord.append(container.urlFunctions.zUrlopenGetTextData(itemspatiallatnode))
|
|
|
311 itemspatiallngnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_longitude')
|
|
|
312 for k in range(0,container.urlFunctions.zUrlopenLength(itemspatiallngnodes)):
|
|
|
313 itemspatiallngnode= container.urlFunctions.zUrlopenGetItem(itemspatiallngnodes,k)
|
|
|
314 coord.append(container.urlFunctions.zUrlopenGetTextData(itemspatiallngnode))
|
|
|
315 coordlist.append(coord)
|
|
|
316 gis_id= "_"+gis_id
|
|
|
317 return coordlist
|
|
|
318
|
|
|
319 # End for GoogleMaps creation
|
|
|
320
|
|
|
321 def RESTwrite2File(self,datadir, name,text):
|
|
|
322 logging.debug("RESTwrite2File datadir=%s name=%s"%(datadir,name))
|
|
|
323 try:
|
|
|
324 import cStringIO as StringIO
|
|
|
325 except:
|
|
|
326 import StringIO
|
|
|
327
|
|
|
328 # make filehandle from string
|
|
|
329 textfile = StringIO.StringIO(text)
|
|
|
330 fileid=name
|
|
|
331 if fileid in datadir.objectIds():
|
|
|
332 datadir.manage_delObjects(fileid)
|
|
|
333 fileInZope=datadir.manage_addFile(id=fileid,file=textfile)
|
|
|
334 return "Write successful"
|
|
|
335
|
|
|
336 def manage_editRestDbGisApi(self, title=None, connection_id=None,
|
|
|
337 REQUEST=None):
|
|
|
338 """Change the object"""
|
|
|
339 if title is not None:
|
|
|
340 self.title = title
|
|
|
341
|
|
|
342 if connection_id is not None:
|
|
|
343 self.connection_id = connection_id
|
|
|
344
|
|
|
345 #checkPermission=getSecurityManager().checkPermission
|
|
|
346 REQUEST.RESPONSE.redirect('manage_main')
|
|
|
347
|
|
|
348
|
|
|
349 manage_addRestDbGisApiForm=PageTemplateFile('zpt/addRestDbGisApi',globals())
|
|
|
350
|
|
|
351 def manage_addRestDbGisApi(self, id, title='', label='', description='',
|
|
|
352 createPublic=0,
|
|
|
353 createUserF=0,
|
|
|
354 REQUEST=None):
|
|
|
355 """Add a new object with id *id*."""
|
|
|
356
|
|
|
357 ob=RestDbGisApi(str(id),title)
|
|
|
358 self._setObject(id, ob)
|
|
|
359
|
|
|
360 #checkPermission=getSecurityManager().checkPermission
|
|
|
361 REQUEST.RESPONSE.redirect('manage_main')
|
|
|
362
|
|
|
363
|
