locked
Spatial Index doesn't work with OR operator in where clause RRS feed

  • Question

  • I am using SQL geometric types. I have a query which works fine if I have one polygon or multiple polygon with AND operator. Doesn't work and gives me the error if I have multiple Polygon with OR clause. Here are the  examples of what works and what not:

    DECLARE @PolygonOnMap0 GEOGRAPHY;

    SET @PolygonOnMap0 = GEOGRAPHY::STGeomFromText('POLYGON((-87.675781 41.864974,-87.678553 41.858781,-87.678553 41.858781,-87.678553 41.856717,-87.679246 41.854652,-87.679939 41.853104,-87.679939 41.852588,-87.679246 41.852072,-87.675088 41.852072,-87.669545 41.852072,-87.665387 41.852072,-87.661922 41.852072,-87.658457 41.852072,-87.656379 41.852072,-87.654993 41.852072,-87.654993 41.852072,-87.675781 41.864974))', 4326);

    DECLARE @PolygonOnMap1 GEOGRAPHY;

    SET @PolygonOnMap1 = GEOGRAPHY::STGeomFromText('POLYGON((-87.641134 41.873747,-87.641827 41.870651,-87.641827 41.870651,-87.643212 41.868070,-87.643905 41.866006,-87.644598 41.863426,-87.644598 41.861878,-87.644598 41.861362,-87.644598 41.860330,-87.643905 41.859813,-87.643212 41.859813,-87.642519 41.859813,-87.641134 41.859813,-87.637669 41.859813,-87.632125 41.860330,-87.626582 41.860846,-87.622424 41.861362,-87.620345 41.861362,-87.618266 41.861878,-87.616187 41.862394,-87.615494 41.862910,-87.614801 41.862910,-87.614801 41.863426,-87.614801 41.863426,-87.641134 41.873747))', 4326);

    Doesn't Works

    SELECT DISTINCT ID FROM MyIDsTable l

    WHERE

             @PolygonOnMap0.STIntersects(l.GeoLocation) = 1

    OR

             @PolygonOnMap1.STIntersects(l.GeoLocation) = 1 


    Works

    SELECT DISTINCT ID FROM MyIDsTable l

    WHERE

             @PolygonOnMap0.STIntersects(l.GeoLocation) = 1

    AND

             @PolygonOnMap1.STIntersects(l.GeoLocation) = 1 


    Works

    SELECT DISTINCT ID FROM MyIDsTable l

    WHERE

             @PolygonOnMap0.STIntersects(l.GeoLocation) = 1

    Thanks.



    Friday, April 5, 2013 10:13 PM

Answers

  • Hi Moss_Sharepoint,

    I didn’t encounter this issue before, please run the following codes to check whether we can get the desired result:

    SELECT DISTINCT ID FROM MyIDsTable l
    WHERE  @PolygonOnMap0.STIntersects(l.GeoLocation) = 1
    uinon all
    SELECT DISTINCT ID FROM MyIDsTable l
    WHERE @PolygonOnMap1.STIntersects(l.GeoLocation) = 1
    


    Allen Li
    TechNet Community Support

    Monday, April 8, 2013 5:50 AM
  • What do you mean by "Doesn't Work"? Do you get an error (what error?), or simply the wrong results? What is the structure of your MyIDsTable?

    Your two polygons are disjoint, so you wouldn't expect to get any results from the "AND" query, but this works for me:

    CREATE TABLE #MyIDsTable (ID int, GeoLocation geography, CONSTRAINT PK_ID PRIMARY KEY CLUSTERED
    ( ID ASC ));
    CREATE SPATIAL INDEX idxS ON #MyIDsTable(GeoLocation);
    
    INSERT INTO #MyIDsTable VALUES 
    (1, 'POINT(-87.62 41.866)'),
    (2, 'POINT(-87.67 41.857)'),
    (3, 'POINT(-87.64 41.866)'),
    (4, 'POINT(-87.66 41.854)');
    
    DECLARE @PolygonOnMap0 GEOGRAPHY;
    SET @PolygonOnMap0 = GEOGRAPHY::STGeomFromText('POLYGON((-87.675781 41.864974,-87.678553 41.858781,-87.678553 41.858781,-87.678553 41.856717,-87.679246 41.854652,-87.679939 41.853104,-87.679939 41.852588,-87.679246 41.852072,-87.675088 41.852072,-87.669545 41.852072,-87.665387 41.852072,-87.661922 41.852072,-87.658457 41.852072,-87.656379 41.852072,-87.654993 41.852072,-87.654993 41.852072,-87.675781 41.864974))', 4326);
    
    DECLARE @PolygonOnMap1 GEOGRAPHY;
    SET @PolygonOnMap1 = GEOGRAPHY::STGeomFromText('POLYGON((-87.641134 41.873747,-87.641827 41.870651,-87.641827 41.870651,-87.643212 41.868070,-87.643905 41.866006,-87.644598 41.863426,-87.644598 41.861878,-87.644598 41.861362,-87.644598 41.860330,-87.643905 41.859813,-87.643212 41.859813,-87.642519 41.859813,-87.641134 41.859813,-87.637669 41.859813,-87.632125 41.860330,-87.626582 41.860846,-87.622424 41.861362,-87.620345 41.861362,-87.618266 41.861878,-87.616187 41.862394,-87.615494 41.862910,-87.614801 41.862910,-87.614801 41.863426,-87.614801 41.863426,-87.641134 41.873747))', 4326);
    
    SELECT DISTINCT ID FROM #MyIDsTable l
    WHERE
             @PolygonOnMap0.STIntersects(l.GeoLocation) = 1
    OR
             @PolygonOnMap1.STIntersects(l.GeoLocation) = 1 


    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    Monday, April 8, 2013 7:19 AM
    Answerer

All replies

  • Hi Moss_Sharepoint,

    I didn’t encounter this issue before, please run the following codes to check whether we can get the desired result:

    SELECT DISTINCT ID FROM MyIDsTable l
    WHERE  @PolygonOnMap0.STIntersects(l.GeoLocation) = 1
    uinon all
    SELECT DISTINCT ID FROM MyIDsTable l
    WHERE @PolygonOnMap1.STIntersects(l.GeoLocation) = 1
    


    Allen Li
    TechNet Community Support

    Monday, April 8, 2013 5:50 AM
  • What do you mean by "Doesn't Work"? Do you get an error (what error?), or simply the wrong results? What is the structure of your MyIDsTable?

    Your two polygons are disjoint, so you wouldn't expect to get any results from the "AND" query, but this works for me:

    CREATE TABLE #MyIDsTable (ID int, GeoLocation geography, CONSTRAINT PK_ID PRIMARY KEY CLUSTERED
    ( ID ASC ));
    CREATE SPATIAL INDEX idxS ON #MyIDsTable(GeoLocation);
    
    INSERT INTO #MyIDsTable VALUES 
    (1, 'POINT(-87.62 41.866)'),
    (2, 'POINT(-87.67 41.857)'),
    (3, 'POINT(-87.64 41.866)'),
    (4, 'POINT(-87.66 41.854)');
    
    DECLARE @PolygonOnMap0 GEOGRAPHY;
    SET @PolygonOnMap0 = GEOGRAPHY::STGeomFromText('POLYGON((-87.675781 41.864974,-87.678553 41.858781,-87.678553 41.858781,-87.678553 41.856717,-87.679246 41.854652,-87.679939 41.853104,-87.679939 41.852588,-87.679246 41.852072,-87.675088 41.852072,-87.669545 41.852072,-87.665387 41.852072,-87.661922 41.852072,-87.658457 41.852072,-87.656379 41.852072,-87.654993 41.852072,-87.654993 41.852072,-87.675781 41.864974))', 4326);
    
    DECLARE @PolygonOnMap1 GEOGRAPHY;
    SET @PolygonOnMap1 = GEOGRAPHY::STGeomFromText('POLYGON((-87.641134 41.873747,-87.641827 41.870651,-87.641827 41.870651,-87.643212 41.868070,-87.643905 41.866006,-87.644598 41.863426,-87.644598 41.861878,-87.644598 41.861362,-87.644598 41.860330,-87.643905 41.859813,-87.643212 41.859813,-87.642519 41.859813,-87.641134 41.859813,-87.637669 41.859813,-87.632125 41.860330,-87.626582 41.860846,-87.622424 41.861362,-87.620345 41.861362,-87.618266 41.861878,-87.616187 41.862394,-87.615494 41.862910,-87.614801 41.862910,-87.614801 41.863426,-87.614801 41.863426,-87.641134 41.873747))', 4326);
    
    SELECT DISTINCT ID FROM #MyIDsTable l
    WHERE
             @PolygonOnMap0.STIntersects(l.GeoLocation) = 1
    OR
             @PolygonOnMap1.STIntersects(l.GeoLocation) = 1 


    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    Monday, April 8, 2013 7:19 AM
    Answerer
  • Union works and thats how we are doing it now. But OR doesnt work.
    Monday, April 8, 2013 10:59 PM
  • Yes... It doesn't throw the error but doesn't use the index in case of OR operator is been used. 
    Monday, April 8, 2013 11:00 PM
  • Hi, have you tried tanoshimi’s codes? I tested them and found both of “AND” and “OR” used “[tempdb].[dbo].[#MyIDsTable].[PK_ID]” index.

    Allen Li
    TechNet Community Support

    Friday, April 12, 2013 7:45 AM