Spatial Columns and Indexing; what do the Levels / Cells_per_Object mean and what are good setting?
Posted on 2011-09-08
I have historically treated location data the brute force way, but as the database has grown the calculations are becoming slow. I have a table containing Latitude, Longitude, and Road_Name, where the specific region has been carved up for me into a row every 50m (i.e. there is a Name, and location every 50 meters along the road).
When someone reports in, there is a lookup that takes their current Latitude/Longitude and "finds" the closest road so the name can be displayed. Very crude.
The starting point to improving this is to store the Spatial data (representing Lat/Lon), and instead of looking for a "nearest" on the Latitude, and the Longitude, to instead do a lookup against the Spatial column.
This created the need for a Spatial Index, but while creating the index I realised I had no idea what the index options meant. I settled on the index below, but would appreciate someone explaining to me the various parameters in the index, and suggesting how I decide on (specifically) the values for LEVEL, and the CELLS_PER_OBJECT.
My "Names" table contains 2,000,000 rows, and I perform the query (to find the Road Name) every 100 ms.
iKey Int IDENTITY(1,1) NOT NULL
fLatitude [decimal](11, 7)
fLongitude [decimal](11, 7)
CREATE SPATIAL INDEX [geogRd_SIdx] ON [dbo].[cRoadNames]
GRIDS =(LEVEL_1 = LOW,LEVEL_2 = MEDIUM,LEVEL_3 = HIGH,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 1024, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)