1

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?

  • What output do you get? For similar task I used a table with lines between each point that contained fields 'to' and 'from' and fetched geometry from that table based on the routing nodes output. – SS_Rebelious Nov 14 '14 at 12:15
  • You can use ST_MakeLine to join up the nodes with order by seq, assuming that the nodes map to some actual geometries. – John Powell Nov 14 '14 at 12:27
  • @JohnBarça My friend what do you mean by actual geometries? – drizo Nov 14 '14 at 13:17
  • @SS_Rebelious I get nodes teh esge(aka connectors of nodes , cost and the sequence they are connected. what could I use? – drizo Nov 14 '14 at 13:18
  • The nodes that you input and output are points in a graph. If these nodes map to a real world, x, y, which you can make a Postgis point from, then you can use ST_MakeLine to join them up. – John Powell Nov 14 '14 at 13:20
1

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.

  • guys if I change the source and target param values, the query wont work.do you know what is wrong? roads is a table extracted from an osm shapefile. – drizo Nov 14 '14 at 16:44
  • 1
    Is that related with this issue? gis.stackexchange.com/questions/121265/… – rnuryadin Nov 14 '14 at 16:56
1

Following the 2nd example from ST_MakeLine, you could join the nodes from your routing output, to nodes representing a Postgis point, and use this to create a line, something like:

SELECT ST_MakeLine(gm.the_geom ORDER BY rt.seq) As route_geom
FROM route rt inner join geometry gm on rt.node = gm.node ;

where route is the output from pgRouting and geometry is a table of Points and associated nodes that you use to join the two tables.

  • my friend can what is gm and rm? ( gm.the_geom and rt.seq) – drizo Nov 14 '14 at 13:46
  • rt is an alias for your route output table, seq, node, etc and gm is an alias for a geometry table containing node, geom, where geom is a Postgis Point. It just makes the query easier to write. – John Powell Nov 14 '14 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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