SpatialDB Advisor

MBR to geography/geometry helper 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

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Function [dbo].[MBR2GEOGRAPHY] (
  @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

CREATE Function [dbo].[MBR2GEOMETRY](
  @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.MBR2GEOMETRY(0,0,100,100,0).STAsText() as geomWKT;

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

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

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

I hope someone finds these functions useful.