SQL Server 2008 R2 - How convert Multipoint to Line?

Question

I work with a SQL Server 2008 R2 and I need convert one column Multipoint to Line. I tried various geographic features, but without success...

For example:

declare @f varchar(max);
declare @g geography;
set @f = (SELECT replace((SUBSTRING(T.ROUTEM.ToString(),11,9999999)),'(','' ) 
          FROM dbo.TRAVELS T WHERE T.ID_TRAVEL = 74063);
set @f = (SELECT replace(@f,')', ''));
set @f = (SELECT 'LINESTRING (' + @f + ')');
set @g = geography::STLineFromText(@f,4326);
select @g;

The return from SQL Server is:

A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
System.ArgumentException: 24200: The specified input does not represent a valid geography instance.
System.ArgumentException: 
   at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)
  at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)*

If someone help me, I would be grateful.

PS: Is there a limit of points for the STLineFromText?

I could, but this question is about how convert one field geography with multipoint information to linestring. I managed to convert linestring in a field using geometric function MakeValid(), but since I need the latitude and longitude, this conversion does not suit me. What kind of information would you like to see? — tomaz lago, Jul 11 '13 at 14:00

score 0 · Answer 1 · answered Jul 11 '13 at 20:21

0

I got resolve my problem. Exists this package SQL Spatial Tools by CodePlex. On package exists the function MakeValidValidGeographicFromText().

For know more: http://sqlspatialtools.codeplex.com/wikipage?title=Current%20Contents

answered Jul 11 '13 at 20:21

tomaz lago

11
2

score 0 · Answer 2 · edited May 23 '17 at 11:50

It would be helpful to see the LINESTRING WKT you are passing to SQL Server. However, while your algorithm converting a Multipoint to a Line may work most of the time, it doesn't guarantee any order to the points, unless you know the multipoint was generated in a particular manner. Because of this, it's possible to generate a self-intersecting line string, which would cause this error.

MakeValid is fixing the problem for you by shifting the points so that the line doesn't self-cross.

Regarding your question about the maximum number of points in a LineString, refer to Sql Server 2008 geography LineString size limitations

SQL Server 2008 R2 - How convert Multipoint to Line?

2 Answers2