[postgis-users] Merging points in right order

[Nicolas Ribot]

> Hi, I have some considerable difficulty with the following problem:
>
> I have a LINESTRING table with walking tracks
> I have a MULTILINESTRING table with contours (with elevation attribute).
>
> I can successfully get the points of intersection between tracks and
> contours to obtain the elevation and coordinates at the point of
> intersection.
> What I need to do next, and I don't know how, is to merge the points of
> intersection for a given track with the track linestring, i.e. add the
> intersection points to the track linestring (or create a new track which
> includes the intersection points). However the intersection points have to
> be inserted in the right position in the track linestring, not just at the
> front, as ST_LINEMERGE() seems to do. The reason is I need then to traverse
> the track, point by point, to obtain the distance between points.
> Any suggestions will be most welcome, thank you.
> George
>

Hi George,

I had to do the same thing some years ago and did it in Java :(
Now I learned Postgis a bit more, here is an approach that seems to work:

1°) Dump points composing tracks, compute their location along the
track (I call this location 'locus')
2°) Compute the intersection between the tracks and the contour lines.
3°) Compute the location of theses intersection points along the track
4°) Build new tracks by making a line with all the points got at the
previous steps, order by locus.

The query looks like:

select id, st_makeline(geom) as geom from
(
with track_points (id, ordre, geom) as (
	select id, (st_dumppoints(geometry)).path[1], (st_dumppoints(geometry)).geom
	from track
	
	UNION

	select t.id, (st_dumppoints(st_intersection(t.geometry, c.geometry))).path[1],
		(st_dumppoints(st_intersection(t.geometry, c.geometry))).geom
	from track t, contour c
	where st_intersects(t.geometry, c.geometry)
	
) select t.id, st_line_locate_point(t2.geometry, t.geom) as locus, t.geom
from track_points t, track t2
where t.id = t2.id
order by id, locus
) as foo
group by id;

The two pictures show the sample data created for this query: in red,
original tracks, in green, rebuilt tracks with inserted vertices

Nicolas
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Screen shot 2011-07-13 at 6.28.24 PM.png
Type: image/png
Size: 150215 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110713/3773eeb2/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Screen shot 2011-07-13 at 6.29.07 PM.png
Type: image/png
Size: 151162 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110713/3773eeb2/attachment-0001.png>