SQL Server 2012 nearest neighbor

Question

I have a very simple table structure which is using spatial data:

     CREATE TABLE Test
          (
           [ID] int not null,
           [GeoLocation] geometry not null,
     CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
     (
         [ID] ASC
     ))  on [PRIMARY]

Here it is the DDL for the spatial index:

CREATE SPATIAL INDEX [IX_GeoLocation_Geo] ON dbo.[Test]
    (
        GeoLocation
    )USING  GEOMETRY_GRID 
    WITH (BOUNDING_BOX =(-180, -90, 180, 90), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 1024, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I have the primary key on the [ID] column and a spatial index on the GeoLocation column. For the spatial index I am using tessellation schema "Geometry auto grid".

Even I have only several thousands rows in the database, the query getting the nearest neighbor is pretty slow.

    SELECT top 15 T.ID
    FROM dbo.TestT --with(index(IX_SpatialData_Geo))
          WHERE T.Geo.Filter(@region) = 1

Actually, by adding the table hint, (forcing the using of spatial index) the query's performance is going down instead of increasing the performances :)

Thanks

score 0 · Answer 1 · answered Nov 06 '13 at 17:21

0

According to BOL, your predicate is not one that's supported by spatial indexing. Try instead:

SELECT top 15 T.ID
FROM dbo.TestT --with(index(IX_SpatialData_Geo))
      WHERE T.Geo.STIntersects(@region) = 1
ORDER BY T.Geo.STDistance(@region)

Note, if you're looking for the closest, you will have to put an ORDER BY clause in there; TOP without one is arbitrary.

answered Nov 06 '13 at 17:21

Ben Thul

31,080
4
45
68

It is event worst now. I think the issue is the way I am creating the spatial index. – Bogdan MITREA Nov 07 '13 at 07:40
The filter efficiency is 0 and I don't understand why. Internal_Filter_Efficiency 0 Primary_Filter_Efficiency 0.130907186804556 – Bogdan MITREA Nov 07 '13 at 07:52
Hmm... interesting. Can you post the DDL for your table and the spatial index in your original question? I'd ask for some sample data too, but that might be pushing it... – Ben Thul Nov 07 '13 at 14:02
I just added the DDL for table&index creation. For data you can easily create a very short script. Thanks :) – Bogdan MITREA Nov 07 '13 at 16:13
The other side of this is that it's possible that your index isn't selective enough. That *would* be data dependent. Consider a non-spatial query where you have an index on a column and where your where clause could end up using that index but would end up returning a significant portion of the table. The optimizer will choose to do a table scan because it gets more efficiency out of doing the read aheads. – Ben Thul Nov 08 '13 at 13:27

SQL Server 2012 nearest neighbor

1 Answers1