locked
inconsistency when calling STIntersects directly or from in-line table function RRS feed

  • Question

  • Hi

    Has anyone seen a similar problem before?

    I have a geography value in a table. When I "STIntersects" the value with a point that does not intersect it I get a zero result. So if I use "STIntersects" as a condition in a where clause it will not return any rows. This is expected. However, if I try doing exactly the same from a table valued function, in-line or otherwise I am bizarrely returned a positive result. Interestingly, the issue does not occur if I do not split the point into its lat and long coordinates (I need to do this because the function is called outside Sql Server).

    I am using Sql Server version 10.0.2531.0 (SP1 I believe).

    Here's a sample session demonstrating the problem: -

    if object_id ( 'test_table', 'U' ) is not null 
        drop table test_table;
    go
    
    create table test_table (g geography);
    go
    
    if object_id ( 'test_function', 'IF' ) is not null 
        drop function test_function;
    go
    
    create function test_function (@lat decimal, @long decimal)
    returns table 
    as
    return 
    (
    	select g from test_table t 
    	where t.g.STIntersects(geography::Point(@lat, @long, 4326)) = 1
    )
    go
    
    declare @g geography = 'POLYGON ((1.656454389090352 52.266780020307472, 1.6094251835377871 52.069498917574407, 0.9603892483383758 51.564467147319753, 1.4497108164305761 51.437131394927832, 1.9330789750962361 51.021177994805939, 2.4610635185941 51.105841367280171, 3.3731480517947592 51.465261063656, 3.3922892113936931 51.611585052177077, 4.0884665174682588 52.0418103949152, 4.3074421218374992 52.174575012088809, 4.3672703472453822 52.269941195305343, 3.4832505528359352 52.539289564037468, 2.542233366514679 52.678993716196672, 1.845580405277736 52.633077527984021, 1.748263735926918 52.443660091840727, 1.7473874756318939 52.358615662661933, 1.656454389090352 52.266780020307472))';
    
    declare @p geography = 'POINT(4.311278 51.887409)'
    
    insert into test_table(g) values (@g);
    
    -- inline gives correct answer since the zone does not intersect
    -- returns empty table
    select g from test_table t 
    	where t.g.STIntersects(geography::Point(@p.Lat, @p.Long, 4326)) = 1
    
    -- calling the query by a function returns the geography even though it doesn't intersect
    -- returns single row!
    select * from test_function(@p.Lat, @p.Long);
    
    
    -- clear up
    drop table test_table
    drop function test_function
    


    This looks like a bug. Any ideas welcome.
    Blair
    Wednesday, January 27, 2010 1:55 PM

Answers

  • The Point() method expects two float coordinate parameters, not decimal.

    Change your function signature as follows and it should fix it:
    create function test_function (@lat float, @long float)

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by BSDZ Wednesday, January 27, 2010 8:47 PM
    Wednesday, January 27, 2010 3:48 PM
    Answerer

All replies

  • The Point() method expects two float coordinate parameters, not decimal.

    Change your function signature as follows and it should fix it:
    create function test_function (@lat float, @long float)

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by BSDZ Wednesday, January 27, 2010 8:47 PM
    Wednesday, January 27, 2010 3:48 PM
    Answerer
  • Thanks for you response. You are correct in that changing the argument types to float does seem to remove the problem. It seems the decimal values are being truncated during parsing.
    Wednesday, January 27, 2010 8:47 PM