Mercurial > hg > ChinaGisRestApi

comparison RestDbGisApi.py @ 59:a5f2550a5b44

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
more cleanup in kml generation sorting works better more parameters
author casties
date Fri, 22 Oct 2010 19:37:55 +0200
parents 5ed0769f5ad3
children 9fdadb60529f
comparison
equal deleted inserted replaced
58:5ed0769f5ad3 59:a5f2550a5b44
123 if isinstance(f,dict): 123 if isinstance(f,dict):
124 # {name: XX, type: YY} 124 # {name: XX, type: YY}
125 name = sqlName(f['name']) 125 name = sqlName(f['name'])
126 type = f['type'] 126 type = f['type']
127 sqltype = gisToSqlTypeMap[type] 127 sqltype = gisToSqlTypeMap[type]
128
129 else: 128 else:
130 # name only 129 # name only
131 name = sqlName(f) 130 name = sqlName(f)
132 type = 'text' 131 type = 'text'
133 sqltype = 'text' 132 sqltype = 'text'
146 logging.warning("create table not allowed!") 145 logging.warning("create table not allowed!")
147 # throw exception? 146 # throw exception?
148 return None 147 return None
149 148
150 149
151 def getKmlUrl(self,schema='chgis',table='mpdl',id=[],doc=None): 150 def getLiveKmlUrl(self,schema,table,useTimestamp=True,REQUEST=None):
152 logging.debug("getKmlUrl")
153 data = self.getDataForGoogleMap(schema,table,id,doc)
154 kml=self.getKMLname(data=data,table=table)
155 baseUrl = self.absolute_url()
156 return "%s/daten/%s"%(baseUrl,kml)
157
158 def getLiveKmlUrl(self,schema,table,REQUEST=None):
159 if REQUEST is None: 151 if REQUEST is None:
160 REQUEST = self.REQUEST 152 REQUEST = self.REQUEST
161 logging.debug("getLiveKmlUrl") 153 logging.debug("getLiveKmlUrl")
162 baseUrl = self.absolute_url() 154 baseUrl = self.absolute_url()
163 timestamp = time.time() 155 timestamp = time.time()
156 # filter parameters in URL and add to new URL
164 params = [p for p in REQUEST.form.items() if p[0] not in ('format','timestamp')] 157 params = [p for p in REQUEST.form.items() if p[0] not in ('format','timestamp')]
165 params.append(('format','KML')) 158 params.append(('format','KML'))
166 params.append(('timestamp',timestamp)) 159 if useTimestamp:
160 # add timestamp so URL changes every time
161 params.append(('timestamp',timestamp))
167 paramstr = urllib.urlencode(params) 162 paramstr = urllib.urlencode(params)
168 return "%s/db/%s/%s?%s"%(baseUrl,schema,table,paramstr) 163 return "%s/db/%s/%s?%s"%(baseUrl,schema,table,paramstr)
169 164
170 def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=None,doc=None):
171 logging.debug("getDataForGoogleMap")
172 qstr="SELECT * FROM "+schema+"."+table
173 try:
174 if id is not None:
175 qstr=qstr+" WHERE "
176 for id_item in id.split(","):
177 if table=='mpdl':
178 qstr=qstr+" mpdl_xmlsource_id = '"+id_item+ "' OR"
179 else:
180 qstr=qstr+" cast(id as text) LIKE '"+id_item+ "' OR"
181 qstr=str(qstr).rsplit(" ",1)[0] #to remove last " and "
182 data=self.ZSQLSimpleSearch(qstr)
183 return data
184 except:
185 return qstr
186
187 def getKmlData(self, schema, table, ids=None, sortBy=1, gisIdField=None, latField=None, lonField=None): 165 def getKmlData(self, schema, table, ids=None, sortBy=1, gisIdField=None, latField=None, lonField=None):
188 """returns data structure for KML template""" 166 """returns data structure for KML template"""
189 logging.debug("getKMLdata") 167 logging.debug("getKMLdata gid=%s lat=%s lon=%s"%(gisIdField,latField,lonField))
190 # Mapping a set of points from table-based SQL-query: 168 # Mapping a set of points from table-based SQL-query:
191 qstr='SELECT * FROM "%s"."%s"'%(schema,table) 169 qstr='SELECT * FROM "%s"."%s"'%(schema,table)
192 idList = None 170 idList = None
193 if ids is not None: 171 if ids is not None:
194 qstr += ' WHERE ' 172 qstr += ' WHERE '
201 qstr += ','.join(['%s' for i in idList]) 179 qstr += ','.join(['%s' for i in idList])
202 qstr += ')' 180 qstr += ')'
203 181
204 if sortBy: 182 if sortBy:
205 # add sort clause 183 # add sort clause
206 qstr += " ORDER BY %s" 184 if sortBy == 1:
207 if idList is None: 185 qstr += ' ORDER BY 1'
208 idList = [] 186 else:
209 idList.append(sortBy) 187 # TODO: proper quoting for names
210 data = self.executeSQL(qstr,idList) 188 qstr += ' ORDER BY "%s"'%sortBy.replace('"','')
211 else: 189
212 data = self.executeSQL(qstr,idList) 190 data = self.executeSQL(qstr,idList)
213 191
214 fieldMap = self.getFieldNameMap(data['fields']) 192 fieldMap = self.getFieldNameMap(data['fields'])
215 193
216 if (gisIdField is None) and (latField is None or lonField is None): 194 if (gisIdField is None) and (latField is None or lonField is None):
217 # no fields given - choose automagically 195 # no fields given - choose automagically
299 #logging.debug("kmlData=%s"%(repr(kmlData))) 277 #logging.debug("kmlData=%s"%(repr(kmlData)))
300 return kmlData 278 return kmlData
301 279
302 def getPointForChGisId(self, gis_id): 280 def getPointForChGisId(self, gis_id):
303 """returns coordinate pair for given gis_id""" 281 """returns coordinate pair for given gis_id"""
282 def getPoint(id):
283 sql="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id = %s"
284 res = self.executeSQL(sql, (gis_id,))
285 if len(res['rows']) > 0:
286 return res['rows'][0]
287 return None
288
304 if gis_id is None or gis_id == "": 289 if gis_id is None or gis_id == "":
305 return None 290 return None
306 291
307 sql="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id = %s" 292 # try gis_id
308 res = self.executeSQL(sql, (gis_id,)) 293 coords = getPoint(gis_id)
309 if len(res['rows']) > 0: 294 if coords is None:
310 coords = res['rows'][0]
311 return coords
312
313 else:
314 # try to clean gis_id... 295 # try to clean gis_id...
315 gis_id = re.sub(r'[^0-9]','',gis_id) 296 gis_id = re.sub(r'[^0-9]','',gis_id)
316 # try again 297 # try again
317 sql="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id = %s" 298 coords = getPoint(gis_id)
318 res = self.executeSQL(sql, (gis_id,)) 299 if coords is None:
319 if len(res['rows']) > 0:
320 coords = res['rows'][0]
321 return coords
322
323 else:
324 logging.error("CH-GIS ID %s not found!"%repr(gis_id)) 300 logging.error("CH-GIS ID %s not found!"%repr(gis_id))
325 301
326 # TODO: do we need the getCoordsFromREST_gisID stuff? 302 # TODO: do we need the getCoordsFromREST_gisID stuff?
327 303
328 return None 304 return coords
329 305
306
307 ## legacy methods...
308
309 def getKmlUrl(self,schema='chgis',table='mpdl',id=[],doc=None):
310 logging.debug("getKmlUrl")
311 data = self.getDataForGoogleMap(schema,table,id,doc)
312 kml=self.getKMLname(data=data,table=table)
313 baseUrl = self.absolute_url()
314 return "%s/daten/%s"%(baseUrl,kml)
315
316 def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=None,doc=None):
317 logging.debug("getDataForGoogleMap")
318 qstr="SELECT * FROM "+schema+"."+table
319 try:
320 if id is not None:
321 qstr=qstr+" WHERE "
322 for id_item in id.split(","):
323 if table=='mpdl':
324 qstr=qstr+" mpdl_xmlsource_id = '"+id_item+ "' OR"
325 else:
326 qstr=qstr+" cast(id as text) LIKE '"+id_item+ "' OR"
327 qstr=str(qstr).rsplit(" ",1)[0] #to remove last " and "
328 data=self.ZSQLSimpleSearch(qstr)
329 return data
330 except:
331 return qstr
330 332
331 333
332 def getKMLname(self,data=[],table=""): 334 def getKMLname(self,data=[],table=""):
333 logging.debug("getKMLname") 335 logging.debug("getKMLname")
334 #session=context.REQUEST.SESSION 336 #session=context.REQUEST.SESSION