[pgrouting-users] No output received with pgr_withPoints()

Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

[pgrouting-users] No output received with pgr_withPoints()

Isabell Klipper
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!
 
 
 

_______________________________________________
Pgrouting-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/pgrouting-users