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

[Nicolas Ribot]

> 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,

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://lists.osgeo.org/pipermail/postgis-users/attachments/20101015/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://lists.osgeo.org/pipermail/postgis-users/attachments/20101015/a29eae0d/attachment-0001.png>