SpatialDB Advisor
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
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.


















<<Extracting geometry types from geometry/geography objects in SQL Server 2008 >>Vectorising geometry objects in SQL Server 2008