none
'geography::Point' failed because parameter 1 is not allowed to be null

    Question

  • I have the following query imbedded in an SSIS package

    SELECT name, geography::Point(STR(Shape.EnvelopeCenter().Lat, 18, 9), STR(Shape.EnvelopeCenter().Long, 18, 9), 4326) AS SHAPE
    FROM PolygonLayer

    I get the following error when running the query 

    'geography::Point' failed because parameter 1 is not allowed to be null

    I add the following where statement to the above query

    where SHAPE not null

    But i still get the same error. This is because the select statement is generating the error before the syntax reaches the where statement. How do I get this query to ignore the error and proceed with the selection?

    Thanks in advance.




    Scott

    Wednesday, February 12, 2014 6:21 PM

Answers

All replies

  • Hello,

    The "Lat" and "Long" parameter of the Point ( Lat, Long, SRID ) should be float. But STR convert numeric data to character data and return varchar.
    You can try to use the following statement:

    SELECT name, geography::STPointFromText('POINT('+STR(Shape.EnvelopeCenter().Lat, 18, 9)+ ' '+
    STR(Shape.EnvelopeCenter().Long, 18, 9) +')', 4326) AS SHAPE
     FROM PolygonLayer
    Regards,
    Fanny Liu

    Fanny Liu
    TechNet Community Support

    Thursday, February 13, 2014 6:29 AM
  • That query produces another error, which i am unable to solve. I understand the error message says i have lat values outside -90 & 90 degrees, which may be the case. I tried a where statement to ignore those values but I couldnt get that to work either.
    Thanks in advance.
    Msg 6522, Level 16, State 1, Line 1
    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
    System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.
    System.FormatException: 
       at Microsoft.SqlServer.Types.GeographyValidator.ValidatePoint(Double x, Double y, Nullable`1 z, Nullable`1 m)
       at Microsoft.SqlServer.Types.Validator.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
       at Microsoft.SqlServer.Types.ForwardingGeoDataSink.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
       at Microsoft.SqlServer.Types.CoordinateReversingGeoDataSink.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
       at Microsoft.SqlServer.Types.OpenGisWktReader.ParsePointText(Boolean parseParentheses)
       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)

    Scott

    Friday, February 14, 2014 9:58 PM
  • Geography::STPointFromText is an OpenGIS standard function which takes Long, Lat (i.e. X, Y) in the specification. Just switch the order. Or use the SQL Server-specific function that you were using, Geography::Point, which takes Lat,Long,SRID as parameters.

    Cheers, Bob

    Saturday, February 15, 2014 11:07 PM