Minimum and Maximum Points For A Series of Geometry Data Types

Question

After bringing in a series of Shapefiles into SQL Server 2008R2 we are looking to grab the min and maximum points for a series of polygons in a table.

Without aggregate functions like STExtent in SQL Server 2008R2 how can one determine the min and maximum points?

This blog post points to a series of options:
http://alastaira.wordpress.com/2011/07/26/determining-the-geographic-extent-of-spatial-features-in-a-sql-server-table/

• Option #1 : With a Cursor
• Option #2 : CLR Function
• Option #3 : CTE
• Option #4 : Persisted Envelopes

An example:

BEGIN TRAN
  CREATE TABLE #Lines
  (
    ID INT IDENTITY(1,1)
    ,Poly GEOMETRY NULL
  );

  INSERT INTO #Lines
    (Poly)
  VALUES
    (geometry::STGeomFromText('LINESTRING(0 0, 2 3)', 0));

  INSERT INTO #Lines
    (Poly)
  VALUES
    (geometry::STGeomFromText('LINESTRING(1 1, 2 4)',0));

  --How can i get the min and max x and y points?
      --(e.g. for this example Xmin = 0, Xmax = 2, Ymin = 0, Ymax = 4)


  DROP TABLE #Lines 
COMMIT

Answer 1

If you work with SQL Server 2008 use:

select MIN((<geom_col>).STEnvelope().STPointN(1).STX) as xmin,
MAX((<geom_col>).STEnvelope().STPointN(3).STX) as xmax,
MIN((<geom_col>).STEnvelope().STPointN(1).STY) as ymin,
MAX((<geom_col>).STEnvelope().STPointN(3).STY) as ymax from <your table>;

SQL Server 2012 has a new function UnionAggregate. So a alternative is:

select  geometry::UnionAggregate (<geom_col>).STEnvelope().STPointN(1).STX as xmin,
geometry::UnionAggregate (<geom_col>).STEnvelope().STPointN(3).STX as xmax,
geometry::UnionAggregate (<geom_col>).STEnvelope().STPointN(1).STY as ymin,
geometry::UnionAggregate (<geom_col>).STEnvelope().STPointN(3).STY as ymax from <your table>

Answer 2

I agree with the author of the aforementioned (and linked) blog post that persistence envelopes is a good solution given the lack of change in the data.

Below I have the edited the example to answer the question with that implementation.

BEGIN TRAN
  CREATE TABLE #Lines
  (
    ID INT IDENTITY(1,1)
    ,Poly GEOMETRY NULL
  );

 INSERT INTO #Lines
  (Poly)
 VALUES
  (geometry::STGeomFromText('LINESTRING(0 0, 2 3)', 0));

 INSERT INTO #Lines
  (Poly)
 VALUES
  (geometry::STGeomFromText('LINESTRING(1 1, 2 4)',0));


 --Using option 4 of persisted envelopes

 ALTER TABLE #Lines
   ADD
     MinX AS (CONVERT(int, Poly.STEnvelope().STPointN((1)).STX, 0)) PERSISTED,
     MinY AS (CONVERT(int, Poly.STEnvelope().STPointN((1)).STY, 0)) PERSISTED,
     MaxX AS (CONVERT(int, Poly.STEnvelope().STPointN((3)).STX, 0)) PERSISTED,
     MaxY AS (CONVERT(int, Poly.STEnvelope().STPointN((3)).STY, 0)) PERSISTED;

 SELECT
   MIN(MinX) AS [X Minimum]
   ,MIN(MinY) AS [Y Minimum]
   ,MAX(MaxX) AS [X Maximum]
   ,MAX(MaxY) AS [Y Maximum]    
 FROM #Lines

 DROP TABLE #Lines 
COMMIT