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.
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
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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP