How to get the max and min lattitude and longitude of a polygon

Question

I have been looking at the geography datatype functions available and not sure how I can go about getting the max and min lat and long values of a polygon. I can extract all lat and longs and I can do my own math to figure it out but I would think there is an easy way to do this using the functionality available. Is anyone aware of any? Any help/hint would be appreciated.

Answer 1

One method would be to convert to the geometry datatype, calculate the envelope of the polygon, and then retrieve the first (bottom left) point and the third (top right) point. Something like this:

-- Start with a geography polygon
DECLARE @geog geography;
SET @geog = geography::STPolyFromText('POLYGON ((15.309 41.110, 15.308 41.126, 15.295 41.131, 15.262 41.108, 15.298 41.093, 15.315 41.104, 15.309 41.110))', 4326);

-- Create the equivalent geometry envelope
DECLARE @geomenvelope geometry;
SET @geomenvelope = geometry::STGeomFromWKB(@geog.STAsBinary(), @geog.STSrid).STEnvelope()

-- Select the coordinates of the corner points
SELECT
 @geomenvelope.STPointN(1).STX AS minlong,
 @geomenvelope.STPointN(1).STY AS minlat,
 @geomenvelope.STPointN(3).STX AS maxlong,
 @geomenvelope.STPointN(3).STY AS maxlat;

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290

Answer 2

Thank you very much. As always, you are very very helpful. I will implement this as suggested. I am sure it will work.

Answer 3

One thing to be wary of is the need to sometimes "densify" polygons with long sides to properly reflect the possibility of "curves" (great circle routes) between points of geography objects.

The script below shows visually what I am talking about.  The true max latitude in this case would be something greater than that shown by the geometry envelope, probably something close to 53 degrees.

-- GEOGRAPHY, watch the "curves" of long sides of polygons

Declare

 

@RectGeog geography = geography::STGeomFromText('POLYGON ((-125 49, -125 25, -66 25, -66 49, -125 49))', 4326)

Declare

 

@WinnepegGeog geography = geography::Point(49.9,-97.14,4326)

select

 

@RectGeog

UNION

 

ALL

select

 

@WinnepegGeog.STBuffer(20000) -- applying buffer to make it more visible in spatial results window

select

 

@RectGeog.STIntersects(@WinnepegGeog)

-- returns a 1

-- GEOMETRY, see the "straight" sides of the polygon

Declare

 

@RectGeom geometry = geometry::STGeomFromText('POLYGON ((-125 49, -125 25, -66 25, -66 49, -125 49))', 4326)

Declare

 

@WinnepegGeom geometry = geometry::Point(-97.14,49.9,4326)

select

 

@RectGeom

UNION

 

ALL

select

 

@WinnepegGeom.STBuffer(.5) -- applying buffer to make it more visible in spatial results window

select

 

@RectGeom.STIntersects(@WinnepegGeom)

-- returns a 0

-- see how the converted objects do not have the curves

Declare

 

@RectConverted geometry = geometry::STGeomFromWKB(@RectGeog.STAsBinary(), @RectGeog.STSrid)

Declare

 

@WinnepegConverted geometry = geometry::STGeomFromWKB(@WinnepegGeog.STAsBinary(), @WinnepegGeog.STSrid)

select

 

@RectConverted

UNION

 

ALL

select

 

@WinnepegConverted.STBuffer(.5) -- applying buffer to make it more visible in spatial results window

select

 

@RectConverted.STIntersects(@WinnepegConverted)