[postgis-users] why doesn't linemerge() work?

[Martin Davis]

LineMerge does a topological line merge, rather than one determinedby 
the order of the points.  You can think of it as computing the geometry 
that would result from drawing all the geometries on paper, and then 
turning it back into a vector geometry.  Order is not considered, and if 
there are *any* discontinuities in the input lines then a 
MULTILINESTRING is created.

This is quite useful in some cases, but obviously not in yours.  In 
theory your use case is much simpler to compute.  There may be a way of 
doing it by extracting the vertices of the input geometries in order, 
and then converting them to a single linestring.  Some systems have a 
connect() aggregate function that does this, but I don't think PostGIS 
does at the moment.

On 10/18/2012 3:27 PM, Andrew Ukrainec wrote:
> I've been trying to append a new LINESTRING to an existing
> LINESTRING in a table, and this seems to be an impossible
> task.  I've searched the net for solutions, and tried all
> possible obvious approaches:
>
> ST_LineMerge(ST_Collect(geom1,geom2))
> ST_LineMerge(ST_Union(geom1,geom2))
> ...
>
> I wrote a function to use the aggregate version of the call
>
> ST_LineMerge(ST_Collect(geom))
>
> I keep getting a MULTILINESTRING() at the output rather
> than a single LINESTRING().
>
> If I run this example adapted from an example on
> the OSGeo site:
>
> SELECT ST_AsText(ST_LineMerge(ST_Collect(the_geom)))
>   FROM (SELECT 'LINESTRING(0 0, 0 1)'::geometry the_geom
>   UNION ALL
>   SELECT 'LINESTRING(1 0, 1 1)'::geometry the_geom
>   UNION ALL
>   SELECT 'LINESTRING(0 0, 1 0)'::geometry the_geom
>   UNION ALL
>   SELECT 'LINESTRING(1 1, 0 1)'::geometry the_geom) as a;
>
> I get a single LINESTRING that I hoped for,
>
>   LINESTRING(0 0, 1 0, 1 1, 0 1, 0 0)
>
> but the answer is completely counter intuitive to me.
> I expected the individual LINESTRINGs to be simply appended
> in order.  If I adjust the data in the first LINESTRING so
> that '0 1' point is now '1 0':
>
> SELECT ST_AsText(ST_LineMerge(ST_Collect(the_geom)))
>   FROM (SELECT 'LINESTRING(0 0, 1 0)'::geometry the_geom
>   UNION ALL
>   SELECT 'LINESTRING(1 0, 1 1)'::geometry the_geom
>   UNION ALL
>   SELECT 'LINESTRING(0 0,1 0)'::geometry the_geom
>   UNION ALL
>   SELECT 'LINESTRING(1 1, 0 1)'::geometry the_geom) as a;
>
> then again the operation fails and I get MULTILINESTRING()
> at the output.
>
>   MULTILINESTRING((1 0,1 1,0 1),(1 0,0 0,1 0))
>
> The PostGIS documentation only contains the mysterious
> statement in the Example section:
>
> "--If can't be merged - original MULTILINESTRING is returned"
>
> I can accept that LINEMERGE() does not work the way I imagined
> it would, but then there is no explanation of how it does work
> in the documentation.  In the case that one wants to glue
> two LINESTRING() together into a single LINESTRING() preserving
> the order of the points, can anyone suggest how this can
> be done efficiently?
>
> Thanks in advance.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2221 / Virus Database: 2441/5339 - Release Date: 10/18/12
>
>