[GIS] How to visualize pgrouting result

openlayers-2pgroutingpostgisqueryvisualisation

So I use this query 

            SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('
            SELECT gid AS id,
                     source::integer,
                     target::integer,
                     st_length(geom) AS cost
                    FROM leoforia',
            30, 40, false, false) ;

and as a result I get a table but when I try to open it in QGIS it says that table has no geometry. How to create geometry and generally visualize my result in OpenLayers?

Best Answer

You could get the geometry by joining your query result's id2/edge with your layers's geometry ID (gid):

SELECT s.seq, s.node, s.edge, s.cost,
b.gid, b.geom
FROM
(
            SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('
            SELECT gid AS id,
                     source::integer,
                     target::integer,
                     st_length(geom) AS cost
                    FROM leoforia',
            30, 40, false, false) 
) s
LEFT JOIN leoforia b
ON (b.gid = s.edge)

That way, you'll get geometry column which could be fetched and pushed to OpenLayer's vector layer.

Related Question