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.

geography/geometry to MBR helper functions

Thursday April 12 2012 at 10:50

Keywordsmbr lower left upper right x y access geometry geography sql server spatial
Summary

This article introduces two simple functions for returning the Minimum Bounding Rectangle (Mbr) of any geometry or geography object.

SQL Server Spatial’s STEnvelope() function returns the minimum bounding rectangle of a geometry object but as a 5 vertex polygon.

  1. SELECT geometry::STGeomFromText('POLYGON((2300 -700, 2800 -300, 2300 700, 2800 1100, 2300 1100, 1800 1100, 2300 400, 2300 200, 2100 100, 2500 100, 2300 -200, 1800 -300, 2300 -500, 2200 -400, 2400 -400, 2300 -700), (2300 1000, 2400  900, 2200 900, 2300 1000))',0).STEnvelope().STAsText() AS mbr;
  2. -- Results
  3. mbr
  4. POLYGON ((1800 -700, 2800 -700, 2800 1100, 1800 1100, 1800 -700))

To access the lower left and upper right XY ordinates requires use of the STPointN() and STX/STY methods against the relevant vertices of the polygon as follows:

  1. WITH MbrPoly AS (
  2.   SELECT geometry::STGeomFromText('POLYGON((2300 -700, 2800 -300, 2300 700, 2800 1100, 2300 1100, 1800 1100, 2300 400, 2300 200, 2100 100, 2500 100, 2300 -200, 1800 -300, 2300 -500, 2200 -400, 2400 -400, 2300 -700), (2300 1000, 2400  900, 2200 900, 2300 1000))',0).STEnvelope() AS mbr
  3. )
  4. SELECT a.mbr.STPointN(1).STX AS llx,
  5.        a.mbr.STPointN(1).STY AS lly,
  6.        a.mbr.STPointN(3).STX AS urx,
  7.        a.mbr.STPointN(3).STY AS ury
  8.   FROM mbrPoly AS a;
  9. -- Results
  10. llx lly urx ury
  11. 1800  -700  2800  1100

Which is long winded, indirect and error prone.

Why not wrap all this processing into a function? First for the geometry data type.

  1. USE [GISDB]  -- Change this to your database
  2. GO
  3. -- Create function
  4. --
  5. CREATE FUNCTION [dbo].[GEOMETRY2MBR] ( @p_geometry   geometry )
  6. RETURNS @TABLE TABLE
  7. (
  8.   minx       FLOAT,
  9.   miny       FLOAT,
  10.   maxx       FLOAT,
  11.   maxy       FLOAT
  12. )
  13. AS
  14. BEGIN
  15.    IF ( @p_geometry IS NULL )
  16.      RETURN;
  17.    INSERT INTO @TABLE ( minx, miny, maxx, maxy )
  18.    VALUES(@p_geometry.STEnvelope().STPointN(1).STX,
  19.           @p_geometry.STEnvelope().STPointN(1).STY,
  20.           @p_geometry.STEnvelope().STPointN(3).STX,
  21.           @p_geometry.STEnvelope().STPointN(3).STY);
  22.    RETURN;
  23. END;
  24. GO

Testing we get

  1. SELECT m.*
  2.   FROM [dbo].[GEOMETRY2MBR](geometry::STGeomFromText('POLYGON((2300 -700, 2800 -300, 2300 700, 2800 1100, 2300 1100, 1800 1100, 2300 400, 2300 200, 2100 100, 2500 100, 2300 -200, 1800 -300, 2300 -500, 2200 -400, 2400 -400, 2300 -700), (2300 1000, 2400  900, 2200 900, 2300 1000))',0)) AS m;
  3. -- Results
  4. minx  miny  maxx  maxy
  5. 1800  -700  2800  1100

What about geography objects? The SQL Server Spatial type system does not support the return of the Envelope (STEnvelope) of geography objects before 2012. While “rough and ready” we can still get an MBR of a geography object by a bit of casting…

  1. CREATE FUNCTION [dbo].[GEOGRAPHY2MBR] ( @p_geography geography )
  2. RETURNS @TABLE TABLE
  3. (
  4.   minx       FLOAT,
  5.   miny       FLOAT,
  6.   maxx       FLOAT,
  7.   maxy       FLOAT
  8. )
  9. AS
  10. BEGIN
  11.    DECLARE
  12.       @v_geometry geometry = dbo.toGeometry(@p_geography,0);
  13.    IF ( @p_geography IS NULL )
  14.      RETURN;
  15.    INSERT INTO @TABLE ( minx, miny, maxx, maxy )
  16.    VALUES(@v_geometry.STEnvelope().STPointN(1).STX,
  17.           @v_geometry.STEnvelope().STPointN(1).STY,
  18.           @v_geometry.STEnvelope().STPointN(3).STX,
  19.           @v_geometry.STEnvelope().STPointN(3).STY);
  20.    RETURN;
  21. END;
  22. GO

Testing it we get:

  1. SELECT m.*
  2.   FROM [dbo].[GEOGRAPHY2MBR](geography::Parse('Polygon((-10 -10, 10 -10, 10 10, -10 10,-10 -10))')) AS m;
  3. -- Results
  4. minx  miny  maxx  maxy
  5. -10 -10 10  10

Hope this helps someone out there.

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 [1]

A very helpful little article. Thanks.

— Rik · 30 May 2012, 23:38 · #