Hey all,
I’m confronting a problem concerning the routing algorithm pgr_withPoints(). I have one table ‘fussg_routing’ with the columns (gid, geom, strname, length, source, target) (e.g.: 14365, 0105000020E8640000010000000102000000020000000000000000571D4100000000072E554100000000E4541D4100000000FA2D5541, Parkweg, 144.668586776812, 47, 48), one table ‘fussg_routing_vertices_pgr’ with the nodes and the node_geometry (you can join the tables via node_id and source). When using the algorihtm pgr_dijkstra() everything works fine and I get the route between the nodes closest to my start and end point.
Now I am not trying to start at the closest node but at the closest point lying on the closest street using pgr_withPoints().
What did I do so far?
I have created a table ‘pois’ similiar to the sample table given in the docu (https://docs.pgrouting.org/2.4/en/sampledata.html) with
CREATE TABLE IF NOT EXISTS pois (
pid SERIAL PRIMARY KEY,
edge_id BIGINT,
side CHAR,
fraction FLOAT,
the_geom geometry,
newPoint geometry);
In the next step I inserted the needed data with
WITH point_data AS
(SELECT * FROM “fussg_routing” ORDER BY ST_DISTANCE(ST_TRANSFORM(ST_PointFromText(‘POINT(8.675555580548018 50.10969977068385)’, 4326), 25832), ST_TRANSFORM(“fussg_routing”.geom, 25832)) LIMIT 1)
INSERT INTO “pois” (pid, edge_id, fraction, the_geom, newpoint) values
((SELECT gid FROM point_data), – as pid
(SELECT gid FROM point_data), – as edge_id
(SELECT ST_LineLocatePoint(
ST_TRANSFORM((ST_DUMP(geom)).geom, 25832), ST_TRANSFORM(ST_PointFromText(‘POINT(8.675555580548018 50.10969977068385)’, 4326), 25832)) FROM point_data), – as fraction
(SELECT ST_TRANSFORM(ST_PointFromText(‘POINT(8.675555580548018 50.10969977068385)’, 4326), 25832)), – as the_geom
(SELECT ST_LineInterpolatePoint(
ST_TRANSFORM((ST_DUMP(geom)).geom, 25832),
(SELECT ST_LineLocatePoint(
ST_TRANSFORM((ST_DUMP(geom)).geom, 25832), ST_TRANSFORM(ST_PointFromText(‘POINT(8.675555580548018 50.10969977068385)’, 4326), 25832)))) FROM point_data)); – as newPoint
So far, everything works fine and the created geometry as well as edge_id and fraction are how they are supposed to be.
Next, I am trying to apply the pgr_withPoints() algorithm with one of the following queries:
SELECT * FROM pgr_withPoints(
‘SELECT “gid” AS id, strname, source, target, length AS cost FROM “fussg_routing”’,
‘SELECT 9601 AS edge_id, 0.448876481757386::float as fraction from “pois”’,
5529,5708);
and:
SELECT * FROM pgr_withPoints(
‘SELECT “gid” AS id, strname, source, target, length AS cost FROM “fussg_routing”’,
‘SELECT edge_id, fraction from “pois”’,
5529,5708)
And here the output is zero. Querying both parts on their own the output looks okay.
first: (e.g. limit 2):
id strname source target cost
14365 Parkweg 47 48 144.668586776812
14677 Altenhöferalle 49 50 30.6757233003559
second:
edge_id fraction
9601 0.448876481757386
9590 0.0933449381876742
The start and end vertices (5529,5708) are the closest nodes to the given coordinates.
Concerning the algorithm I notices some problems like:
- When creating the pois table the fraction column was defined as float, but it had to be redefined as float in the pgr_withPoints query (like: 0.448876481757386::float as fraction). Otherwise the error occured:
Unexpected Column 'fraction' type. Expected ANY-NUMERICAL
CONTEXT: SQL function "pgr_withpoints" statement 1
- Using the sample data and example (https://docs.pgrouting.org/2.4/en/sampledata.html) an outpout is only created when not defining an EPSG whlie creating the pointsofinterest table (e.g.: the_geom geometry instead of the_geom geometry(Point,25832)). I don’t know if the problem occurs here?
I am using:
PostgreSQL 10.7 (Ubuntu 10.7-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0, 64-bit
I would appreciate any ideas and help.
Thank you in advance!