Adding a LineString to a MultiLineString

Question

With SQLServer 2008 I'm trying to transform two LineStrings to a multiple LineStrings (preferably a MultiLineString), by splitting them up in part based on each other.

L1 |---------------|
L2      |----|

=  |----|----|-----|

Thinking in generic terms I can get the centerpart by using L1.STIntersection(L2). The two other parts I can get with L1.STSymDifference(Intersection). The two last parts are returned as a MultiLineString.

How can I add the first centerpart to this MultiLineString?

Using STUnion will result in a single merged LineString like L1. Perhaps this is the problem, MultiLineStrings that can be merged should always be merged?

Answer 1

I can't find anything functions that would accomplish this in SQL Server 2008, but I'm also relatively new to SQL Server 2008 sql functions.

When dealing with SQL Server 2008, I usually check PostGIS forums & email lists and see how it's done there (PostgreSQL-PostGIS have been at this a lot longer). Someone seems to have the opposite problem as this one: http://bit.ly/bcb0 which appears to be the result you are after.

Unfortunately, there's no ST_LineMerge (or STLineMerge), so you'll probably need to roll your own.

I'm not sure if these examples will work in all cases (and the linestrings themselves are not in sequential order):

select geometry::STGeomFromText(Replace(@g2.STSymDifference(@g1).ToString(),'))','),' + Replace(@g1.STIntersection(@g2).ToString(),'LINESTRING','') + ')'), @g1.STSrid).STAsText()

here's the same logic with actual linestring data:

DECLARE @g1 geometry;
DECLARE @g2 geometry;
DECLARE @g3 geometry;

SET @g1 = geometry::STGeomFromText('LINESTRING(0 0, 0 20)', 0);
SET @g2 = geometry::STGeomFromText('LINESTRING(0 5, 0 10)', 0);

SELECT @g1.STIntersection(@g2).ToString();

SELECT @g1.STIntersection(@g2).STAsText();

SELECT @g2.STSymDifference(@g1).ToString();

select @g1.STIntersection(@g2).ToString();


SET @g3 = geometry::STGeomFromText(Replace(@g2.STSymDifference(@g1).ToString(),'))','),' + Replace(@g1.STIntersection(@g2).ToString(),'LINESTRING','') + ')'), @g1.STSrid);

select @g3.STAsText();