PgRoute queries on OSM data

pgRoute_queries.sql

-- SHORTEST PATH
SELECT * FROM pgr_dijkstra('SELECT gid id, source, target, cost, reverse_cost FROM ways', 645, 803, directed:=true);

-- COMBINING GEOMETRY
SELECT seq,name, node, edge, pgr.cost, agg_cost, st_astext(the_geom) AS geom FROM pgr_dijkstra('SELECT gid id, source, target, cost, reverse_cost FROM ways', 645, 803, directed:=true) AS pgr JOIN ways w ON pgr.edge = w.gid;

-- VIA
SELECT seq, node, edge, pgr.cost, agg_cost, st_astext(the_geom) AS geom FROM pgr_dijkstravia('SELECT gid id, source, target, cost, reverse_cost FROM ways', array[645, 885, 803], directed:=true) AS pgr JOIN ways w ON pgr.edge = w.gid;

-- ONE TO MANY
SELECT seq, node, edge, pgr.cost, agg_cost, st_astext(the_geom) AS geom FROM pgr_dijkstra('SELECT gid id, source, target, cost, reverse_cost FROM ways', 645, array[803, 804], directed:=true) AS pgr JOIN ways w ON pgr.edge = w.gid;

-- DRIVE TIME/ CATCHMENT MAP
SELECT 1 As id, ST_SetSRID(pgr_pointsAsPolygon( $$SELECT dd.seq AS id, ST_X(v.the_geom) AS x, ST_Y(v.the_geom) As y 
FROM pgr_drivingDistance($sub$SELECT gid As id, source, target, cost_s AS cost, reverse_cost_s AS reverse_cost 
FROM ways$sub$,(SELECT n.id FROM ways_vertices_pgr AS n ORDER BY ST_SetSRID(ST_Point(77.54653690,12.97587840),4326) <-> n.the_geom LIMIT 1) ,  2*60, true ) AS dd INNER JOIN ways_vertices_pgr AS v ON dd.node = v.id$$), 4326) As geom;