[UPDATE] I tried the index definition below and received the following error message:
Cannot create primary xml, selective xml or spatial index 'SI_Property' on table 'BTSOne.dbo.Properties', column 'Point', because the column is computed.
This makes sense but now I'm back to square one.
The reason I'm trying to do this? Because queries are timing out. I have indices set up for all my other queries except for the spatial ones which are the main type of query executed.
I'm a little confused on which columns to create the spatial index on. I'm concerned because some of the records are missing longitude and latitude values (default to zero) which effects the point column, the index column. At first I thought I could create the index on the point column but reading articles about the matter suggests I use multiple columns. The more I read the more confuse I become. Also, there's the question of setting the grids correctly. The rule of thumb seems to be setting them to high.
These are the relevant table columns:
[Latitude] [float] NULL CONSTRAINT [DF_Properties_Latitude] DEFAULT ((0)),
[Longitude] [float] NULL CONSTRAINT [DF_Properties_Longitude] DEFAULT ((0)),
[Point] AS ([geography]::Point([Latitude],[Longitude],[SRID])),
[SRID] [int] NULL CONSTRAINT [DF_Properties_SRID] DEFAULT ((4326)),
This is the relevant part of the stored procedure:
DECLARE @SearchPoint as geography,
@Region nvarchar(80)
SET @SearchPoint = geography::Point(@Latitude, @Longitude, 4326)
DECLARE @tempTable dbo.WorkingProperties
SELECT [PropertyId] AS "Id", ISNULL([InnCode],'NA') AS "InnCode", [UseName] AS "OfficeName", [Addr1] As "Address", [City]
, [Zip] AS "PostalCode", [CountryCode], [Brand], [BrandCode] ,[Latitude], [Longitude],
([Point].STDistance(@SearchPoint)/1000) AS "Distance",
NULL AS "ProjectType",'Properties' As "Source", [GlobalRMArea]
FROM [BTSOne].[dbo].[Properties]
WHERE [Point].STDistance(@SearchPoint) <= (@intRadiusKm * 1000)
AND OpenStatus = 'Open'
ORDER BY "Distance"
This is how I would create the index:
CREATE SPATIAL INDEX [SI_Property] ON [BTSOne].[dbo].[Properties]
(
[Point]
)USING GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 16, 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]
GO
I'm working in a very limited access environment so I don't have the luxury for trial and error too many and I don't have direct access to the sql server instance so I don't want to wear out my re-try limit :-).
Thanks!
Spatial Indexes do take a little getting used to, but once you do they're quite simple.
Firstly, Spatial Indexes can ONLY be created on Spatial columns - that is those of type GEOMETRY or GEOGRAPHY. In your instance, you have a single column "[Point]" and so this is the only column you should and can index.
When running queries that involve spatial data, the query plan generated is normally very efficient, using the Spatial Index for that part of the WHERE clause and other non-Spatial Indexes for other parts of a WHERE clause.
As for Grid levels, it can be trial and error unfortunately as ultimately it depends on your data. When you start playing around with the settings though, often I find you're only saving milliseconds - in most cases. Start as you have at HHHH with 16 cells per object. If you're not happy with the results, check the query plan to ensure it's being used and if it is, tweak it.
If you really want to understand Spatial Indexes, I recommend you check out "Pro Spatial with SQL Server 2012" by Alistair Aitchison. It really is the bible on using Spatial data in SQL and Alistair has done a fantastic job with it.