def printShape(db,shape_id):
result = db.select('shapes',shape_id=shape_id)
coordList = [[p['shape_pt_lon'],p['shape_pt_lat']] for p in result]
feature = geojson.geoJsonLineString(shape_id,coordList,{'type':'Line'})
resultGeoJson = geojson.geoJsonFeatCollection([feature])
jsonPrint(resultGeoJson)
return {'success':True}
def getStops(db):
stops = []
features = []
for stopCode in db.select('recorridos',nombre=trip):
stop = db.select('paradas',codigoparada=stopCode['CodigoParada'])[0]
stopD = dict(stop)
stops.append(stopD)
feature = geojson.geoJsonFeature(stopD['CodigoParada'],stopD['lon'],stopD['lat'],stopD)
features.append(feature)
jsonPrint(geojson.geoJsonFeatCollection(features))
def bbox(self, bbox, filterQ):
w,s,e,n = map(float,bbox.split(','))
q = ["""SELECT *
FROM stops s INNER JOIN stop_seq sq ON s.stop_id=sq.stop_id
WHERE
(stop_lat BETWEEN {s} AND {n})
AND
(stop_lon BETWEEN {w} AND {e}) """]
if filterQ:
if 'id:' in filterQ:
stop_id = filterQ.split(':')[1]
q.append("""AND s.stop_id='{f}'""")
q.append("LIMIT 300")
query = ''.join(q).format(s=s,n=n,w=w,e=e, f=stop_id)
elif 'calle:' in filterQ:
stop_calle = filterQ.split(':')[1]
q.append("""AND s.stop_calle LIKE '%{f}%'""")
q.append("LIMIT 300")
query = ''.join(q).format(s=s,n=n,w=w,e=e, f=stop_calle)
else:
q.append("""AND s.stop_calle LIKE '%{f}%'""")
q.append("LIMIT 300")
query = ''.join(q).format(s=s,n=n,w=w,e=e, f=filterQ)
else:
q.append("LIMIT 300")
query = ''.join(q).format(s=s,n=n,w=w,e=e)
self.db.query(query)
d = {}
for r in self.db.cursor.fetchall():
stop = dict(r)
linea = stop.pop('trip_id')
stop_id = stop.pop('stop_id')
if stop_id in d:
d[stop_id]['lineas'].append(linea)
else:
d[stop_id] = stop
d[stop_id]['lineas'] = [linea]
features = []
for stop_id,stop in d.items():
f = geojson.geoJsonFeature(stop_id,
stop['stop_lon'],
stop['stop_lat'],
{'stop_id':stop_id,
'stop_lineas':stop['lineas'],
'stop_calle':stop['stop_calle'],
'stop_numero':stop['stop_numero'],
'stop_esquina':stop['stop_esquina'],
'stop_entre':stop['stop_entre']})
features.append(f)
return geojson.geoJsonFeatCollection(features)
def findStop(self, stop_id):
data = self.db.select('stops',stop_id=stop_id)
if data:
stop = data[0]
l = self.db.select('stop_seq',stop_id=stop_id)
lineas = [t['trip_id'] for t in l]
f = geojson.geoJsonFeature(stop_id,
stop['stop_lon'], stop['stop_lat'],
{'stop_id':stop_id,
'stop_lineas':lineas,
'stop_calle':stop['stop_calle'],
'stop_numero':stop['stop_numero'],
'stop_esquina':stop['stop_esquina'],
'stop_entre':stop['stop_entre']})
response = geojson.geoJsonFeatCollection([f])
else:
response = {'success': False}
return response
def printTripStops(db,trip_id):
features = []
stopCodes = []
q = """SELECT * FROM stop_seq WHERE trip_id="{0}" ORDER BY stop_sequence""".format(trip_id)
db.query(q)
for row in db.cursor.fetchall():
stopCodes.append(row['stop_id'])
for i,stop_id in enumerate(stopCodes):
d = db.select('stops',stop_id=stop_id)[0]
f = geojson.geoJsonFeature(stop_id,
d['stop_lon'],
d['stop_lat'],
{'stop_id':d['stop_id'],
'stop_seq':i+1,
'stop_calle':d['stop_calle'],
'stop_numero':d['stop_numero'],
'stop_esquina':d['stop_esquina'],
'stop_entre':d['stop_entre']})
features.append(f)
resultGeoJson = geojson.geoJsonFeatCollection(features)
jsonPrint(resultGeoJson)
return {'success':True}
def getBBOX(db,bbox):
w,s,e,n = map(float,bbox.split(','))
q = """SELECT * FROM stops
WHERE (stop_lat BETWEEN {s} AND {n})
AND (stop_lon BETWEEN {w} AND {e})
LIMIT 500
""".format(s=s,n=n,w=w,e=e)
db.query(q)
features = []
for stop in db.cursor.fetchall():
f = geojson.geoJsonFeature(stop['stop_id'],
stop['stop_lon'],
stop['stop_lat'],
{'stop_id':stop['stop_id'],
'stop_calle':stop['stop_calle'],
'stop_numero':stop['stop_numero'],
'stop_esquina':stop['stop_esquina'],
'stop_entre':stop['stop_entre']})
features.append(f)
resultGeoJson = geojson.geoJsonFeatCollection(features)
jsonPrint(resultGeoJson)
#~ jsonPrint({'test':bbox, 'w':w,'s':s,'e':e,'n':n,'query':q})
return {'success':True}
def shape(self, shape_id):
result = self.db.select('shapes',shape_id=shape_id)
coordList = [[p['shape_pt_lon'],p['shape_pt_lat']] for p in result]
feature = geojson.geoJsonLineString(shape_id,coordList,{'type':'Line'})
return geojson.geoJsonFeatCollection([feature])
def main():
database = form.getvalue('database')
db = o.dbInterface(database+".sqlite")
if action == 'getDb':
FILENAME = database+".sqlite"
with open(database+".sqlite", "r") as f:
buff = f.read()
print "Content-Type:application/x-download\n",
print "Content-Disposition:attachment;filename={0}\n".format(os.path.split(database+".sqlite")[-1]),
print "Content-Length:{0}\n".format(len(buff)),
print "\n{0}".format(buff)
elif action == 'getShapes':
features = []
for sid in getShapesList(db):
routeShape = [[float(p['lon']),float(p['lat'])] for p in db.select('shapes',sid=sid)]
feature = geojson.geoJsonPolygon(sid,[routeShape])
#~ feature.update({'properties':{'test1':'test2'}})
features.append(feature)
featureCollection = geojson.geoJsonFeatCollection(features)
jsonPrint(featureCollection)
elif action == 'getTags':
sid = form.getvalue('sid')
#~ Dict comprehension incompatible with python version<2.6!!!
#~ out = {k:v for _,k,v in db.select('shape_tags',sid=sid)}
out = {}
if sid <> 'null':
for sid,k,v in db.select('shape_tags',sid=sid):
if (k <> u'' and v <> u''):
out.update({k:v})
jsonPrint(out)
elif action == 'getSavedTag':
codigo = form.getvalue('codigo')
row = db.select('codigos',codigo=codigo)[0]
#~ Dict comprehension incompatible with python version<2.6!!!
#~ out = {k:row[k] for k in row.keys()}
out = {}
for k in row.keys():
if (k and row[k] and k <> 'codigo'):
out.update({k:row[k]})
jsonPrint(out)
elif action == 'getCodes':
db.query('SELECT ALL codigo FROM codigos')
out = [r[0] for r in db.cursor.fetchall()]
jsonPrint(out)
elif action == 'saveTags':
sid = form.getvalue('sid')
tags = json.loads(form.getvalue('tags'))
db.remove('shape_tags',sid=sid)
for k,v in tags.items():
if (k <> u'' and v <> u''):
db.insert('shape_tags',sid=sid,k=k,v=v)
jsonPrint(tags)
elif action == 'removeShape':
sid = form.getvalue('sid')
db.remove('shapes',sid=sid)
db.remove('shape_id',sid=sid)
db.remove('shape_tags',sid=sid)
jsonPrint({'success':True})
elif action == 'saveShapes':
jData = json.loads(form.getvalue('features'))
features = {}
for feature in jData['features']:
if feature['geometry']['type'] == 'Polygon':
if 'id' in feature:
features.update({feature['id']:feature['geometry']['coordinates'][0]})
else:
db.query("""INSERT INTO shape_id DEFAULT VALUES """)
newid = db.cursor.lastrowid
features.update({str(newid):feature['geometry']['coordinates'][0]})
for sid,coordList in features.items():
db.remove('shapes',sid=sid)
#~ jsonPrint(coordList)
for i,pt in enumerate(coordList):
db.insert('shapes',sid=sid,
lat=pt[1],
lon=pt[0],
seq=i+1)
jsonPrint({'success':True})
db.close()
def getShape(db,shape_id):
result = db.select('shapes',id=shape_id)
coordList = [[p['lon'],p['lat']] for p in result]
feature = geojson.geoJsonLineString(shape_id,coordList)
resultGeoJson = geojson.geoJsonFeatCollection([feature])
return resultGeoJson
