2

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

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)
)
  • The query is work find in pgAdmin3 is work only distrack but in A* i can fill the colums x1,y1 x2,y2 – vagelis Oct 5 '14 at 11:38

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.