<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Hey all, </div>
<div>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. </div>
<div>Now I am not trying to start at the closest node but at the closest point lying on the closest street using pgr_withPoints(). </div>
<div> </div>
<div>What did I do so far?</div>
<div>I have created a table 'pois' similiar to the sample table given in the docu (<a href="https://docs.pgrouting.org/2.4/en/sampledata.html">https://docs.pgrouting.org/2.4/en/sampledata.html</a>) with </div>
<div>
<pre>CREATE TABLE IF NOT EXISTS pois (
pid SERIAL PRIMARY KEY,
edge_id BIGINT,
side CHAR,
fraction FLOAT,
the_geom geometry,
newPoint geometry);</pre>
<div>In the next step I inserted the needed data with </div>
<div> </div>
<div>WITH point_data AS<br/>
(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)</div>
<div><br/>
INSERT INTO "pois" (pid, edge_id, fraction, the_geom, newpoint) values<br/>
((SELECT gid FROM point_data), -- as pid<br/>
(SELECT gid FROM point_data), -- as edge_id<br/>
(SELECT ST_LineLocatePoint(<br/>
ST_TRANSFORM((ST_DUMP(geom)).geom, 25832), ST_TRANSFORM(ST_PointFromText('POINT(8.675555580548018 50.10969977068385)', 4326), 25832)) FROM point_data), -- as fraction<br/>
(SELECT ST_TRANSFORM(ST_PointFromText('POINT(8.675555580548018 50.10969977068385)', 4326), 25832)), -- as the_geom<br/>
(SELECT ST_LineInterpolatePoint(<br/>
ST_TRANSFORM((ST_DUMP(geom)).geom, 25832),<br/>
(SELECT ST_LineLocatePoint(<br/>
ST_TRANSFORM((ST_DUMP(geom)).geom, 25832), ST_TRANSFORM(ST_PointFromText('POINT(8.675555580548018 50.10969977068385)', 4326), 25832)))) FROM point_data)); -- as newPoint<br/>
</div>
<div>So far, everything works fine and the created geometry as well as edge_id and fraction are how they are supposed to be. </div>
<div> </div>
<div>Next, I am trying to apply the pgr_withPoints() algorithm with one of the following queries:</div>
<div> </div>
<div>SELECT * FROM pgr_withPoints(<br/>
'SELECT "gid" AS id, strname, source, target, length AS cost FROM "fussg_routing"',<br/>
'SELECT 9601 AS edge_id, 0.448876481757386::float as fraction from "pois"',<br/>
5529,5708);</div>
<div> </div>
<div>and:</div>
<div> </div>
<div>SELECT * FROM pgr_withPoints(<br/>
'SELECT "gid" AS id, strname, source, target, length AS cost FROM "fussg_routing"',<br/>
'SELECT edge_id, fraction from "pois"',<br/>
5529,5708)</div>
<div> </div>
<div>And here the output is zero. Querying both parts on their own the output looks okay. </div>
<div>first: (e.g. limit 2):</div>
<div>id strname source target cost<br/>
14365 Parkweg 47 48 144.668586776812<br/>
14677 Altenhöferalle 49 50 30.6757233003559</div>
<div> </div>
<div>second:<br/>
edge_id fraction<br/>
9601 0.448876481757386<br/>
9590 0.0933449381876742</div>
<div> </div>
<div>The start and end vertices (5529,5708) are the closest nodes to the given coordinates.</div>
<div> </div>
<div>Concerning the algorithm I notices some problems like:</div>
<div>- 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:
<pre>Unexpected Column 'fraction' type. Expected ANY-NUMERICAL</pre>
<pre>CONTEXT: SQL function "pgr_withpoints" statement 1</pre>
</div>
<div> </div>
<div>- Using the sample data and example (<a href="https://docs.pgrouting.org/2.4/en/sampledata.html">https://docs.pgrouting.org/2.4/en/sampledata.html</a>) 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? </div>
<div> </div>
<div>I am using:<br/>
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</div>
<div> </div>
<div>
<div>I would appreciate any ideas and help.</div>
<div>Thank you in advance!</div>
</div>
<div> </div>
<div> </div>
<div> </div>
</div></div></body></html>