Go to content Go to navigation and search

Home

Current SQL Server Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

MBR to geography/geometry helper functions

Monday September 28 2009 at 10:45

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.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment