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

  • 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.

    Wednesday, September 29, 2010 8:16 PM

Answers

  • 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
    Wednesday, September 29, 2010 8:36 PM
    Answerer

All replies

  • 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
    Wednesday, September 29, 2010 8:36 PM
    Answerer
  • Thank you very much. As always, you are very very helpful. I will implement this as suggested. I am sure it will work.
    Wednesday, September 29, 2010 9:00 PM
  • 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)

    Wednesday, September 29, 2010 9:41 PM