Answered by:
How to Get the Bounding Rectangle of a Geography
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 PMAnswerer
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 PMAnswerer 
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 AMAnswerer 
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