locked
Problems with STIntersects RRS feed

  • Question

  • Why STIntersects returns null for geometry objects?

    I have next piece of code

    select SHAPE from Spatial_Table
    where SHAPE.STIntersects(
    geometry::Parse('POLYGON((-30000 -30000, 30000 -30000, 30000 30000, -30000 30000, -30000 -30000))')) = 1;

    This code doesn't work, STIntersects always returns null (not zero, but NULL).

    Why?

    I work with plain geometry, not with geography.

    Wednesday, April 14, 2010 10:00 AM

Answers

  • May you check your spatial data & used SRID with:

    select SHAPE.STSrid AS Srid

    from Spatial_Table

     


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Wednesday, April 14, 2010 12:44 PM

All replies

  • Hello,

    If I try the script below, I get the expected results: @s and @g intersects and return 1; @n and @g don't intersects and returns 0. So I guess it depends more on you spatial data, maybe?

    DECLARE @g AS geometry, @s AS geometry, @n AS geometry;

     

    SET @g = geometry::Parse('POLYGON((-30000 -30000, 30000 -30000, 30000 30000, -30000 30000, -30000 -30000))');

    SET @s = geometry::Parse('POLYGON((-20000 -20000, 20000 -20000, 20000 20000, -20000 20000, -20000 -20000))');

     

    SET @n = geometry::Parse('POLYGON((-50000 -50000, -40000 -50000, -40000 -40000, -50000 40000, -50000 -50000))');

     

    SELECT @g.STIsValid()

          ,@s.STIsValid()

     

     

    SELECT @g.STIntersects(@s) AS G_S

          ,@s.STIntersects(@g) AS S_G

          ,@n.STIntersects(@g) AS N_G

     


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Wednesday, April 14, 2010 10:30 AM
  • NULL means that no matches was found. A common mistake people make is intersecting data (geometry / geography) with different SRID's. Ensure that your SRID's are the same in your table and the specific geometry used for the bounds. Looking at your code above, if your data is stored using SRID 2000, and by using the Parse constructor for a geometry, an instance is created with an SRID 0, then resulting in no matches which is correct.

    Perhaps try the following:

    DECLARE @bounds geometry

    SET @g = geometry:STGeomFromText('POLYGON((-30000 -30000, 30000 -30000, 30000 30000, -30000 30000, -30000 -30000))', <SAME SRID AS IN TABLE>);

    SELECT Shape FROM Table WHERE Shape.STIntersects(@bounds) = 1

    If this does not work, I would assume that your bounds used above is not correct or actually intersects with any data.

    Hope this helps.

    • Proposed as answer by Olaf HelperMVP Wednesday, April 14, 2010 12:01 PM
    Wednesday, April 14, 2010 10:38 AM
  • [Olaf Helper]

    This script works on my machine too. Problem is somewhere in my data, but I don't know - where?

     

    [andrieso]

    I'm working with GEOMETRY , not GEOGRAPHY.

    For GEOMETRY objects SRID always is 0 - or I'm wrong?

    If my bounds dont actually intersect with any data - I should get 0 (zero) from STIntersects, not NULL.

    So my problem is in else place - but I don't know about this place [:(]

    Wednesday, April 14, 2010 11:46 AM
  • The SRID could indeed causes the problem; even it's a geometry.

    If you run the similar script below, intersect returns NULL for @n, because I used a different SRID.

    DECLARE @g AS geometry, @s AS geometry, @n AS geometry;

     

    SET @g = geometry::STGeomFromText('POLYGON((-30000 -30000, 30000 -30000, 30000 30000

                                              , -30000 30000, -30000 -30000))', 4326);

    SET @s = geometry::STGeomFromText('POLYGON((-20000 -20000, 20000 -20000, 20000 20000, -20000 20000

                                              , -20000 -20000))', 4326);

     

    SET @n = geometry::STGeomFromText('POLYGON((-50000 -50000, -40000 -50000, -40000 -40000

                                              , -50000 40000, -50000 -50000))', 4327);

     

    SELECT @g.STIntersects(@s) AS G_S

          ,@s.STIntersects(@g) AS S_G

          ,@n.STIntersects(@g) AS S_G

     


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Wednesday, April 14, 2010 12:04 PM
  • May you check your spatial data & used SRID with:

    select SHAPE.STSrid AS Srid

    from Spatial_Table

     


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Wednesday, April 14, 2010 12:44 PM
  • Is there a particular shape from your data that you would expect to intersect with the polygon?  It's hard to guess why something like this didn't work without seeing some sample data.
    Wednesday, April 14, 2010 12:51 PM
  • Thank you very much, Olaf.

    I did this query and realized that SRID in spatial table is 3, not 0.

    I added SRID = 3 into my query and got data I needed.

     

    I thought that for geometry SRID always is 0, but I was wrong.

     

    With best regards, Andrey Alifanov.

    Thursday, April 15, 2010 3:38 AM