Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

My goal is to produce a Web UI for pgRouting using this tutorial. I have already imported my data from an OSM pBF to PostGIS using osm2po. Using the pgRoutingLayer plugin for QGIS, I was able to figure out the SQL code to create routes using driving time as the cost.

SELECT ways.*, route.cost AS route_cost FROM ways JOIN (
    SELECT * FROM shortest_path(
    'SELECT gid AS id, 
            source::int4 AS source, 
            target::int4 AS target, 
            cost::float8 AS cost,
            reverse_cost::float8 AS reverse_cost FROM ways',
            62,19169, True, True)) 
    AS route ON ways.gid= route.edge_id;

My next step is to convert this into GeoJSON as done in Part 1 of the tutorial. This is what seemed logical:

SELECT * INTO export
FROM ways.*, route.cost AS route_cost FROM ways JOIN (
    SELECT * FROM shortest_path(
    'SELECT gid AS id, 
            source::int4 AS source, 
            target::int4 AS target, 
            cost::float8 AS cost,
            reverse_cost::float8 AS reverse_cost FROM ways',
            62,19169, True, True)) 
    AS route ON ways.gid= route.edge_id;
SELECT ST_AsGeoJSON(ST_UNION(the_geom)) AS geom_union
FROM export;

ERROR:  syntax error at or near ","
LINE 2:  FROM ways.*, route.cost AS route_cost FROM ways JOIN (
                    ^

but I can't get it to work. Honestly, I'm not very well acquainted with SQL, can someone point out my mistake?

edit: This seems to work, but does it do the same thing?

CREATE TEMPORARY TABLE route_result AS 
SELECT ways.*, route.cost AS route_cost FROM ways JOIN (
    SELECT * FROM shortest_path(
    'SELECT gid AS id, 
            source::int4 AS source, 
            target::int4 AS target, 
            cost::float8 AS cost,
            reverse_cost::float8 AS reverse_cost FROM ways',
            62,19169, True, True)) 
    AS route ON ways.gid= route.edge_id; 
SELECT ST_AsGeoJSON(ST_UNION(the_geom)) AS geom_union 
FROM route_result;
share|improve this question
add comment

1 Answer

How about this:

SELECT ST_AsGeoJSON(ST_UNION(b.the_geom)) AS geojson 
    FROM shortest_path('SELECT ... FROM ...',<start>,<end>,true,true) a,
         ways b 
    WHERE a.edge_id = b.id;

This should return the route as a single geometry.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.