Solving STUnion rounding problem

Question

Hi,

I'm trying to join 3 LineStrings that I know connect and will form a single LineString. But STUnion thinks it is a MultiLineString, since it does some rounding error when doing the operation. I'll guess is not a real bug, but an effect of complex geometry-operations? 

I'm thinking of implementing my own STUnion for LineString, that simply appends points from the second LineString to the first LineString. Is there a better alternative?

Thanks in advance

/Tommy

Answer 1

Hi there,

You're correct in that spatial calculations are performed using floating-point arithmetic, which can introduce 'rounding' errors when converted to/from decimal coordinate values. This can sometimes have the effect of coordinates shifting slightly when returned from methods such as STUnion(). However, if the same coordinate is used to end one linestring as start another, I would expect the 'rounding error' to be the same each time, so the linestrings would still touch.... are you sure that is the reason why you're getting a multilinestring and not some other reason (for instance, do your linestrings cross?) Generally speaking, STUnion() returns the simplest geometry type that is capable of representing all the points of the combined instances.

You can implement your own method using SqlServer.Types.dll, but that will likely suffer from the same problem (since it's using the same library that SQL Server itself uses).
Can you give a simple example of the linestrings that are failing to be unioned correctly? If your linestrings are currently complex, just the start and end points will do.

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290

Answer 2

Hi!

Here's an real life example (with reduced middle points):

First line: [1] {LINESTRING (513406.314039705 6713023.73599683,.., 513371.3093917307 6713021.6532588238)}
Second Line: [2] {LINESTRING (513739.783117184 6713004.60780459,..., 513406.314039705 6713023.73599683)}

After STUnion of [1] and [2]:
[3] {LINESTRING (513739.78311920166 6713004.6078047752,.., 513371.30939483643 6713021.6532588005)}

The third line: [4] {LINESTRING (513797.28307185532 6712989.6183822583,..., 513739.783117184 6713004.60780459)}

STUnion of [3] and [4] results in:

{MULTILINESTRING ((513739.78311920166 6713004.6078052521,..., 513371.30939483643 6713021.6532592773),
(513739.78311920166 6713004.6078042984,..., 513797.28307342529 6712989.6183824539))}

If you look at the endpoints of [1] [2] and [4] you can see that the coordinates match exactly to the last decimal, but they do not after STUnion.

All three lines should form one single (curved) line |--[4]--|----[2]----|--[1]--|

> You can implement your own method using SqlServer.Types.dll, but that will likely suffer from the same problem (since it's using the same library that SQL Server itself uses).

Will this happen also if I build up the concatenated linestring with SqlGeometryBuilder?

/Tommy

 

Answer 3

Yes - you're right - in fact, the result depends on the order in which you STUnion() each element:

DECLARE @a geometry = 'LINESTRING (513406.314039705 6713023.73599683, 513371.3093917307 6713021.6532588238)';
DECLARE @b geometry = 'LINESTRING (513739.783117184 6713004.60780459, 513406.314039705 6713023.73599683)';
DECLARE @c geometry = 'LINESTRING (513797.28307185532 6712989.6183822583, 513739.783117184 6713004.60780459)';

SELECT
@a.STUnion(@b).STUnion(@c).STGeometryType(), --MultiLineString
@a.STUnion(@c).STUnion(@b).STGeometryType(), --LineString
@b.STUnion(@a).STUnion(@c).STGeometryType(), --MultiLineString
@b.STUnion(@c).STUnion(@a).STGeometryType(), --LineString
@c.STUnion(@a).STUnion(@b).STGeometryType(), --LineString
@c.STUnion(@b).STUnion(@a).STGeometryType(); --LineString

Do these linestrings represent sections of a route (or something similar)? If so, adding each point to build a linestring programatically using SqlGeometryBuilder may have a couple of advantages - not only will it probably prevent the rounding errors, but also you can maintain the "direction" of each section (STUnion() does not maintain any concept of what was the "start" or "end point" of each element - it just creates a union of all the points)

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290

Answer 4

Yes, they represent a route on a road network.

When looking at building the linestring programmatically (SqlGeometryBuilder) it seems that I have to build both lines from scratch into a new one? Or is there some method to just append points on an existing linestring?

Another alternative is to build WKT programmatically of both LineString I want to Unite. Do you know which is the fastest?

/Tommy

Answer 5

SqlGeometryBuilder will almost certainly be faster than WKT, as parsing text input always adds an overhead, and yes you need to iteratively add each point to the instance with consecutive calls to AddLine()

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290