[postgis-users] Merging points in right order (Nicolas Ribot)

[George Washington]

Thank you Nicolas, this seems to be exactly what I was looking for, but I am having some problems with parts of it.
1) the inner SELECT works perfectly, no problem at all getting the UNION to work
2) the outer SELECT gave me 3  problems (I first created an empty table called track_point with id (int), ordre(int), geom(geometry)):

it didn't like with track_points (id,ordre,geom), so I change it to with track_points (id,locus,geom) and the error disappeared (but that doesn't mean I did the right thing!)it gave me an st_line_locate_point error because my track line was a multilinestring, not a linestring, so I added ST_Dump around it and the error went away eg: st_line_locate_point((st_dump(t2.geometry)).geom, t.geom) as locus, t.geom
it now all works but my new track is not a line but a series of linestrings, each with one point. A weird result which I suspect is due to my adding ST_Dump but don't know why...
Nothing I am trying seems to work, no doubt because of my beginners' knowledge of postgis.
Do you have any suggestions.? I can see this should work but I must be doing something very wrong. And can I also ask you what software you use to visualise the query (in your two attachments). (I am using openJump now but it crashes with index out of bounds exception, running under win 7)
Many thanks for your help, this has been really helpful.
G.


> 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://postgis.refractions.net/pipermail/postgis-users/attachments/20110713/3773eeb2/attachment-0002.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://postgis.refractions.net/pipermail/postgis-users/attachments/20110713/3773eeb2/attachment-0003.png>
> 
**************************************
 		 	   		  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20101014/22a1a94e/attachment.html>