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

[George Washington]

Hi Nicolas, magic things are happening, and all is working now! Thank you very much. At one stage I thought I was missing some points but then I realised that the code is very clever and only stores a point once even if it appears both as an intersection and as a track point.
Unfortunately I still cannot get OpenJump (1.4.1) to work, it crashes not when it starts up, but when it tries to display the image. It can display the inner select without problems but not the whole query, it gives me index out of bounds error.  I installed the same plugin as yours but the outcome is still the same. 
I think I'll do some more experimenting to narrow down the problem.
Regards
George


> Hi George,
> 
> Indeed if your tracks are real MULTILINESTRING objects, the query will
> be more complex.
> First, if your track data are in fact LINESTRING objects, you should
> update your table to store simple objects:
> 
> select distinct(st_numGeometries(geometry)) from track;
> 
> will give you the number of elements in your MULTI objects. If the
> result is one record with the value 1, then you can extract LINESTRING
> from MULTI:
> 
> update track set geometry = st_geometryN(geometry, 1);
> (you may have to drop the geometry type constraint if your table was
> loaded with shp2pgsql: alter table track drop constraint
> constraint_name)
> 
> Second, if your tracks are really MULTILINESTRING objects (several
> components inside the same object), the process could look like this:
> 
> (The process could for sure be made with only one query, but I will
> create a working table to ease it).
> 
> the track_line table will contain simple linestrings extracted from
> multilinestrings contained in the track table:
> 
> create table track_line as
> select id, (st_dump(geom)).path[1] as pos, (st_dump(geom)).geom as geom
> from track;
> 
> Then, work is done on the track_line table to compute and order
> intersection with contour.
> Lines are collected with st_collect using the id attribute to group
> by, in order to rebuild MULTILINESTRINGS.
> In the inner queries, interpolation between points and lines is
> controlled by both id and pos attributes to force the work on the same
> line parts.
> The original track table could be update with this query:
> 
> select id1, st_collect(geom) as geom from (
> 	select id1, st_makeline(geom) as geom from
> 	(
> 	with track_points (id, pos, ordre, geom) as (
> 	       select id, pos, (st_dumppoints(geom)).path[1],
> (st_dumppoints(geom)).geom
> 	       from track_line
> 
> 	       UNION
> 
> 	       select distinct t.id, t.pos,
> (st_dumppoints(st_intersection(t.geom, c.geometry))).path[1],
> 		       (st_dumppoints(st_intersection(t.geom, c.geometry))).geom
> 	       from track_line t, contour c
> 	       where st_intersects(t.geom, c.geometry)
> 
> 	) select t.id as id1, t.pos as pos1, t2.id as id2, t2.pos as pos2,
> st_line_locate_point(t2.geom, t.geom) as locus, t.geom
> 	from track_points t, track_line t2
> 	where t.id = t2.id and t.pos = t2.pos
> 	order by t.id, t.pos, locus
> 	) as foo
> 	group by id1, pos1
> ) as bar
> group by id1;
> 
> On the pictures: tracks and contour are MULTILINESTRINGs
> Then, lines are rebuilt as MULTI objects, with intersection points inserted.
> 
> I also use OpenJump to visualize Postgis queries, combined with the
> Larry Reeder's DBQuery plugin.
> Version is 1.4.0.2 on Mac, but I used the same version on Windows
> without problem.
> 
> When does the crash occur in OpenJump ? when launching it or when
> loading layers from Postgis ?
> 
> Regards
> Nicolas
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: Screen shot 2011-07-15 at 3.09.12 PM.png
> Type: image/png
> Size: 65500 bytes
> Desc: not available
> URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20110715/a29eae0d/attachment.png>
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: Screen shot 2011-07-15 at 3.07.03 PM.png
> Type: image/png
> Size: 69408 bytes
> Desc: not available
> URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20110715/a29eae0d/attachment-0001.png>
******
 		 	   		  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20101016/57df4978/attachment.html>