SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexing a Geography field and making SQL use the index


Indexing a Geography field and making SQL use the index

Author
Message
Wayne Evans-440401
Wayne Evans-440401
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 274
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.

Wayne Evans-440401
Wayne Evans-440401
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 274
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
duncanburtenshaw
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 652
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search