Mercurial > hg > ChinaGisRestApi
comparison RestDbGisApi.py @ 43:562717546168
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('&','&') | |
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 |