exeGesIS Spatial Data Management


Calculating the extent of the geometry in a SQL Server table

Andy Brewer passed me this, adapted from http://barendgehrels.blogspot.co.uk/2011/04/extent-of-sql-server-spatial-table.html

This is a useful script, using CTEs (common table expressions) to calculate the extent of the geometry in a SQL Server table. Replace “geom” with the name of your geometry column and “MyTable” with the name of your table.

Note that this script depends on STEnvelope returning a rectangular extent with its first point at the SW corner and its third in the NE corner. Tests from the author of the blog above confirm the SQL Server currently does this.

  cte_envelope as
    select geom.STEnvelope() as envelope from MyTable
  cte_corner as
    select envelope.STPointN(1) as point from cte_envelope
    union all
    select envelope.STPointN(3) from cte_envelope
select min(point.STX) as min_x, min(point.STY) as min_y, max(point.STX) as max_x, max(point.STY) as max_y
from cte_corner