Tip #2: Spatial Indexing and Primary Keys

For fast and efficient search and retrieval all columns containing spatial data should have a spatial index created on them.

For Oracle an index can be created on any SDO_GEOMETRY column regardless as to whether the table has a primary key or not. This is because Oracle uses an internal, unique, ROWID pseudo-column in its indexes: the ROWID links a leaf in the spatial index to the table’s actual row on disk (in the table’s tablespace).

However, SQL Server 2008 “Katmai” requires that all tables must have a primary key before a spatial index can be constructed. The primary key can be based on one or more columns in the table with those columns being of any data type.

The table I loaded for Tip #1, TAS_LGA, can have its primary key constructed as follows:

1. Make the column that will become the primary key “NOT NULL”

alter table dbo.tas_lga alter column lga_pid char(15) not null;

2. Declaratively define the primary key on our not null column lga_pid as follows:

alter table dbo.tas_lga add constraint tas_lga_pk primary key (lga_pid);

We are now in a position to create our spatial index. Microsoft’s spatial index requires you define the bounding box of the spatial index. For our TAS_LGA table this will be the complete extent of the data. This can be discovered as follows:

select min( a.geog.STEnvelope().STPointN(1).STX) as minx,
       min( a.geog.STEnvelope().STPointN(1).STY) as miny,
       max( a.geog.STEnvelope().STPointN(3).STX) as maxx,
       max( a.geog.STEnvelope().STPointN(3).STY) as maxy
  from gisdb.dbo.tas_lga a;

minx                              miny                              maxx                             maxy
143.813480017165   -43.8603741008445   148.503628803775   -39.1918359319521

Now that we know the extent of our data we can create a spatial index as follows:

CREATE SPATIAL INDEX tas_lga_geog_idx
  ON tas_lga (geog)
  USING GEOMETRY_GRID
  WITH (
BOUNDING_BOX = ( 143, -44, 149, -39),
GRIDS = (LEVEL_1 = HIGH,
 LEVEL_2 = HIGH,
 LEVEL_3 = HIGH,
 LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 16);