Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Indexing a Geography field and making SQL use the index Expand / Collapse
Author
Message
Posted Thursday, August 15, 2013 4:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 7:00 AM
Points: 118, Visits: 246
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:

Id	Street	HighWayType	Name	MaxSpeed
74253042 0xE6100000010403000000C2BE428B11444A40FA8B8F053BF4F6BFEBC726F911444A40557655FBCFF5F6BFC1AF912408444A40DC23E53801F7F6BF01000000010000000001000000FFFFFFFF0000000002 residential Loweswater Close NULL
74253031 0xE610000001040D000000E66F8CAE1C444A40FB682739160AF7BF820C78F41A444A405DA38A879C03F7BFF5BF5C8B16444A4041649126DE01F7BFE412EC6415444A40895EEBF76901F7BF718AD8710D444A40BB13477941FFF6BFEACCE26A09444A40DFC9028129FEF6BF5555C3D908444A40751772B6CAFDF6BFB0D40EDA06444A40901FE0FF7DFCF6BFEACCE26A09444A4034FB97FFEBF7F6BFC1AF912408444A40DC23E53801F7F6BFB0D40EDA06444A4097C3938213F6F6BF011764CBF2434A401F8C33E1F2F3F6BFA0AE4912DF434A40D8B90E7ADBF1F6BF01000000010000000001000000FFFFFFFF0000000002 residential Buttermere Avenue NULL
74253039 0xE61000000104030000004FC9DEF714444A40DE8F80762CFBF6BF686D09AF13444A40913C2185FCFDF6BF718AD8710D444A40BB13477941FFF6BF01000000010000000001000000FFFFFFFF0000000002 residential Keswick Close NULL
74253010 0xE6100000010404000000A95EC834E4434A40559396804EF7F6BF69ACFD9DED434A40AF601BF164F7F6BF61F7C209F1434A403F1EFAEE56F6F6BF011764CBF2434A401F8C33E1F2F3F6BF01000000010000000001000000FFFFFFFF0000000002 residential Easedale Close NULL
74253006 0xE61000000104050000005A428875F4434A40F3E7DB82A5FAF6BFC5F87A08F4434A406F87E17E1BFDF6BF943A5C06F7434A402D57E47C56FEF6BFB789496E03444A4070A422049AFEF6BFEACCE26A09444A40DFC9028129FEF6BF01000000010000000001000000FFFFFFFF0000000002 residential Kendal Close NULL
74253035 0xE610000001040A00000004B00111E2434A403A820AFD02F0F6BFA0AE4912DF434A40D8B90E7ADBF1F6BFC007AF5DDA434A40D9F39084D8F4F6BF2CBEA1F0D9434A4034FB97FFEBF7F6BF66B67581DC434A408FE55DF580F9F6BF6F66F4A3E1434A402C1D627259FBF6BF51A96D1EE2434A40243F2CE5C6FEF6BF043A387DE2434A40895EEBF76901F7BFC202F3EBE2434A40E465F2727D04F7BFEC6D3315E2434A40EAD8F7979306F7BF01000000010000000001000000FFFFFFFF0000000002 residential Langdale Drive NULL
74253037 0xE6100000010405000000043A387DE2434A40895EEBF76901F7BFDCA16131EA434A403BD109FC3C01F7BFDE9CA5BFF2434A40472EEEF5A402F7BF352A70B20D444A40E348B1EDFE02F7BFF5BF5C8B16444A4041649126DE01F7BF01000000010000000001000000FFFFFFFF0000000002 residential Burnham Rise NULL
74253013 0xE61000000104030000009C3347FBC4434A40A8BC676ECFFDF6BFCAE2FE23D3434A40F4B7F3B347FEF6BF51A96D1EE2434A40243F2CE5C6FEF6BF01000000010000000001000000FFFFFFFF0000000002 residential Skelwith Rise NULL
74253015 0xE6100000010404000000CAE2FE23D3434A40F4B7F3B347FEF6BFAE1D7BACCF434A40F883CB74F900F7BFA6684018D3434A401A390B7BDA01F7BF121F33ABD2434A40CCC86A042C03F7BF01000000010000000001000000FFFFFFFF0000000002 residential Skelwith Rise NULL
74253062 0xE61000000104030000000A2648B604444A40B8AA9102B006F7BF4CD356DB03444A403F6DF9ED9007F7BFD02D742502444A40AFAF1AF09E08F7BF01000000010000000001000000FFFFFFFF0000000002 residential NULL NULL

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.

Post #1484664
Posted Thursday, August 15, 2013 6:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 7:00 AM
Points: 118, Visits: 246
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.

Post #1484716
Posted Thursday, August 22, 2013 9:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 7, 2014 12:58 AM
Points: 32, Visits: 486
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.
Post #1487351
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse