def calculateASTAR(): dbtables.ROUTE_STEPS.createTable() kursor = dbconnect.Verbinding().kursor astarsql = "SELECT id, source, target, cost, x1, y1, x2, y2 FROM routing" sql = "SELECT id, startnode, endnode FROM routes" kursor.execute(sql) routes = kursor.fetchall() monitor = outputs.VoortgangRegel(len(routes), outputs.VoortgangRegel.MODUS_NUM, 'Routes verwerkt') errors = 0 for route in routes: sql = "SELECT seq, id1, id2, cost FROM pgr_astar('%s', %d, %d, false, false)" % ( astarsql, route[1], route[2]) try: kursor.execute(sql) steps = kursor.fetchall() for step in steps: sql = "INSERT INTO route_steps (route_id, seq, node, edge, cost) VALUES (%d, %d, %d, %d, %f)" % ( route[0], step[0], step[1], step[2], step[3]) kursor.execute(sql) kursor.connection.commit() except psycopg2.InternalError: errors += 1 kursor.close() kursor = dbconnect.Verbinding().kursor monitor.plusEen() print 'Errors: %d' % errors kursor.connection.commit() kursor.close()
def completeUserTable(): dbtables.USERS.createTable() print 'Selecting dates...' kursor = dbconnect.Verbinding().kursor sql = "SELECT tweet_name FROM tweets_amsterdam GROUP BY tweet_name, datum" kursor.execute(sql) datumnamen = kursor.fetchall() print 'Selecting tweets...' sql = "SELECT tweet_name FROM tweets_amsterdam" kursor.execute(sql) tweetnamen = kursor.fetchall() print 'Selecting users...' sql = "SELECT DISTINCT tweet_name FROM tweets_amsterdam" kursor.execute(sql) namen = kursor.fetchall() voortgang = VoortgangRegel(len(namen), VoortgangRegel.MODUS_NUM, 'Gebruikers verwerkt') for naam in namen: days = datumnamen.count(naam) tweets = tweetnamen.count(naam) sql = "INSERT INTO users (name, days, tweets) VALUES ('%s', %d, %d)" % ( naam[0], days, tweets) kursor.execute(sql) voortgang.plusEen() kursor.connection.commit() kursor.close()
def ContinueFetching(): kursor = dbconnect.Verbinding().kursor sql = "SELECT name FROM users WHERE processed=false AND useless=false ORDER BY name" kursor.execute(sql) namen = kursor.fetchall() kursor.close() FetchUsefulTweets(namen)
def createTrajectories(): dbtables.TRAJECTORIES.createTable() dbtables.REL_TRAJ_TWEETS.createTable() kursor = dbconnect.Verbinding().kursor sql = "SELECT DISTINCT tweet_name FROM scraped_tweets" kursor.execute(sql) namen = kursor.fetchall() monitor = outputs.TrajectoryMonitor(len(namen)).nieuweMonitor() for naam in namen: sql = "SELECT id, tijddatum FROM scraped_tweets WHERE tweet_name='%s' ORDER BY tijddatum ASC" % naam[ 0] kursor.execute(sql) tweets = kursor.fetchall() trajectory = [tweets[0]] for tweet in tweets[1:]: oudetijd = trajectory[-1][1] nieuwetijd = tweet[1] thresholdtijd = oudetijd + datetime.timedelta(hours=THRESHOLD) if nieuwetijd > thresholdtijd: if len(trajectory) > 1: writeTrajectory(trajectory, naam[0]) monitor.updateRegel(naam, trajectory[0][1], trajectory[-1][1]) trajectory = [tweet] else: trajectory.append(tweet) if len(trajectory) > 1: writeTrajectory(trajectory, naam[0]) monitor.updateRegel(naam, trajectory[0][1], trajectory[-1][1]) kursor.close()
def createStopTable(): dbtables.STOPS.createTable() kursor = dbconnect.Verbinding().kursor sql = "SELECT id, geom FROM haltes" kursor.execute(sql) haltes = kursor.fetchall() monitor = outputs.VoortgangRegel(len(haltes), outputs.VoortgangRegel.MODUS_NUM, 'Haltes verwerkt') for halte in haltes: sql = "SELECT string_agg FROM src_tracksatstop WHERE pointref=%d" % halte[ 0] kursor.execute(sql) lijnen = kursor.fetchone()[0].split(';') for lijn in lijnen: sql = "INSERT INTO stops (pointref, geom, lijnnaam) VALUES (%d, '%s', '%s')" % ( halte[0], halte[1], lijn) kursor.execute(sql) monitor.plusEen() sql = "DELETE FROM stops USING lijnen WHERE stops.lijnnaam=lijnen.lijnnaam AND lijnen.type='TRAIN'" kursor.execute(sql) sql = "SELECT station, lon, lat FROM treinverbindingen INNER JOIN stations_ns ON station=naam" kursor.execute(sql) stations = kursor.fetchall() for station, lon, lat in stations: sql = "INSERT INTO stops (pointref, geom, lijnnaam) SELECT max(pointref)+1, ST_SetSRID(ST_MakePoint(%f, %f), 4326), 'trein' " \ "FROM stops RETURNING pointref" % (lon, lat) kursor.execute(sql) pointref = kursor.fetchone()[0] sql = "UPDATE treinverbindingen SET pointref=%d " \ "WHERE station='%s'" % (pointref, station) kursor.execute(sql) kursor.connection.commit() kursor.close()
def createVertexTable(): dbtables.VERTICES.createTable() kursor = dbconnect.Verbinding().kursor sql = "SELECT source, max(x1), max(y1) FROM routing GROUP BY source" kursor.execute(sql) sources = kursor.fetchall() monitor = outputs.VoortgangRegel(len(sources), outputs.VoortgangRegel.MODUS_NUM, 'Sources verwerkt') for source in sources: sql = "INSERT INTO vertices (node_id, lon, lat, geom) VALUES (%d, %f, %f, ST_SetSRID(ST_MakePoint(%f, %f), 4326))" % ( source[0], source[1], source[2], source[1], source[2]) kursor.execute(sql) monitor.plusEen() kursor.connection.commit() sql = "SELECT target, max(x2), max(y2) FROM routing LEFT JOIN vertices ON routing.target=vertices.node_id WHERE vertices.node_id IS NULL GROUP BY target" kursor.execute(sql) targets = kursor.fetchall() monitor = outputs.VoortgangRegel(len(targets), outputs.VoortgangRegel.MODUS_NUM, 'Targets verwerkt') for target in targets: sql = "INSERT INTO vertices (node_id, lon, lat, geom) VALUES (%d, %f, %f, ST_SetSRID(ST_MakePoint(%f, %f), 4326))" % ( target[0], target[1], target[2], target[1], target[2]) kursor.execute(sql) monitor.plusEen() kursor.connection.commit() kursor.close()
def createRouteTable(): dbtables.ROUTES.createTable() kursor = dbconnect.Verbinding().kursor sql = "SELECT id, traj_id FROM rel_traj_tweets WHERE node IS NOT NULL" kursor.execute(sql) trajs = kursor.fetchall() monitor = outputs.VoortgangRegel(len(trajs), outputs.VoortgangRegel.MODUS_NUM, 'Trajectories verwerkt') errors = 0 for traj in trajs: sql = "SELECT traj.node FROM (SELECT node, tweet_id FROM rel_traj_tweets WHERE traj_id=%d) AS traj ORDER BY traj.tweet_id DESC" % ( traj[1]) kursor.execute(sql) tweets = kursor.fetchall() for i in range(len(tweets[:-1])): try: if tweets[i] != tweets[i + 1]: sql = "INSERT INTO routes (rel_id, traj_id, route_num, startnode, endnode) VALUES (%d, %d, %d, %d, %d)" % ( traj[0], traj[1], i, tweets[i][0], tweets[i + 1][0]) kursor.execute(sql) except TypeError: errors += 1 monitor.plusEen() print 'Errors: %d' % errors kursor.connection.commit() kursor.close()
def createPlatforms(): con = dbconnect.Verbinding() sql = "DELETE FROM ptrouting WHERE edge_type='platform'; SELECT stops.id, pointref, stops.geom FROM stops INNER JOIN ptvertices ON pointref=station_id" stops = con.selectAll(sql) monitor = outputs.VoortgangRegel(len(stops), outputs.VoortgangRegel.MODUS_NUM, 'Stops verwerkt') fouten = 0 for id, pointref, geom in stops: sql = "SELECT node_id, geom, lon, lat FROM ptvertices WHERE node_type='station' AND station_id=%d" % pointref res = con.selectAll(sql) if len(res) > 0: stationId, stationGeom, lon, lat = res[0] sql = "INSERT INTO ptvertices (node_id, lon, lat, geom, node_type, station_id, stop_id) " \ "SELECT max(node_id)+1, ST_X('%s'), ST_Y('%s'), '%s', 'stop', %d, %d FROM ptvertices " \ "RETURNING node_id" % (geom, geom, geom, pointref, id) node = con.selectOne(sql)[0] sql = "INSERT INTO ptrouting (source, target, km, kmh, x1, y1, x2, y2, geom_way, edge_type) " \ "VALUES (%d, %d, ST_Length_Spheroid(ST_MakeLine('%s', '%s'), 'SPHEROID[\"WGS 84\",6378137,298.257223563]')/1000" \ ", 5, %f, %f, ST_X('%s'), ST_Y('%s'), ST_MakeLine('%s', '%s'), 'platform')" \ % (stationId, node, stationGeom, geom, lon, lat, geom, geom, stationGeom, geom) con.exe(sql) else: fouten += 1 con.exe(sql) monitor.plusEen() con.commit() con.sluit()
def createLijnTabel(): dbtables.LIJNEN.createTable() con = dbconnect.Verbinding() sql = "INSERT INTO lijnen (lijnnaam, type) SELECT lijnnaam, max(transportm) " \ "FROM stops LEFT JOIN ptnet ON lijnnaam=publiccode GROUP BY lijnnaam ;" \ "UPDATE lijnen SET type='BUS' WHERE type IS NULL;" \ "INSERT INTO lijnen (lijnnaam, type) VALUES ('trein', 'TRAIN')" con.exe(sql) con.commit() con.sluit()
def calculateFrequency(): con = dbconnect.Verbinding() print "\nFrequentietabel maken..." sql = "UPDATE ptrouting SET used=f.c FROM " \ "(SELECT ptrouting.id i, count(route_steps.id) c FROM ptrouting " \ "LEFT JOIN route_steps ON ptrouting.id=route_steps.edge " \ "GROUP BY ptrouting.id) AS f " \ "WHERE id=f.i" con.exe(sql) con.commit() con.sluit()
def writeTrajectory(trajectory, naam): kursor = dbconnect.Verbinding().kursor sql = "INSERT INTO trajectories (name, starttime, endtime) VALUES ('%s', '%s', '%s') RETURNING id" % ( naam, trajectory[0][1], trajectory[-1][1]) kursor.execute(sql) id = kursor.fetchone()[0] for t in trajectory: sql = "INSERT INTO rel_traj_tweets (traj_id, tweet_id) VALUES (%d, %d)" % ( id, t[0]) kursor.execute(sql) kursor.connection.commit() kursor.close()
def StartFetching(): kursor = dbconnect.Verbinding().kursor sql = "UPDATE users SET useless=false, processed=false" kursor.execute(sql) sql = "UPDATE users SET useless=true WHERE days>15 OR tweets<2" kursor.execute(sql) kursor.connection.commit() sql = "SELECT name FROM users WHERE useless=false ORDER BY name" kursor.execute(sql) namen = kursor.fetchall() kursor.close() FetchUsefulTweets(namen)
def FetchUsefulTweets(namen): kursor = dbconnect.Verbinding().kursor sql = "SELECT min(tweet_id), max(tweet_id) FROM tweets_amsterdam" kursor.execute(sql) (firstId, lastId) = kursor.fetchone() scraper = twitscraper.TimelineScraper(int(firstId), int(lastId)) grinder = TweetGrinder() for naam in namen: scraper.pushOpdracht(naam[0], grinder.verwerkTweets) kursor.close() scraper.scrape() grinder.klaar()
def createEdgeNetwork(): print '\nNetwerk maken...' dbtables.OTPNETWORK.createTable() con = dbconnect.Verbinding() sql = "WITH n AS (" \ "INSERT INTO otpnetwork (geom, mode, used) SELECT geom, mode, count(id) FROM otpedges " \ "GROUP BY geom, mode " \ "RETURNING geom g, mode m, id i) " \ "UPDATE otpedges SET network_id=n.i FROM n " \ "WHERE geom=n.g AND mode=n.m" con.exe(sql) con.commit() con.sluit()
def copyRoutingTable(): con = dbconnect.Verbinding() sql = "DROP TABLE IF EXISTS ptrouting; SELECT * INTO ptrouting FROM routing; ALTER TABLE ptrouting OWNER TO postgres; " \ "ALTER TABLE ptrouting ADD CONSTRAINT ptrouting_pkey PRIMARY KEY (id); " \ "ALTER TABLE ptrouting ADD COLUMN edge_type character varying(20);" \ "ALTER TABLE ptrouting DROP COLUMN osm_id, DROP COLUMN osm_meta, DROP COLUMN osm_source_id, " \ "DROP COLUMN osm_target_id, DROP COLUMN clazz, DROP COLUMN flags; " \ "ALTER TABLE ptrouting DROP COLUMN id, ADD COLUMN id serial NOT NULL;" \ "UPDATE ptrouting SET edge_type='street', kmh=5 " con.exe(sql) con.commit() dbtables.PTVERTICES.createTable() kursor = dbconnect.Verbinding().kursor sql = "SELECT source, max(x1), max(y1) FROM ptrouting GROUP BY source" kursor.execute(sql) sources = kursor.fetchall() monitor = outputs.VoortgangRegel(len(sources), outputs.VoortgangRegel.MODUS_NUM, 'Sources verwerkt') for source in sources: sql = "INSERT INTO ptvertices (node_id, lon, lat, geom) VALUES (%d, %f, %f, ST_SetSRID(ST_MakePoint(%f, %f), 4326))" % ( source[0], source[1], source[2], source[1], source[2]) kursor.execute(sql) monitor.plusEen() kursor.connection.commit() sql = "SELECT target, max(x2), max(y2) FROM ptrouting LEFT JOIN ptvertices ON ptrouting.target=ptvertices.node_id WHERE ptvertices.node_id IS NULL GROUP BY target" kursor.execute(sql) targets = kursor.fetchall() monitor = outputs.VoortgangRegel(len(targets), outputs.VoortgangRegel.MODUS_NUM, 'Targets verwerkt') for target in targets: sql = "INSERT INTO ptvertices (node_id, lon, lat, geom) VALUES (%d, %f, %f, ST_SetSRID(ST_MakePoint(%f, %f), 4326))" % ( target[0], target[1], target[2], target[1], target[2]) kursor.execute(sql) monitor.plusEen() kursor.connection.commit() kursor.close()
def createPtgeometry(): con = dbconnect.Verbinding() sql = "ALTER TABLE ptrouting DROP COLUMN IF EXISTS geom_p, " \ "ADD COLUMN geom_p geometry; " \ "UPDATE ptrouting SET geom_p=geom_way; " \ "SELECT ptrouting.id, src.geom, tgt.geom, src.lijnnaam, edge_type FROM ptrouting " \ "INNER JOIN ptvertices AS srcv ON source=srcv.node_id " \ "INNER JOIN ptvertices AS tgtv ON target=tgtv.node_id " \ "INNER JOIN stops AS src ON srcv.stop_id=src.id " \ "INNER JOIN stops AS tgt ON tgtv.stop_id=tgt.id " \ "WHERE edge_type IN ('BOAT', 'BUS', 'TRAM', 'METRO', 'TRAIN')" routes = con.selectAll(sql) monitor = outputs.VoortgangRegel(len(routes), outputs.VoortgangRegel.MODUS_NUM, 'OV verwerkt') errors = 0 for id, src, tgt, lijnnaam, type in routes: if type == 'TRAIN': sql = "SELECT g, ST_Line_Locate_Point(g, '%s'), ST_Line_Locate_Point(g, '%s') FROM " \ "(SELECT ST_LineMerge(geom) g FROM ptnet_nl WHERE transportm='TRAIN') AS l " \ "ORDER BY (ST_Distance(g, '%s')+ST_Distance(g, '%s')) ASC LIMIT 1" \ % (src, tgt, src, tgt) else: sql = "SELECT g, ST_Line_Locate_Point(g, '%s'), ST_Line_Locate_Point(g, '%s') FROM " \ "(SELECT ST_LineMerge(geom) g FROM ptnet_nl WHERE publiccode='%s') AS l " \ "ORDER BY (ST_Distance(g, '%s')+ST_Distance(g, '%s')) ASC LIMIT 1" \ % (src, tgt, lijnnaam, src, tgt) netroutes = con.selectOne(sql) if netroutes is None: if type != 'TRAIN': sql = "SELECT g, ST_Line_Locate_Point(g, '%s'), ST_Line_Locate_Point(g, '%s') FROM " \ "(SELECT ST_LineMerge(geom) g FROM ptnet WHERE ST_DWithin('%s', geom, 0.01) OR ST_DWithin('%s', geom, 0.01)) AS l " \ "ORDER BY (ST_Distance(g, '%s')+ST_Distance(g, '%s')) ASC LIMIT 1" \ % (src, tgt, src, tgt, src, tgt) netroutes = con.selectOne(sql) errors += 1 route, srcfrac, tgtfrac = netroutes if srcfrac < tgtfrac: sql = "UPDATE ptrouting SET geom_p=ST_Line_Substring('%s', %f, %f) WHERE id=%d" % ( route, srcfrac, tgtfrac, id) elif tgtfrac < srcfrac: sql = "UPDATE ptrouting SET geom_p=ST_Reverse(ST_Line_Substring('%s', %f, %f)) WHERE id=%d" % ( route, tgtfrac, srcfrac, id) else: sql = "UPDATE ptrouting SET geom_p=geom_way WHERE id=%d" % (id) con.exe(sql) monitor.plusEen() con.commit() con.sluit() print 'errors: ', errors
def makeFrequencyColumn(): kursor = dbconnect.Verbinding().kursor sql = "SELECT count(edge), edge FROM route_steps WHERE edge > 0 GROUP BY edge" kursor.execute(sql) edges = kursor.fetchall() monitor = outputs.VoortgangRegel(len(edges), outputs.VoortgangRegel.MODUS_NUM, 'Edges verwerkt') for edge in edges: sql = "UPDATE routing SET used=%d WHERE id=%d" % edge kursor.execute(sql) monitor.plusEen() kursor.connection.commit() kursor.close()
def nearestNodes(): kursor = dbconnect.Verbinding().kursor sql = "SELECT rel_traj_tweets.id, the_geom FROM scraped_tweets INNER JOIN rel_traj_tweets ON scraped_tweets.id=rel_traj_tweets.tweet_id ORDER BY traj_id ASC" kursor.execute(sql) tweets = kursor.fetchall() monitor = outputs.VoortgangRegel(len(tweets), outputs.VoortgangRegel.MODUS_NUM, 'Tweets verwerkt') for tweet in tweets: sql = "UPDATE rel_traj_tweets SET node=nodes.source FROM " \ "(SELECT subset.source FROM (SELECT source, geom_way FROM ptrouting WHERE ST_DWithin('%s', geom_way, 0.01)) AS subset " \ "ORDER BY ST_Distance(subset.geom_way, '%s') ASC LIMIT 1) AS nodes " \ "WHERE rel_traj_tweets.id=%d" % (tweet[1], tweet[1], tweet[0]) kursor.execute(sql) monitor.plusEen() kursor.connection.commit() kursor.close()
def setCost(): con = dbconnect.Verbinding() #print 'Straatkosten berekenen...' #sql = "UPDATE ptrouting SET cost=(km/5), reverse_cost=(km/5) WHERE edge_type='street'" #con.exe(sql) print 'OV-kosten berekenen...' sql = "UPDATE ptrouting SET cost=(km/10), reverse_cost=1000000 WHERE edge_type='BOAT'; " \ "UPDATE ptrouting SET cost=(km/30), reverse_cost=1000000 WHERE edge_type='BUS'; " \ "UPDATE ptrouting SET cost=(km/30), reverse_cost=1000000 WHERE edge_type='TRAM'; " \ "UPDATE ptrouting SET cost=(km/60), reverse_cost=1000000 WHERE edge_type='METRO'; " \ "UPDATE ptrouting SET cost=(km/80), reverse_cost=1000000 WHERE edge_type='TRAIN' " con.exe(sql) print 'Platformkosten berekenen...' sql = "UPDATE ptrouting SET cost=(0.0833333), reverse_cost=(km/5) WHERE edge_type='platform' " con.exe(sql) con.commit() con.sluit()
def createStations(): dbtables.STATIONS.createTable() con = dbconnect.Verbinding() sql = "SELECT pointref, max(geom) FROM stops GROUP BY pointref" haltes = con.selectAll(sql) monitor = outputs.VoortgangRegel(len(haltes), outputs.VoortgangRegel.MODUS_NUM, 'Haltes verwerkt') for pointref, geom in haltes: sql = "SELECT ways.id, ways.osm_name, ways.source, ways.target, ways.x1, ways.y1, ways.x2, ways.y2, ways.geom_way, " \ "ST_line_locate_point(ways.geom_way, '%s'), " \ "ST_line_interpolate_point(ways.geom_way, ST_line_locate_point(ways.geom_way, '%s')), " \ "ST_Line_Substring(ways.geom_way, 0, ST_line_locate_point(ways.geom_way, '%s'))," \ "ST_Line_Substring(ways.geom_way, ST_line_locate_point(ways.geom_way, '%s'), 1) FROM " \ "(SELECT subset.id, subset.osm_name, subset.source, subset.target, subset.km, subset.x1, subset.y1, subset.x2, subset.y2, subset.geom_way " \ "FROM (SELECT id, osm_name, source, target, km, x1, y1, x2, y2, geom_way FROM ptrouting WHERE ST_DWithin('%s', geom_way, 0.005)) AS subset " \ "ORDER BY ST_Distance(subset.geom_way, '%s') ASC LIMIT 1) AS ways" % (geom, geom, geom, geom, geom, geom) id, name, source, target, x1, y1, x2, y2, waygeom, frac, stationgeom, waysub1, waysub2 = con.selectOne( sql) sql = "INSERT INTO stations (pointref, geom) VALUES (%d, '%s'); " \ "INSERT INTO ptvertices (node_id, lon, lat, geom, node_type, station_id) SELECT max(node_id)+1, ST_X('%s'), " \ "ST_Y('%s'), '%s', 'station', %d FROM ptvertices RETURNING node_id; " % (pointref, stationgeom, stationgeom, stationgeom, stationgeom, pointref) node = con.selectOne(sql)[0] if frac == 0 or frac == 1: vertex = source if frac == 1: vertex = target sql = "UPDATE ptvertices SET node_type='station', station_id=%d WHERE node_id=%d" % ( node, vertex) con.exe(sql) else: sql = "INSERT INTO ptrouting ( source, target, km, kmh, x1, y1, x2, y2, geom_way, edge_type) " \ "VALUES ( %d, %d, ST_Length_Spheroid('%s', 'SPHEROID[\"WGS 84\",6378137,298.257223563]')/1000, 5, %f, %f, ST_X('%s'), ST_Y('%s'), '%s', 'street'); " \ "INSERT INTO ptrouting ( source, target, km, kmh, x1, y1, x2, y2, geom_way, edge_type) " \ "VALUES ( %d, %d, ST_Length_Spheroid('%s', 'SPHEROID[\"WGS 84\",6378137,298.257223563]')/1000, 5, ST_X('%s'), ST_Y('%s'), %f, %f, '%s', 'street'); " \ "DELETE FROM ptrouting WHERE id=%d" \ % ( source, node, waysub1, x1, y1, stationgeom, stationgeom, waysub1, node, target, waysub2, stationgeom, stationgeom, x2, y2, waysub2, id) con.exe(sql) monitor.plusEen() sql = "UPDATE ptrouting SET cost=(km/5), reverse_cost=(km/5) WHERE cost IS NULL" con.exe(sql) con.commit() con.sluit()
def calcLegNums(): con = dbconnect.Verbinding() sql = "select id, route_id from otplegs" legs = con.selectAll(sql) vorigeRoute = 0 monitor = outputs.VoortgangRegel(len(legs), outputs.VoortgangRegel.MODUS_NUM, 'Legs verwerkt') for id, route in legs: if route == vorigeRoute: legnum += 1 else: legnum = 0 vorigeRoute = route sql = "UPDATE otplegs SET legnum=%d WHERE id=%d" % (legnum, id) con.exe(sql) monitor.plusEen() con.commit() con.sluit()
def filterTweets(): kursor = dbconnect.Verbinding().kursor dbtables.TWEETS_AMSTERDAM.createTable() sql = "SELECT tweet_id, tweet_name, tweet_datetime, the_geom, latitude, longitude, date(tweet_datetime) " \ "FROM tweetdata WHERE (latitude >= %f AND latitude <= %f AND longitude >= %f AND longitude <= %f)" % (ONDER, BOVEN, LINKS, RECHTS) print "Selecting..." kursor.execute(sql) totaal = kursor.rowcount rijen = kursor.fetchall() voortgang = outputs.VoortgangRegel(totaal, outputs.VoortgangRegel.MODUS_PROCENT, 'Voortgang') for rij in rijen: tw = twitobjects.createFromFetch(rij) sql = "INSERT INTO tweets_amsterdam (tweet_id, tweet_name, tijddatum, the_geom, lat, lon, datum) " \ "VALUES ('%s', '%s', '%s', '%s', %f, %f, '%s')" % tw.getData() kursor.execute(sql) voortgang.plusEen() kursor.connection.commit() kursor.connection.close()
def createEdges(): dbtables.OTPEDGES.createTable() con = dbconnect.Verbinding() sql = "SELECT id, mode FROM otplegs" ids = con.selectAll(sql) monitor = outputs.VoortgangRegel(len(ids), outputs.VoortgangRegel.MODUS_NUM, 'Legs verwerkt') for id, mode in ids: sql = "SELECT ST_DumpPoints(points) FROM otplegs WHERE id=%d" % (id) points = con.selectAll(sql) valuestr = "" for i in range(len(points) - 1): valuestr = valuestr + "(%d, %d, ST_MakeLine('%s', '%s'), '%s'), " % ( id, i, points[i][0].split(',')[1][:-1], points[i + 1][0].split(',')[1][:-1], mode) sql = "INSERT INTO otpedges (leg_id, edgenum, geom, mode) VALUES %s" % valuestr[: -2] con.exe(sql) monitor.plusEen() con.commit() con.sluit()
def createTracks(): con = dbconnect.Verbinding() sql = "DELETE FROM ptrouting WHERE edge_type<>'street' AND edge_type<>'platform'" con.exe(sql) sql = "SELECT srctabel.node_id, tgttabel.node_id, distance, srctabel.geom, tgttabel.geom, lijnen.lijnnaam FROM src_tracks " \ "INNER JOIN ptvertices AS srctabel ON pointref=srctabel.station_id " \ "INNER JOIN ptvertices AS tgttabel ON pointref_1=tgttabel.station_id " \ "INNER JOIN stops AS srcstop ON srctabel.stop_id=srcstop.id " \ "INNER JOIN stops AS tgtstop ON tgttabel.stop_id=tgtstop.id " \ "INNER JOIN lijnen ON srcstop.lijnnaam=lijnen.lijnnaam " \ "WHERE srcstop.lijnnaam=tgtstop.lijnnaam " tracks = con.selectAll(sql) monitor = outputs.VoortgangRegel(len(tracks), outputs.VoortgangRegel.MODUS_NUM, 'Tracks verwerkt') for source, target, distance, srcgeom, tgtgeom, lijn in tracks: sql = "INSERT INTO ptrouting (source, target, km, x1, y1, x2, y2, geom_way, edge_type) " \ "SELECT %d, %d, %f/1000, ST_X('%s'), ST_Y('%s'), ST_X('%s'), ST_Y('%s')," \ "ST_MakeLine('%s', '%s'), type FROM lijnen WHERE lijnnaam='%s'" \ % (source, target, distance, srcgeom, srcgeom, tgtgeom, tgtgeom, srcgeom, tgtgeom, lijn) con.exe(sql) monitor.plusEen() print '' print 'Treinverbindingen leggen...' sql = "SELECT station, doelstations, ptvertices.node_id, stops.geom FROM treinverbindingen " \ "INNER JOIN stops ON treinverbindingen.pointref=stops.pointref " \ "INNER JOIN ptvertices ON stops.id=ptvertices.stop_id" stations = con.selectAll(sql) for station, doelen, source, geom in stations: for doel in doelen.split(';'): sql = "INSERT INTO ptrouting (source, target, km, x1, y1, x2, y2, geom_way, edge_type) " \ "SELECT %d, ptvertices.node_id, ST_Length_Spheroid(ST_MakeLine('%s', stops.geom), 'SPHEROID[\"WGS 84\",6378137,298.257223563]')/1000, " \ "ST_X('%s'), ST_Y('%s'), ST_X(stops.geom), ST_Y(stops.geom), ST_MakeLine('%s', stops.geom), 'TRAIN' " \ "FROM treinverbindingen INNER JOIN stops ON treinverbindingen.pointref=stops.pointref " \ "INNER JOIN ptvertices ON stops.id=ptvertices.stop_id " \ "WHERE station='%s'" % (source, geom, geom, geom, geom, doel) con.exe(sql) con.commit() con.sluit()
def batchOTP(): dbtables.OTPFAST.createTable() dbtables.OTPPREF.createTable() dbtables.OTPLEGS.createTable() con = dbconnect.Verbinding() sql = "UPDATE routes SET useful=null, isfast=null where useful is not null" con.exe(sql) sql = "SELECT routes.id, srct.lon, srct.lat, srct.tijddatum, tgtt.lon, tgtt.lat, realtime FROM routes " \ "INNER JOIN rel_traj_tweets AS src ON routes.startrel=src.id " \ "INNER JOIN rel_traj_tweets AS tgt ON routes.endrel=tgt.id " \ "INNER JOIN scraped_tweets AS srct ON src.tweet_id=srct.id " \ "INNER JOIN scraped_tweets AS tgtt ON tgt.tweet_id=tgtt.id " routes = con.selectAll(sql) monitor = outputs.VoortgangRegel(len(routes), outputs.VoortgangRegel.MODUS_NUM, 'Routes verwerkt') errors = 0 for (id, startlon, startlat, startdate, endlon, endlat, realtime) in routes: monitor.plusEen() newDate = date(MONDAY.year, MONDAY.month, MONDAY.day + startdate.weekday()) #Snelst mogelijke tijd opvragen params = { 'time': '%s' % startdate.time(), 'fromPlace': '%s,%s' % (startlat, startlon), 'toPlace': '%s,%s' % (endlat, endlon), 'mode': 'WALK,TRANSIT', 'date': newDate, 'walkSpeed': 1.4, 'maxWalkDistance': 1000 } url = URL + urllib.urlencode(params) req = urllib2.Request(url) req.add_header('Accept', 'application/json') try: response = urllib2.urlopen(req) except urllib2.HTTPError as e: errors += 1 print e continue try: content = response.read() objs = json.loads(content) plan = objs['plan'] except Exception, e: errors += 1 continue trip = plan['itineraries'][0] fduration = (trip['endTime'] - plan['date']) / 1000 fslow = realtime < 0.9 * (fduration / 3600) fastlegs = trip['legs'] sql = "INSERT INTO otpfast (route_id, duration, tooslow) VALUES (%d, %d, %s);" \ "UPDATE routes SET useful=%s WHERE id=%d" % (id, fduration, fslow, not(fslow), id) con.exe(sql) #Pref. route opvragen params = { 'time': '%s' % startdate.time(), 'fromPlace': '%s,%s' % (startlat, startlon), 'toPlace': '%s,%s' % (endlat, endlon), 'mode': 'WALK,TRANSIT', 'date': newDate, 'maxWalkDistance': 2000 } url = URL + urllib.urlencode(params) req = urllib2.Request(url) req.add_header('Accept', 'application/json') try: response = urllib2.urlopen(req) except urllib2.HTTPError as e: errors += 1 print e continue try: content = response.read() objs = json.loads(content) plan = objs['plan'] except Exception, e: errors += 1 continue
def __init__(self): dbtables.SCRAPED_TWEETS.createTable() self.kursor = dbconnect.Verbinding().kursor