Iterating over LineString and applying filter in PostGIS

Question

I want to iterate over a LineString and apply a filter so that I create a new LineString with those points that succeed in the test. How do I do that?

My goal is to find some outliers that are too far from the rest of the points in the path and remove them from the result. So I would like to measure the distance between each point in the LineString to make sure that they are not too far from each other.

You can get some inspiration here to extract each segment from the line, then you can compute their length and filter them and at last reconstruct the line. — JGH, Jul 25 '23 at 12:40

score 2 · Accepted Answer · edited Jul 25 '23 at 11:03

You can St_dump your points from the LineString

order them by St_linelocatePoint and use a where condition to not use the points you don't want

and remake the LineString with St_makeline groupind by "id" of the line.

so something like this ... I guess

With points as (
select (St_dump(t.geom)).geom as geom, lt.id as lt_id
from linestring_table as lt)
select St_makeline(p.geom) from points as p,
from linestring_table as lt
where 'condition to keep point'
group by p.lt_id
order by St_linelocatePoint(lt.geom,p.geom)

Iterating over LineString and applying filter in PostGIS

1 Answers1