locked
Error when using paramters in "geometry::STGeomFromText('POLYGON ..." in a stored proc RRS feed

  • Question

  • I have this statement in a stored proc:

    UPDATE if_query_addresses
    SET trade_area_geom =
    (CASE
    WHEN @tradeArea = 'Polygon' THEN
    (geometry::STGeomFromText('POLYGON((@north_long @north_lat ,@east_long @east_lat ,@south_long @south_lat ,@west_long @west_lat, @north_long @north_lat ))', 4326))
    --(geometry::STGeomFromText('POLYGON((-112.143116 33.581590 ,-112.151356 33.574439 ,-112.142944 33.567359 ,-112.134104 33.575083, -112.143116 33.581590 ))', 4326))
    END
    )
    WHERE query_address_id = @query_address_id

    When I use the commented out statement "geometry::STGeomFromText('POLYGON((-112.143116 33.581590 ...", the update works OK.
    But when I use the "geometry::STGeomFromText('POLYGON((@north_long @north_lat ..." statement I get an error.

    Here is the definition of the parameters:

    @north_lat float(8),
    @north_long float(8),
    @east_lat float(8),
    @east_long float(8),
    @south_lat float(8),
    @south_long float(8),
    @west_lat float(8),
    @west_long float(8)

    Here is the error I get:

    A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": 
    System.FormatException: 24141: A number is expected at position 20 of the input. The input has @north_long.
    System.FormatException: 
       at Microsoft.SqlServer.Types.OpenGisWktReader.RecognizeDouble()
       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.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)
       at Microsoft.SqlServer.Types.SqlGeometry.STGeomFromText(SqlChars geometryTaggedText, Int32 srid)
    .
    The statement has been terminated.

    What is wrong with using parameters in the geometry::STGeomFromText statement?

    I did check and the parameters do have values in them that are correct.

    Thanks for any ideas you may have.

    Jay

    Thursday, January 21, 2010 5:48 PM

Answers

  • You're trying to build WKT by inserting the names of variables directly into the string and expecting them to be substituted for the value of those variables - what you've ended up with is a polygon using literal coordinates of '@north_lat' etc.

    A secondary problem is that WKT needs to be a string, but all your variables are currently floats, so you need to CAST or CONVERT them to a string first. Try this:

    DECLARE @WKT nvarchar(max);
    SET @WKT = 'POLYGON((' 
     + CAST(@north_long AS varchar(32)) + ' ' + CAST(@north_lat AS varchar(32)) + ','
     + CAST(@east_long AS varchar(32)) + ' ' + CAST(@east_lat AS varchar(32)) + ','
     + CAST(@south_long AS varchar(32)) + ' ' + CAST(@south_lat AS varchar(32)) + ',' 
     + CAST(@west_long AS varchar(32)) + ' ' + CAST(@west_lat AS varchar(32)) + ',' 
     + CAST(@north_long AS varchar(32)) + ' ' + CAST(@north_lat AS varchar(32)) + '))';
    
    
    UPDATE if_query_addresses
    SET trade_area_geom =
     (CASE
     WHEN @tradeArea = 'Polygon' THEN
     (geometry::STGeomFromText(WKT, 4326))
      END
     )
    WHERE query_address_id = @query_address_id 



    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, January 21, 2010 9:50 PM
    Answerer

All replies

  • You're trying to build WKT by inserting the names of variables directly into the string and expecting them to be substituted for the value of those variables - what you've ended up with is a polygon using literal coordinates of '@north_lat' etc.

    A secondary problem is that WKT needs to be a string, but all your variables are currently floats, so you need to CAST or CONVERT them to a string first. Try this:

    DECLARE @WKT nvarchar(max);
    SET @WKT = 'POLYGON((' 
     + CAST(@north_long AS varchar(32)) + ' ' + CAST(@north_lat AS varchar(32)) + ','
     + CAST(@east_long AS varchar(32)) + ' ' + CAST(@east_lat AS varchar(32)) + ','
     + CAST(@south_long AS varchar(32)) + ' ' + CAST(@south_lat AS varchar(32)) + ',' 
     + CAST(@west_long AS varchar(32)) + ' ' + CAST(@west_lat AS varchar(32)) + ',' 
     + CAST(@north_long AS varchar(32)) + ' ' + CAST(@north_lat AS varchar(32)) + '))';
    
    
    UPDATE if_query_addresses
    SET trade_area_geom =
     (CASE
     WHEN @tradeArea = 'Polygon' THEN
     (geometry::STGeomFromText(WKT, 4326))
      END
     )
    WHERE query_address_id = @query_address_id 



    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, January 21, 2010 9:50 PM
    Answerer
  • Oh !!!  Thank you so much!

    jay
    • Marked as answer by JayViz Thursday, January 21, 2010 10:14 PM
    • Unmarked as answer by tanoshimiEditor Thursday, January 21, 2010 10:59 PM
    Thursday, January 21, 2010 10:14 PM