Using pgrouting and algorithms in PostgreSQL?

Question

I am trying my first pgrouting in a roads shapefile I load it in PostgreSQL first, the table has osm_id, gid, name, ref, type, oneway, maxspeed

Then I add columns source and target in table:

ALTER TABLE roads ADD COLUMN "source" integer;
ALTER TABLE roads ADD COLUMN "target" integer;

Then I make the topology with function:

SELECT pgr_createTopology('roads', 0.0001, 'geom', 'gid');

make indexis

CREATE INDEX roads_source_idx ON roads("source");
CREATE INDEX roads_target_idx ON roads("target");

Add cost column and count the cost with geom column use ST_LENGTH method

ALTER TABLE roads ADD COLUMN length double precision;
UPDATE roads SET length = ST_LENGTH(geom);

Add reverse cost column and count it

ALTER TABLE roads ADD COLUMN reverse_cost double precision;
UPDATE roads SET reverse_cost = length;

Finally I run the distrack query, which is running in PostgreSQL, but if I try to run this query in QGIS it is not running. This is the first problem.

SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('
                SELECT gid AS id,
                         source::integer,
                         target::integer,
                         shape_leng::double precision AS cost
                        FROM roads',
                30, 300, false, false);

Then I try to use A*

I count the x1 y1 x2 y2 but the columns is empty and I don't know why insert the columns with these queries:

ALTER TABLE roads ADD COLUMN x1 double precision;
ALTER TABLE roads ADD COLUMN y1 double precision;
ALTER TABLE roads ADD COLUMN x2 double precision;
ALTER TABLE roads ADD COLUMN y2 double precision;

Then I use these queries to field them but the columns still empty after execution:

UPDATE roads SET x1 = ST_x(ST_startpoint(geom));
UPDATE roads SET y1 = ST_y(ST_startpoint(geom));
UPDATE roads SET x2 = ST_x(ST_endpoint(geom));
UPDATE roads SET y2 = ST_y(ST_endpoint(geom));

I read in net same versions have problem with startpoint and endpoint and I use the PointN but I still take empty columns:

UPDATE roads SET x1 = ST_x(ST_PointN(geom, 1));
UPDATE roads SET y1 = ST_y(ST_PointN(geom, 1));
UPDATE roads SET x2 = ST_x(ST_PointN(geom, ST_NumPoints(geom)));
UPDATE roads SET y2 = ST_y(ST_PointN(geom, ST_NumPoints(geom)));

And the query for A*

SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_astar('
                SELECT gid AS id,
                         source::integer,
                         target::integer,
                         shape_leng::double precision AS cost,
                         x1, y1, x2, y2
                        FROM roads',
                30, 60, false, false);

How can I show the routing in QGIS from PostgreSQL I did it, but I still have problem with A* x1,y1 & x2,y2 after the update is still empty the columns x1 y1 x2 y2?

I try to use these queries but the x2y2 is not filling:

UPDATE roads SET x1 = ST_X(ST_StartPoint(ST_geometryN(geom,1)));
UPDATE roads SET y1 = ST_Y(ST_StartPoint(ST_geometryN(geom,1)));
UPDATE roads SET x2 = ST_X(ST_EndPoint(ST_geometryN(geom,ST_NumPoints(geom))));
UPDATE roads SET y2 = ST_Y(ST_EndPoint(ST_geometryN(geom,ST_NumPoints(geom))));

Please if anyone can help me

Answer 1

Try running the query in a terminal window or pgAdmin3 to see if it works there.

If you want to visualize the routing query in QGIS look at the workshop:

• Create a database connection and add the “ways” table as a background layer.
• Add another layer of the “ways” table but select Build query before adding it.

Then type the following into the SQL where clause field:

"gid" IN ( SELECT id2 AS gid FROM pgr_dijkstra('
                SELECT gid AS id,
                         source::integer,
                         target::integer,
                         length::double precision AS cost
                        FROM ways',
                30, 60, false, false) a LEFT JOIN ways b ON (a.id2 = b.gid)
)