Top 5 Recent Articles

ARTICLES CATEGORIES

STGeometry2MBR/STGeography2MBR: Compute and return MBR ordinates

SQL Server Spatial’s STEnvelope() function returns the minimum bounding rectangle of a geometry object but as a 5 vertex polygon.

 SELECT geometry::STGeomFromText('POLYGON((2300 -700, 2800 -300, 2300 700, 2800 1100, 2300 1100, 1800 1100, 2300 400, 2300 200, 2100 100, 2500 100, 2300 -200, 1800 -300, 2300 -500, 2200 -400, 2400 -400, 2300 -700), (2300 1000, 2400  900, 2200 900, 2300 1000))',0).STEnvelope().STAsText() AS mbr;
 -- Results
 mbr
 POLYGON ((1800 -700, 2800 -700, 2800 1100, 1800 1100, 1800 -700))

To access the lower left and upper right XY ordinates requires use of the STPointN() and STX/STY methods against the relevant vertices of the polygon as follows:

 WITH MbrPoly AS (
   SELECT geometry::STGeomFromText('POLYGON((2300 -700, 2800 -300, 2300 700, 2800 1100, 2300 1100, 1800 1100, 2300 400, 2300 200, 2100 100, 2500 100, 2300 -200, 1800 -300, 2300 -500, 2200 -400, 2400 -400, 2300 -700), (2300 1000, 2400  900, 2200 900, 2300 1000))',0).STEnvelope() AS mbr
 )
 SELECT a.mbr.STPointN(1).STX AS llx,
        a.mbr.STPointN(1).STY AS lly,
        a.mbr.STPointN(3).STX AS urx,
        a.mbr.STPointN(3).STY AS ury
   FROM mbrPoly AS a;
 -- Results
 llx lly urx ury
 1800  -700  2800  1100

Which is long winded, indirect and error prone.

Why not wrap all this processing into a function? First for the geometry data type.

 USE [GISDB]  -- Change this to your database
 GO
 -- Create function
 --
 CREATE FUNCTION [dbo].[STGeometry2MBR] ( @p_geometry   geometry )
 RETURNS @TABLE TABLE
 (
   minx       FLOAT,
   miny       FLOAT,
   maxx       FLOAT,
   maxy       FLOAT
 )
 AS
 BEGIN
    IF ( @p_geometry IS NULL )
      RETURN;
    INSERT INTO @TABLE ( minx, miny, maxx, maxy )
    VALUES(@p_geometry.STEnvelope().STPointN(1).STX,
           @p_geometry.STEnvelope().STPointN(1).STY,
           @p_geometry.STEnvelope().STPointN(3).STX,
           @p_geometry.STEnvelope().STPointN(3).STY);
    RETURN;
 END;
 GO

Testing we get

 SELECT m.*
   FROM [dbo].[STGeometry2MBR](geometry::STGeomFromText('POLYGON((2300 -700, 2800 -300, 2300 700, 2800 1100, 2300 1100, 1800 1100, 2300 400, 2300 200, 2100 100, 2500 100, 2300 -200, 1800 -300, 2300 -500, 2200 -400, 2400 -400, 2300 -700), (2300 1000, 2400  900, 2200 900, 2300 1000))',0)) AS m;
 -- Results
 minx  miny  maxx  maxy
 1800  -700  2800  1100

What about geography objects? The SQL Server Spatial type system does not support the return of the Envelope (STEnvelope) of geography objects before 2012. While “rough and ready” we can still get an MBR of a geography object by a bit of casting…

 CREATE FUNCTION [dbo].[STGeography2MBR] ( @p_geography geography )
 RETURNS @TABLE TABLE
 (
   minx       FLOAT,
   miny       FLOAT,
   maxx       FLOAT,
   maxy       FLOAT
 )
 AS
 BEGIN
    DECLARE
       @v_geometry geometry = dbo.STToGeometry(@p_geography,0);
    IF ( @p_geography IS NULL )
      RETURN;
    INSERT INTO @TABLE ( minx, miny, maxx, maxy )
    VALUES(@v_geometry.STEnvelope().STPointN(1).STX,
           @v_geometry.STEnvelope().STPointN(1).STY,
           @v_geometry.STEnvelope().STPointN(3).STX,
           @v_geometry.STEnvelope().STPointN(3).STY);
    RETURN;
 END;
 GO

Testing it we get:

 SELECT m.*
   FROM [dbo].[STGeography2MBR](geography::Parse('Polygon((-10 -10, 10 -10, 10 10, -10 10,-10 -10))')) AS m;
 -- Results
 minx  miny  maxx  maxy
 -10 -10 10  10

Hope this helps someone out there.