Exegesis Spatial Data Management

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.

with
  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