locked
Polygon creation from existing Lat/Long fields RRS feed

  • Question

  • Hi Guys,

    I've spent a few hours on this and can't figure out where I'm going wrong.  

    I am trying to modify an old existing database (SQL Server 2008 R2) by adding a geography column to an existing table and using the Long/lats within this table to create the spatial object, the resulting object will be used in MapInfo.  The geography column has been added (BoundingPy_Geo) and the update query I have come up with is below:

    UPDATE DatasetWGS84Extent

          SET BoundingPy_Geo = (geography::STGeomFromText('POLYGON('

          + CAST([MinX] AS VARCHAR(50)) + ' ' + CAST([MaxY] AS VARCHAR(50)) + ','

          + CAST([MinX] AS VARCHAR(50)) + ' ' + CAST([MinY] AS VARCHAR(50)) + ','

          + CAST([MaxX] AS VARCHAR(50)) + ' ' + CAST([MinY] AS VARCHAR(50)) + ',' 

          + CAST([MaxX] AS VARCHAR(50)) + ' ' + CAST([MaxY] AS VARCHAR(50)) + ','      

          + CAST([MinX] AS VARCHAR(50)) + ' ' + CAST([MaxY] AS VARCHAR(50))  + ')', 4326))

    GO

    It fails however with the following error:

    Msg 6522, Level 16, State 1, Line 1

    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":

    System.FormatException: 24142: Expected "(" at position 9. The input has "9".

    System.FormatException:

       at Microsoft.SqlServer.Types.OpenGisWktReader.RecognizeToken(String token)

       at Microsoft.SqlServer.Types.OpenGisWktReader.ParseLineStringText()

       at Microsoft.SqlServer.Types.OpenGisWktReader.ParsePolygonText()

       at Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType type)

       at Microsoft.SqlServer.Types.OpenGisWktReader.Read(OpenGisType type, Int32 srid)

       at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)

    I can’t find the 9 referenced or figure out where position 9 is.

    Any help would be greatly appreciated.

    Cheers,
    Tony

    Wednesday, June 10, 2015 10:51 PM

Answers

  • Well-known text for polygon always begins with two left parens, e.g. POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10)) you only have one left paren. The '9' in the error message refers to the position in the WKT string 'POLYGON(' (8 characters) the missing paren. should be the 9th character.

    What I normally do for any heavy string concatenation I'm having trouble with is to concatenate the string into a variable "SET @s = ....", then PRINT the variable, then use it in the statement ...STGeomFromText(@s,4326). Easier to find that way. And if the problem is WKT format (like it was here), compare it to other WKT examples for the same spatial type.

    In addition, if you're constructing geography, remember the ring orientation order rule (http://blogs.msdn.com/b/edkatibah/archive/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx), although the "less than a hemisphere" restriction went away in SQL Server 2012. 

    Cheers, Bob


    Thursday, June 11, 2015 1:27 AM