How to Determine the Bounding Box of a Spatial Index in SQL Server

In SQL Server you can associate an object with a region by invoking the STIntersects() function against a geometry or geography column, as it would be a traditional join:

DECLARE @g geometry = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0)
DECLARE @h geometry = geometry::STGeomFromText('POINT(1 1)', 0)
SELECT @g.STIntersects(@h)

This method allows the spatial location of objects using a standard SQL query: we're basically asking SQL Server which polygons our objects/points fall in.

Tracking a large population of objects and especially if they're moving, however, shows a really poor query performance. Is there any method to increase this geometrical logic performance?

According to the Microsoft online documentation, we can make use of a spatial index - a standard B-tree structure, which decomposes the 2-dimensional spatial data into a linear, hierarchial grid. By means of a spatial index, SQL Server can then internally perform a spatial calculation using simple and fast integer arithmetic.

A spatial index decomposes the 2-dimensional spatial data into a linear, hierarchial grid.

The syntax is pretty much trivial:

create spatial index
    six_tb_Dim_Geographie_Polygon
on
    dbo.tb_Dim_Geographie(Polygon)
using GEOMETRY_GRID
with
(
    BOUNDING_BOX =(xmin=9.53074889950784, ymin=46.3723050741545,xmax=17.1607732090805, ymax=49.0205207370626)

    GRIDS = (LOW, LOW, MEDIUM, HIGH),
    CELLS_PER_OBJECT = 64,
    PAD_INDEX  = ON
);

Since geometry data can -teoretically- occupy an infinte plane, the spatial index requires a rectangular bounding box, i.e. the coordinates of the x/y coordinates of the lower-left/upper-right corners of the entire geometrical structure. How to calculate them? Here is a simple query:

; with
x
as
(
   select
     geom = geometry::EnvelopeAggregate(g.Polygon)
   from
     dbo.tb_Dim_Geography g
)
   select
   xMin = x.geom.STPointN(1).STX
   , yMin = x.geom.STPointN(1).STY
   , xMax = x.geom.STPointN(3).STX
   , yMax = x.geom.STPointN(3).STY
  from
   x
;

Whereby the '1' point is the lower-left corner and '3' is the upper-right one; 'dbo.tb_Dim_Geography' could be the geographical dimension of your data warehouse, or any table containing the geographical structure in a normalised environment.