I've setup a spatial index on a table that has 1.3 million records in it that are all geocoded. These values are stored in a geography data type column. The problem I'm having is that when I query this column that has a spatial index is is really slow still. It's taking about 20 seconds to find all of the accounts within a mile for instance.
Here is an example of a query that runs slow:
DECLARE @g Geography;
SET @g = (select ci.Geocode from CustomerInformation ci where ci.CIOI = 372658)
DECLARE @region geography = @g.STBuffer(1609.344)
Select top 100 ci.Geocode.STDistance(@g), ci.CIOI
from CustomerInformation ci
where ci.Geocode.Filter(@region) = 1
order by ci.Geocode.STDistance(@g) asc
Here is my create index statement:
CREATE SPATIAL INDEX [IX_CI_Geocode] ON [dbo].[CustomerInformation]
(
[Geocode]
)USING GEOGRAPHY_GRID
WITH (
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = LOW,LEVEL_3 = LOW,LEVEL_4 = LOW),
CELLS_PER_OBJECT = 128, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
The data is every house in a portion of a single state. So in a mile radius I expect there to be a 1000 points or more. Am I indexing this properly? Any help would be great.
Another slow query example:
DECLARE @g Geography;
SET @g = (select ci.Geocode from CustomerInformation ci where ci.CIOI = 372658)
select top(100) CIOI, (ciFinding.Geocode.STDistance(@g) / 1609.344) as Distance, ciFinding.Geocode.ToString() --ciFinding.Geocode.STDistance(@g) / 1609.344
from CustomerInformation ciFinding
where ciFinding.Geocode.STDistance(@g) is not null and ciFinding.Geocode.STDistance(@g) < 1609.344
order by ciFinding.Geocode.STDistance(@g)