locked
Stored Procedures with SQL Spatial datatypes/functions RRS feed

  • Question

  • Hi there -

    Quick question, has anyone else experienced problems calling (from Visual Studio) stored procedures that contain spatial data types?  If I run the stored proc below in sql server, it works fine.  If I run it from .net without the last line (with STBuffer and STIntersects) it works fine.  But running the full query below from .net returns an empty object.  Thanks for any help.

    Mike

    Declare

     

    @gridPoint Geography;

     

    Set @gridPoint = Geography::Point(@latitude, @longitude, 4326);

     

    print @gridPoint.ToString()

     

    Select SUM(TSI) from test_Portfolio_Location p

     

    where p.geogWGS.STIntersects(@gridPoint.STBuffer(@hotspotRadius))=1

    Thursday, January 28, 2010 4:58 PM

Answers

All replies

  • Okay, the problem relates to the datatype that i'm passing to my stored procedure:

    @latitude

    numeric(20,15),

    @longitude

    numeric(20,15),

    @hotspotRadius

    int


    In .net, if i just set my (latitude) parameter value to a number like 51.5 (which it says is type Struct  System.Double), then the sproc runs fine.  But if I try and put my variable (type double) in as the parameter's value, the sproc returns an empty object.  Even if i parse my double variable into System.Double the sproc fails.

    I think i'm close...

    Thursday, January 28, 2010 6:30 PM
  • The Point() method expects three parameters - latitude, longitude, and distance - all of which should be defined as floats, not numeric/int. See if that fixes it.

    Strangely enough, this exact same issue came up with another thread today: http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/981b0c79-481c-4905-9851-199e945e67b7
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by blomm Friday, January 29, 2010 10:23 AM
    Thursday, January 28, 2010 8:10 PM
    Answerer
  • Ya, i was just running into issues with datatypes, but problem solved,
    my stored procedure takes this as parameters:

    @latitude

    decimal(12,9),

    @longitude

    decimal(12,9),

    @hotspotRadius

    int

    and .net passes doubles.

    Friday, January 29, 2010 10:30 AM