Create polygon (rectangle) from min and max lat/long

Question

Given a lat/lon representing a South West point and a lat/lon representing a North East point When I create a polygon Then it should work in SQL 2008...

Here's my SQL:

   DECLARE @minX varchar(10) = N'49.871159'
    DECLARE @maxX varchar(10) = N'55.811741'
    DECLARE @minY varchar(10) = N'-6.379880'
    DECLARE @maxY varchar(10) = N'1.768960'

    DECLARE @boundingRect varchar(150)
    SET @boundingRect = 'POLYGON((' + @minX + ' '  + @minY + ', ' + @minX + ' ' + @maxY + ', ' + @maxX + ' ' + @maxY + ', ' + @maxX + ' ' + @minY + ', ' + @minX + ' ' + @minY + '))'
    SELECT GEOGRAPHY::Parse(@boundingRect)

But it is giving me the following error: "The specified input does not represent a valid geography instance because it exceeds a single hemisphere."

Can anyone tell me what I'm doing wrong? The bounding rectangle in my example should be roughly covering the UK and my assumption is that I need to specify 5 points - bottom left, top left, top right, bottom right and back to the bottom left again.

Answer 1

First off this is unrelated to you crossing the Prime Meridian in your rectangle and being in both the Eastern and Western Hemisphere. That's just a coincidence.

SQL Geography uses the left-hand rule, so that as an observer walks your ring in the order provided, the inside of the shape is always to their left. For exterior rings, this means that the ring is defined in counter-clockwise order. What your original rectangle is, by this rule, is the entire world outside of the UK, which of course is more than a hemisphere. :)

So just change the order of your points in the POLYGON statement, (also, I switched your X and Y values, it doesn't affect the validity of the statement, but it makes the map come out looking like a long/lat for the UK)

DECLARE @minY varchar(10) = N'49.871159'
DECLARE @maxY varchar(10) = N'55.811741'
DECLARE @minX varchar(10) = N'-6.379880'
DECLARE @maxX varchar(10) = N'1.768960'

DECLARE @boundingRect varchar(150)
SET @boundingRect = 'POLYGON((' + @minX + ' '  + @minY + ', ' + 
                                                   @maxX + ' ' + @minY + ', ' + 
                                                   @maxX + ' ' + @maxY + ', ' + 
                                                   @minX + ' ' + @maxY + ', ' + 
                                                   @minX + ' ' + @minY + '))'