Indexing a Geography field and making SQL use the index

Question

Post reply

Indexing a Geography field and making SQL use the index

Wayne Evans-440401

Ten Centuries

Points: 1017
More actions
August 15, 2013 at 4:10 am

#276276

The data set I'm using is UK PBF data from Openstreetmap (http://download.geofabrik.de/europe.html) and imported it into SQL with OSM2MSSQL (http://wiki.openstreetmap.org/wiki/Osm2mssql) (600MB ~ 3Hours)
I'm trying to use the Spatial Index that has been created on info.Roads
info.Roads table Layout
CREATE SPATIAL INDEX [idxInfoRoad] ON [info].[Roads]
(
[Street]
)USING GEOGRAPHY_GRID
WITH (
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = Low),
CELLS_PER_OBJECT = 64, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
It takes a while to run, so I assume it is creating the index
The contents of the Geography cells are similar to this:
DECLARE @g geography;
SET @g = (select street from info.Roads where id = 74253042);
SELECT @g.ToString();
Result:
LINESTRING (-1.4346266 52.531785400000004, -1.4350128000000002 52.5317985, -1.4353039 52.531498500000005)
I'm looking for the top n road names in the area by using the query:
DECLARE @g geography = 'POINT(-1.43499 52.53194)';
SELECT TOP(10) *
FROM info.Roads
ORDER BY street.STDistance(@g);
This runs and gets me the correct answer:
IdStreetHighWayTypeNameMaxSpeed
742530420xE6100000010403000000C2BE428B11444A40FA8B8F053BF4F6BFEBC726F911444A40557655FBCFF5F6BFC1AF912408444A40DC23E53801F7F6BF01000000010000000001000000FFFFFFFF0000000002residentialLoweswater CloseNULL
742530310xE610000001040D000000E66F8CAE1C444A40FB682739160AF7BF820C78F41A444A405DA38A879C03F7BFF5BF5C8B16444A4041649126DE01F7BFE412EC6415444A40895EEBF76901F7BF718AD8710D444A40BB13477941FFF6BFEACCE26A09444A40DFC9028129FEF6BF5555C3D908444A40751772B6CAFDF6BFB0D40EDA06444A40901FE0FF7DFCF6BFEACCE26A09444A4034FB97FFEBF7F6BFC1AF912408444A40DC23E53801F7F6BFB0D40EDA06444A4097C3938213F6F6BF011764CBF2434A401F8C33E1F2F3F6BFA0AE4912DF434A40D8B90E7ADBF1F6BF01000000010000000001000000FFFFFFFF0000000002residentialButtermere AvenueNULL
742530390xE61000000104030000004FC9DEF714444A40DE8F80762CFBF6BF686D09AF13444A40913C2185FCFDF6BF718AD8710D444A40BB13477941FFF6BF01000000010000000001000000FFFFFFFF0000000002residentialKeswick CloseNULL
742530100xE6100000010404000000A95EC834E4434A40559396804EF7F6BF69ACFD9DED434A40AF601BF164F7F6BF61F7C209F1434A403F1EFAEE56F6F6BF011764CBF2434A401F8C33E1F2F3F6BF01000000010000000001000000FFFFFFFF0000000002residentialEasedale CloseNULL
742530060xE61000000104050000005A428875F4434A40F3E7DB82A5FAF6BFC5F87A08F4434A406F87E17E1BFDF6BF943A5C06F7434A402D57E47C56FEF6BFB789496E03444A4070A422049AFEF6BFEACCE26A09444A40DFC9028129FEF6BF01000000010000000001000000FFFFFFFF0000000002residentialKendal CloseNULL
742530350xE610000001040A00000004B00111E2434A403A820AFD02F0F6BFA0AE4912DF434A40D8B90E7ADBF1F6BFC007AF5DDA434A40D9F39084D8F4F6BF2CBEA1F0D9434A4034FB97FFEBF7F6BF66B67581DC434A408FE55DF580F9F6BF6F66F4A3E1434A402C1D627259FBF6BF51A96D1EE2434A40243F2CE5C6FEF6BF043A387DE2434A40895EEBF76901F7BFC202F3EBE2434A40E465F2727D04F7BFEC6D3315E2434A40EAD8F7979306F7BF01000000010000000001000000FFFFFFFF0000000002residentialLangdale DriveNULL
742530370xE6100000010405000000043A387DE2434A40895EEBF76901F7BFDCA16131EA434A403BD109FC3C01F7BFDE9CA5BFF2434A40472EEEF5A402F7BF352A70B20D444A40E348B1EDFE02F7BFF5BF5C8B16444A4041649126DE01F7BF01000000010000000001000000FFFFFFFF0000000002residentialBurnham RiseNULL
742530130xE61000000104030000009C3347FBC4434A40A8BC676ECFFDF6BFCAE2FE23D3434A40F4B7F3B347FEF6BF51A96D1EE2434A40243F2CE5C6FEF6BF01000000010000000001000000FFFFFFFF0000000002residentialSkelwith RiseNULL
742530150xE6100000010404000000CAE2FE23D3434A40F4B7F3B347FEF6BFAE1D7BACCF434A40F883CB74F900F7BFA6684018D3434A401A390B7BDA01F7BF121F33ABD2434A40CCC86A042C03F7BF01000000010000000001000000FFFFFFFF0000000002residentialSkelwith RiseNULL
742530620xE61000000104030000000A2648B604444A40B8AA9102B006F7BF4CD356DB03444A403F6DF9ED9007F7BFD02D742502444A40AFAF1AF09E08F7BF01000000010000000001000000FFFFFFFF0000000002residentialNULLNULL
Results
, but takes over a minute to run and looking at the execution plan its not using the newly created index:
Actual Execution Plan
I am doing something wrong, but I don't know what.
Can anyone point me in the right direction
Thanks
Wayne
Did you get access denied? Great the security works.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Wayne Evans-440401 Ten Centuries Points: 1017 More actions · Answer 1

I knew it would be a numpty moment. I was missing a where clause, so trying to do a top 10 over the entire dataset(?)

DECLARE @g geography = 'POINT(-1.43499 52.53194)';

SELECT TOP(10) *

FROM info.Roads

where street.STDistance(@g) < 5000

ORDER BY street.STDistance(@g);

It now takes 0 seconds to run and the Execution plan now comes out completely different

Execution Plan

Wayne

Did you get access denied? Great the security works.

duncanburtenshaw Ten Centuries Points: 1323 More actions · Answer 2

sometimes it's worth putting in a hint to use the spatial index, i.e.

SELECT CASE WHEN (SELECT count(*) FROM table1 WITH (INDEX ([SpatialIndex])) where SHAPE.STIntersects(@center) = 1) > 0

just to ensure that the spatial index is always used.