def tripStops(self, trip_id): features = [] stopCodes = [] q = """SELECT stop_id,is_timepoint FROM stop_seq WHERE trip_id='{0}' ORDER BY stop_sequence""".format(trip_id) self.db.query(q) for i,row in enumerate(self.db.cursor.fetchall()): stopCodes.append([i,row['stop_id'],row['is_timepoint']]) for i,stop_id,is_timepoint in stopCodes: try: d = self.db.select('stops',stop_id=stop_id)[0] except Exception, e: print("unable to find stop: " + stop_id) # raise e continue l = self.db.select('stop_seq',stop_id=stop_id) lineas = [t['trip_id'] for t in l] f = geojson.geoJsonFeature(stop_id, d['stop_lon'], d['stop_lat'], {'stop_id':d['stop_id'], 'stop_seq':i+1, 'is_timepoint':bool(is_timepoint), 'stop_lineas':lineas, 'stop_calle':d['stop_calle'], 'stop_numero':d['stop_numero'], 'stop_esquina':d['stop_esquina'], 'stop_entre':d['stop_entre']}) features.append(f)
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}