PL/pgSQL error inshootingstar_sp_smart

[ochriste]

hello

Im using the shootingstar_sp_smart in a project but I noticed my Postgres log are getting cluttered by such kind of error messages:

ERROR: cannot EXECUTE a null querystring
CONTEXT: PL/pgSQL function "shootingstar_sp_smart" line 102 at FOR over EXECUTE statement
PL/pgSQL function "shootingstar_sp_smart" line 5 at FOR over EXECUTE statement

the problem occur when the start or end point is away from any arcs of the network, so I would expect a null or zero sized result instead of the PL/pgSQL error.

I have prepared a simplified set of data to demonstrate the problem, as postgres dump and as shapefile:
http://dev.camptocamp.com/files/shootingstar_sp_smart/
The postgres dump was done on a Postgres 8.3 with the -F c option.

here are two sql queries, one work as expected (one path found), the other crash (no path found):

path ok:

SELECT astext(ST_Collect(ST_LineMerge(the_geom))) AS geom FROM
shootingstar_sp_smart('arcs', 659823.0, 165715.0,
659843.0, 164255.0, 200.0, E'ST_SetSRID(''''''''BOX3D(651383.0 154535.0, 679523.0 170415.0)''''''''::box3d, 21781)',
E'length * (CASE WHEN wander_n THEN 0.01 WHEN wander_r THEN 0.01 WHEN wander_l THEN 0.01 WHEN ww THEN 0.01 WHEN klass_1 THEN 1.0 WHEN klass_2 THEN 1.0 ELSE 1.0 END)', FALSE, FALSE);

result:

MULTILINESTRING((659823 165715,659813.652377407 165728.155068868),(659813.652377407 165728.155068868,659826.1 165737,659860.9 165727.7,659903.4 165746.5,659917.4 165744.5,659900.6 165728,659923.1 165735.5,659935.6 165727.5,659892.9 165699,659890.6 165690.2,659968.6 165696.1),(659836.362033598 164255.445833564,659836.5 164257.5,659889.3 164344.4,659890.2 164375.5,659865.5 164388.2,659860.7 164405.7,659871.2 164425.7,659863.2 164456.5,659899.7 164511.5,659881.7 164543.5,659893.5 164645.5,659961.5 164723.7,660003.5 164790.5,660024.7 164802,660048 164828.7,660074.7 164882.5,660143 164941.7,660178 164979.2,660190.5 165006.2,660206.2 165008.7,660243.5 165063.5,660269.2 165082.2,660280.7 165123.2,660261.7 165190.7,660209 165217.7,660211.2 165239.2,660199 165257.2,660199 165275.5,660177.2 165287,660198.7 165305,660174.7 165331.2,660182.2 165340.2,660202.7 165344,660205.2 165355,660195 165370.5,660194.2 165395.5,660171 165397.2,660184 165414.5,660178.3 165419.6,660182 165433.7,660163 165441.2,660132.2 165434.7,660098.2 165451,660049.2 165439,660023.6 165440.8,659989.5 165500.5,659985.7 165537.5,659997.2 165586,659923 165598.7,659928.9 165642.9,659967.7 165678,659968.6 165696.1),(659836.362033598 164255.445833564,659843 164255))

no path:

SELECT astext(ST_Collect(ST_LineMerge(the_geom))) AS geom FROM
shootingstar_sp_smart('arcs', 659863.0, 164295.0,
660583.0, 163095.0, 200.0, E'ST_SetSRID(''''''''BOX3D(651383.0 154535.0, 679523.0 170415.0)''''''''::box3d, 21781)',
E'length * (CASE WHEN wander_n THEN 0.01 WHEN wander_r THEN 0.01 WHEN wander_l THEN 0.01 WHEN ww THEN 0.01 WHEN klass_1 THEN 1.0 WHEN klass_2 THEN 1.0 ELSE 1.0 END)', FALSE, FALSE);

result:

LOG: statement: SELECT astext(ST_Collect(ST_LineMerge(the_geom))) AS geom FROM
shootingstar_sp_smart('arcs', 659863.0, 164295.0,
660583.0, 163095.0, 200.0, E'ST_SetSRID(''''''''BOX3D(651383.0 154535.0, 679523.0 170415.0)''''''''::box3d, 21781)',
E'length * (CASE WHEN wander_n THEN 0.01 WHEN wander_r THEN 0.01 WHEN wander_l THEN 0.01 WHEN ww THEN 0.01 WHEN klass_1 THEN 1.0 WHEN klass_2 THEN 1.0 ELSE 1.0 END)', FALSE, FALSE);
ERROR: cannot EXECUTE a null querystring
CONTEXT: PL/pgSQL function "shootingstar_sp_smart" line 102 at FOR over EXECUTE statement
PL/pgSQL function "shootingstar_sp_smart" line 5 at FOR over EXECUTE statement

please let me know if I can give more information to solve this problem.

best regards
Oliver

[dkastl]

Hi Oliver,

Without trying your sample data I guess, that this is caused by this function:
https://github.com/pgRouting/pgrouting-contrib/blob/master/wrapper/routing_core_smart.sql#L74

When there is no nearest link within the bbox around your start/end point, it may cause such an error as you already guessed. When you follow the parameter called "distance" in this function, then you will see that it's the same as "delta" in shooting_star_smart function. Which is actually used for another type of bbox, so this could indeed be written better.

If you have some idea how to do it better, let me know. Of course it's possible to add several distance parameters to a function, which is then a bit confusing as well. Because a larger bbox around a node shouldn't slow down very much, you could fo example double the distance parameter. Maybe that helps already.

[ochriste]

thank you for your suggestion, trying different delta values showed a minimum of 607 should be used to at least avoid the psql error and get an empty result with the query I used for test.
I will have to do some empiric testing to see the impact on performances with a greater values at all scale and in area with different network density.

sadly Im not knowledgeable enough in this matter to be able to propose a fix at the moment :/

[ochriste]

sorry, I missclicked on the comment&close button

[dkastl]

You could modify the wrapper function where it calls the "find closest link" function, and instead of "delta" you could pass "2 * delta". For example here:
https://github.com/pgRouting/pgrouting-contrib/blob/master/wrapper/routing_core_smart.sql#L495

[ochriste]

While experimenting to find an adequate delta value, I stumbled upon another error:

SELECT astext(ST_Collect(ST_LineMerge(the_geom))) AS geom FROM
shootingstar_sp_smart('arcs', 559547.0, 212509.0, 559505.0, 212528.0, 50.0,
E'ST_SetSRID(''''''''BOX3D(556195.5 211448.5, 561675.5 214653.5)''''''''::box3d, 21781)',
E'length * (CASE WHEN wander_n THEN 0.01 WHEN wander_r THEN 0.01 WHEN wander_l THEN 0.01 WHEN ww THEN 0.01 WHEN klass_1 THEN 1.0 WHEN klass_2 THEN 1.0 ELSE 1.0 END)',
FALSE, FALSE);

NOTICE: IllegalArgumentException: point array must contain 0 or >1 elements

ERROR: GEOS LineMerge() threw an error!

sometime, for the same query, the notice also say:

NOTICE: IllegalArgumentException: Cannot compute the quadrant for point (0,0)

Sadely increasing the delta value didnt solve this new error :(

any idea what could be the cause ?