def calcDependentVars(): print 'Toeristuren/meter uitrekenen...' con = dbconnect.Verbinding() sql = "UPDATE otpnetwork AS n SET " \ "thm_r=nn.touristtime_r/ST_Length(ST_Transform(nn.geom, 28992))/3600, " \ "thm_s=nn.touristtime_s/ST_Length(ST_Transform(nn.geom, 28992))/3600 " \ "FROM otpnetwork AS nn " \ "WHERE n.id=nn.id AND ST_Length(ST_Transform(n.geom, 28992))>0; " \ "UPDATE otpnetwork AS n SET " \ "thm=nn.thm_r+nn.thm_s " \ "FROM otpnetwork AS nn " \ "WHERE n.id=nn.id" con.exe(sql) con.commit() print 'Gemiddelde verblijftijd stationair uitrekenen...' sql = "UPDATE otpnetwork AS n SET " \ "avgth_s=nn.touristtime_s/nn.used_s " \ "FROM otpnetwork AS nn " \ "WHERE n.id=nn.id AND n.used_s>0" con.exe(sql) con.commit() print 'Gemiddelde verblijftijden uitrekenen...' sql = "UPDATE otpnetwork AS n SET " \ "avgth_r=nn.touristtime_r/nn.used_r, " \ "avgth=nn.touristtime/nn.used " \ "FROM otpnetwork AS nn " \ "WHERE n.id=nn.id" con.exe(sql) con.commit() con.sluit()
def calcTouristTime(): #route times print 'Routetijden optellen...' con = dbconnect.Verbinding() sql = "UPDATE otpnetwork SET touristtime_r=n.t FROM " \ "(SELECT network_id, sum(realtime) t FROM otpedges GROUP BY network_id) AS n " \ "WHERE id=network_id " con.exe(sql) con.commit() #stationary times print 'Stationaire tijden optellen...' sql = "UPDATE otpnetwork SET touristtime_s=r.t FROM " \ "(SELECT stationaryedge, sum(realtime) t FROM routes WHERE stationaryedge IS NOT NULL GROUP BY stationaryedge) AS r " \ "WHERE id=stationaryedge; " \ "UPDATE otpnetwork SET used_s=r.u FROM " \ "(SELECT stationaryedge, count(DISTINCT name) u FROM routes " \ "INNER JOIN trajectories ON routes.traj_id=trajectories.id GROUP BY stationaryedge) AS r " \ "WHERE otpnetwork.id=stationaryedge; " \ "UPDATE otpnetwork SET used_s=0, touristtime_s=0 WHERE used_s IS NULL" con.exe(sql) con.commit() #total times print 'Totale tijden uitrekenen...' sql = "UPDATE otpnetwork SET touristtime=n.touristtime_r+n.touristtime_s FROM " \ "otpnetwork AS n " \ "WHERE otpnetwork.id=n.id " con.commit() con.exe(sql) con.sluit()
def calcEdgeLength(): print 'Lengtes uitrekenen...' con = dbconnect.Verbinding() sql = "UPDATE otpnetwork SET length=ST_Length(ST_Transform(n.geom, 28992)) " \ "FROM otpnetwork AS n WHERE otpnetwork.id=n.id" con.exe(sql) con.commit() con.sluit()
def calcCostPaths(): print 'Kosten berekenen...' con = dbconnect.Verbinding() sql = "UPDATE routes SET path=r.g, cost=r.c FROM " \ "(SELECT routes.id i, steps.g g, steps.c c FROM routes " \ "INNER JOIN (SELECT route_id, ST_MakeLine(ptrouting.geom_p) g, sum(route_steps.cost) c FROM route_steps " \ "INNER JOIN ptrouting ON edge=ptrouting.id WHERE edge<>-1 GROUP BY route_id) AS steps ON routes.id=steps.route_id " \ ") AS r WHERE routes.id=r.i;" con.exe(sql) con.commit() con.sluit()
def calcRealTimes(): print 'Tijden berekenen...' con = dbconnect.Verbinding() sql = "UPDATE routes SET realtime=r.r FROM" \ "(SELECT routes.id i, abs(extract(epoch FROM tgtt.tijddatum-srct.tijddatum)) r 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 " \ ") AS r WHERE routes.id=r.i" con.exe(sql) con.commit() con.sluit()
def createPoints(veld, srid): kursor = dbconnect.Verbinding().kursor sql = "SELECT id, lat, lon FROM scraped_tweets" kursor.execute(sql) coords = kursor.fetchall() monitor = VoortgangRegel(len(coords), VoortgangRegel.MODUS_NUM, 'Regels verwerkt') monitor.nieuweRegel() for coord in coords: sql = "UPDATE scraped_tweets SET %s=ST_Transform(ST_SetSRID(ST_MakePoint(%f, %f), 4326), %d) WHERE id=%d" % ( veld, coord[2], coord[1], srid, coord[0]) kursor.execute(sql) monitor.plusEen() kursor.connection.commit() kursor.close()
def createLines(): kursor = dbconnect.Verbinding().kursor sql = "SELECT id FROM trajectories" kursor.execute(sql) ids = kursor.fetchall() monitor = outputs.VoortgangRegel(len(ids), outputs.VoortgangRegel.MODUS_NUM, 'Trajectories verwerkt') for id in ids: sql = "UPDATE trajectories SET lijn=traj.lijn FROM (SELECT ST_MakeLine(tw.geom_3857) AS lijn " \ "FROM (SELECT scraped_tweets.geom_3857 FROM rel_traj_tweets INNER JOIN " \ "scraped_tweets ON rel_traj_tweets.tweet_id=scraped_tweets.id WHERE rel_traj_tweets.traj_id=%d) AS tw) AS traj WHERE id=%d" % (id[0], id[0]) kursor.execute(sql) monitor.plusEen() kursor.connection.commit() kursor.close()
def calcStationaryRouteEdges(): con = dbconnect.Verbinding() sql = "SELECT routes.id, the_geom FROM routes " \ "INNER JOIN rel_traj_tweets ON routes.endrel=rel_traj_tweets.id " \ "INNER JOIN scraped_tweets ON rel_traj_tweets.tweet_id=scraped_tweets.id " \ "WHERE startnode=endnode AND otpduration IS NULL" routes = con.selectAll(sql) monitor = VoortgangRegel(len(routes), VoortgangRegel.MODUS_NUM, 'Routes verwerkt') for id, geom in routes: sql = "UPDATE routes SET stationaryedge=nn.id, isstationary=TRUE FROM " \ "(SELECT id FROM (SELECT id, geom FROM otpnetwork WHERE ST_DWithin('%s', geom, 0.0007) AND mode='WALK') AS n " \ "ORDER BY ST_Distance('%s', n.geom) ASC LIMIT 1) AS nn " \ "WHERE routes.id=%d" % (geom, geom, id) con.exe(sql) monitor.plusEen() con.commit() con.sluit()
def calcEdgeTime(): con = dbconnect.Verbinding() sql = "SELECT otpedges.id, ST_Length(ST_Transform(otpedges.geom, 28992)), ST_Length(ST_Transform(e.g, 28992)), otplegs.realtime, e.c FROM otpedges " \ "INNER JOIN (SELECT leg_id, ST_MakeLine(geom) g, count(id) c FROM otpedges GROUP BY leg_id) AS e ON otpedges.leg_id=e.leg_id " \ "INNER JOIN otplegs ON otpedges.leg_id=otplegs.id " edges = con.selectAll(sql) monitor = VoortgangRegel(len(edges), VoortgangRegel.MODUS_NUM, 'Edges verwerkt') for id, distance, totaldistance, totaltime, edgecount in edges: try: if totaldistance == 0: realtime = totaltime * (1 / edgecount) else: realtime = totaltime * (distance / totaldistance) except Exception, e: continue sql = "UPDATE otpedges SET realtime=%f WHERE id=%d" % (realtime, id) con.exe(sql) monitor.plusEen()
def calcTimeSurplus(): print 'Tijdsurplus berekenen...' con = dbconnect.Verbinding() sql = "UPDATE routes SET otpduration=d, otpsurplus=(r.realtime)-d " \ "FROM routes AS r INNER JOIN " \ "(SELECT route_id, sum(duration) d FROM otplegs GROUP BY route_id) AS l " \ "ON r.id=l.route_id " \ "WHERE routes.id=r.id" con.exe(sql) sql = "SELECT id, route_id, legnum, mode, duration, distance FROM otplegs" legs = con.selectAll(sql) sql = "SELECT id, isfast, otpsurplus FROM routes" routes = con.selectAll(sql) monitor = VoortgangRegel(len(routes), VoortgangRegel.MODUS_NUM, 'Routes verwerkt') # arrays maken ids = [] routeids = [] legnums = [] modes = [] durations = [] distances = [] for leg in legs: ids.append(leg[0]) routeids.append(leg[1]) legnums.append(leg[2]) modes.append(leg[3]) durations.append(leg[4]) distances.append(leg[5]) #routes verwerken for id, isfast, otpsurplus in routes: legids = [i for i, x in enumerate(routeids) if x == id] if isfast: routelegnums = [legnums[i] for i in legids] maxlegnumid = legnums.index(max(routelegnums)) for i in legids: if legnums[i] == 0 or legnums[i] == legnums[maxlegnumid]: surplus = otpsurplus * ( distances[i] / (distances[0] + distances[maxlegnumid])) else: surplus = 0 tijd = durations[i] + surplus sql = "UPDATE otplegs SET timesurplus=%f, realtime=%f WHERE id=%d" % ( surplus, tijd, i) con.exe(sql) else: walkdistance = sum([ distances[j] for j in [i for i, x in enumerate(modes) if x == 'WALK'] ]) for i in legids: if modes[i] == 'WALK': surplus = otpsurplus * (durations[i] / walkdistance) else: surplus = 0 tijd = durations[i] + surplus sql = "UPDATE otplegs SET timesurplus=%f, realtime=%f WHERE id=%d" % ( surplus, tijd, i) con.exe(sql) monitor.plusEen() con.commit() con.sluit()