TransWikia.com

Removing the mirror route on postgres for shortestpath using pgr_ksp algorithm?

Geographic Information Systems Asked by Srija Harshika on February 18, 2021

I have created an underground route for few points and when I try to see the possible routes using pgr_ksp I end up with an extra route which is not present in the geom table.

In the image below, this is one of the possible routes. In this route I am getting a line which is imaginary as there is no underground route to the diagonal.

This is one of the possible routes.In this route i am getting a line which is imaginary,there is no underground route to the diagonal.

enter image description here

How do I remove that line?

Below is the query used:

SELECT 
  ST_AsText(ST_MakeLine(the_geom)),
  sum(cost),
  id1,
  sum(agg_cost)  
FROM pgr_KSP( 
       'SELECT structure_route_id::integer as id , 
         source::integer as source, 
         target::integer as target, 
         project_id, 
         st_length(st_transform(the_geom,4326)::geography)::double precision as cost,
         st_length(st_transform(the_geom,4326)::geography)::double precision as reverse_cost,
         st_length(st_transform(the_geom,4326)::geography)::double precision as agg_cost 
       FROM icw.underground_route  
       where project_id=''"+ProjectId+"''  
         and source is not null 
         and target is not null 
       order by source',
       "+source+",
       "+target+",10,false) as di  
JOIN icw.underground_route pt  
 ON di.id3 = pt.structure_route_id 
group by id1 
ORDER BY id1 ASC

One Answer

The issue is caused because the ordering of the input points is not specified. ST_MAKELINE receives a set of points (in an undetermined order) and aggregates them, so instead of creating a line A-B-C it may creates A-C-B.

You can specify the ORDER BY clause within ST_MAKELINE (see the doc example), using the returned sequence

SELECT 
  ST_AsText(ST_MakeLine(the_geom) ORDER BY seq),
  sum(cost),
  id1,
  sum(agg_cost)  
FROM [...]
GROUP BY id1 

Answered by JGH on February 18, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP