Mercurial > hg > ChinaGisRestApi

comparison RestDbGisApi.py @ 43:562717546168

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
refactoring... RestDbGisApi and RestDbJsonStore inherit from RestDbInterface
author casties
date Thu, 02 Sep 2010 13:17:23 +0200
parents
children c6c47034d2a4
comparison
equal deleted inserted replaced
42:291aed5f0e0d 43:562717546168
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('&','&amp;')
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