locked
How to Get the Bounding Rectangle of a Geography RRS feed

  • Question

  • I realise that a geography is not flat, and therefore a bounding rectangle doesn't mean what it says, but I would be happy with being able to find the max and min long and lat of a polygon contained in a geography column.

    Long and Lat only work on single points, so I can't use them like this

    SELECT MAX(geog.Long) FROM MyTable

    because it just returns NULL.

    Can anyone tell me how to do this?

    TIA

    Charles

     

    Friday, September 9, 2011 5:22 PM

Answers

  • The normal trick is to convert to geometry, work out the bounding box, then convert back to geography.

    Suppose your geography instance is like this:

    DECLARE @g geography = 'POLYGON((0 52, 2 52, 1 54, 0 52))';
    

    Then you can work out the bounding box as follows:
    -- Work out bounding box by converting to geometry via WKB, then using STEnvelope()
    DECLARE @boundingbox geometry = geometry::STGeomFromWKB(@g.STAsBinary(), @g.STSrid).STEnvelope();
    
    -- Convert result back to geography via WKB
    SELECT geography::STGeomFromWKB(@boundingbox.STAsBinary(), @boundingbox.STSrid);
    

     


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    • Marked as answer by ckl42 Friday, September 9, 2011 6:31 PM
    Friday, September 9, 2011 6:09 PM
    Answerer

All replies

  • The normal trick is to convert to geometry, work out the bounding box, then convert back to geography.

    Suppose your geography instance is like this:

    DECLARE @g geography = 'POLYGON((0 52, 2 52, 1 54, 0 52))';
    

    Then you can work out the bounding box as follows:
    -- Work out bounding box by converting to geometry via WKB, then using STEnvelope()
    DECLARE @boundingbox geometry = geometry::STGeomFromWKB(@g.STAsBinary(), @g.STSrid).STEnvelope();
    
    -- Convert result back to geography via WKB
    SELECT geography::STGeomFromWKB(@boundingbox.STAsBinary(), @boundingbox.STSrid);
    

     


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    • Marked as answer by ckl42 Friday, September 9, 2011 6:31 PM
    Friday, September 9, 2011 6:09 PM
    Answerer
  • Hi Tanoshimi

    That does exactly what I need.

    Many thanks

    Charles

     

    Friday, September 9, 2011 6:31 PM
  •  

    I don't understand your logistics. Since when has geography been flat?

    Tuesday, December 6, 2011 8:39 PM
  • @PurplePoppy - geography isn't flat - that's why you can't define a bounding rectangle directly using the geography datatype. Bounding rectangles only make sense on a flat plane, so the "trick" is simply to treat angular lat/lng coordinates from the WKB stream retrieved from the geography instance as directly projected to y/x coordinates in the geometry datatype instead.
    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, December 7, 2011 8:38 AM
    Answerer
  • I have a table "Boundary" with column "Zone" as Geography Polygons, how can I make this conversion in one SELECT statement?

    If I do like this the first step works, but I can get it right to implement the conversion back to Geography. Any ides?

    SELECT  (geometry::STGeomFromWKB(Zone.STAsBinary(), 4326).STEnvelope()) AS Boundbox
    FROM	Boundary
    

    Best regards, Thomas

    Wednesday, October 17, 2018 12:58 PM