STMBR2Geometry/STMBR2Geography functions

Oracle has a polygon geometry type (non-standard) called an optimized rectangle. To define one all one has to do is supply it with the lower-left and upper-right coordinates. This is a much easier thing to do than having to supply 5 coordinates in anti-clockwise order.

PostGIS also has a bunch of bounding box functions that also are useful for when working with rectangular polygons or minimum bounding rectangles (MBRs).

I have been playing around a lot, recently, with grid generation in SQL Server 2008 (more on this in another post). And, in doing so, I decided to code some functions that would return SQL Server 2008 geometry and geography polygon objects given the lower-left and upper-right coordinates of a minimum bounding rectangle.

Here they are.

 USE [GISDB]  -- Change this to your database
 GO
 -- Function that operates on Geography data type.
 CREATE Function [dbo].[STMBR2Geography] (
   @p_minx  float,
   @p_miny  float,
   @p_maxx  float,
   @p_maxy  float,
   @p_srid  Int )
   returns geography
 As
 Begin
   Return geography::STGeomFromText('POLYGON((' + 
                   CONVERT(varchar(100), CAST(@p_minx as DECIMAL(24,12))) + ' ' + CONVERT(varchar(100), CAST(@p_miny as DECIMAL(24,12))) + ',' +
                   CONVERT(varchar(100), CAST(@p_maxx as DECIMAL(24,12))) + ' ' + CONVERT(varchar(100), CAST(@p_miny as DECIMAL(24,12))) + ',' +
                   CONVERT(varchar(100), CAST(@p_maxx as DECIMAL(24,12))) + ' ' + CONVERT(varchar(100), CAST(@p_maxy as DECIMAL(24,12))) + ',' +
                   CONVERT(varchar(100), CAST(@p_minx as DECIMAL(24,12))) + ' ' + CONVERT(varchar(100), CAST(@p_maxy as DECIMAL(24,12))) + ',' +
                   CONVERT(varchar(100), CAST(@p_minx as DECIMAL(24,12))) + ' ' + CONVERT(varchar(100), CAST(@p_miny as DECIMAL(24,12))) + '))',
                   @p_srid);
 End;
 GO
 -- Same function but for Geometry type
 CREATE Function [dbo].[STMBR2Geometry](
   @p_minx  float,
   @p_miny  float,
   @p_maxx  float,
   @p_maxy  float,
   @p_srid  Int )
   returns geometry
 As
 Begin
   Return geometry::STGeomFromText('POLYGON((' + 
                   CONVERT(varchar(100), CAST(@p_minx as DECIMAL(24,12))) + ' ' + CONVERT(varchar(100), CAST(@p_miny as DECIMAL(24,12))) + ',' +
                   CONVERT(varchar(100), CAST(@p_maxx as DECIMAL(24,12))) + ' ' + CONVERT(varchar(100), CAST(@p_miny as DECIMAL(24,12))) + ',' +
                   CONVERT(varchar(100), CAST(@p_maxx as DECIMAL(24,12))) + ' ' + CONVERT(varchar(100), CAST(@p_maxy as DECIMAL(24,12))) + ',' +
                   CONVERT(varchar(100), CAST(@p_minx as DECIMAL(24,12))) + ' ' + CONVERT(varchar(100), CAST(@p_maxy as DECIMAL(24,12))) + ',' +
                   CONVERT(varchar(100), CAST(@p_minx as DECIMAL(24,12))) + ' ' + CONVERT(varchar(100), CAST(@p_miny as DECIMAL(24,12))) + '))',
                   @p_srid);
 End;
 GO

Testing

Testing is pretty easy.

 -- First, let's create a simple polygon geometry
 --
 select dbo.STMBR2Geometry(0,0,100,100,0).STAsText() as geomWKT;

 -- Now, let's create a polygon with a hole
 --
 select dbo.STMBR2Geometry(0,0,100,100,0).STDifference(dbo.STMBR2Geometry(40,40,60,60,0)).STAsText() as geomWKT;

 -- Now let's create a multipolygon with a hole
 --
 select dbo.STMBR2Geometry(0,0,100,100,0).STDifference(dbo.STMBR2Geometry(40,40,60,60,0)).STUnion(dbo.STMBR2Geometry(200,200,400,400,0)).STAsText() as geomWKT;

 -- Finally, let's create a polygon with a hole using the STMBR2Geography function
 --
 select dbo.STMBR2Geography (147,-44,148,-43,4326)
           .STDifference(dbo.STMBR2Geography (147.4,-43.6,147.6,-43.2,4326)).STAsText() as geogWKT;

I hope someone finds these functions useful.