1

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
0

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 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.