SpatialDB Advisor: MBR to geography/geometry helper functions

MBR to geography/geometry helper functions

· Sep 28, 09:45 pm by Simon Greener

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;

geomWKT
POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0))

-- 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;

geomWKT
POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0), (40 40, 40 60, 60 60, 60 40, 40 40))

-- 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;

geomWKT
MULTIPOLYGON (((200 200, 400 200, 400 400, 200 400, 200 200)), ((0 0, 100 0, 100 100, 0 100, 0 0), (40 40, 40 60, 60 60, 60 40, 40 40)))

-- 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;

geogWKT
POLYGON ((147 -44, 148 -44, 148 -43, 147 -43, 147 -44), (147.40000000596046 -43.599999994039536, 147.40000000596046 -43.200000002980232, 147.59999999403954 -43.200000002980232, 147.59999999403954 -43.599999994039536, 147.40000000596046 -43.599999994039536))

I hope someone finds these functions useful.