Sql geometry polygon direction and crossing the International Date Line (IDL)

Question

As you can see in the following, the result of the geography polygon is different from the geometry polygon. What I want is the yellow square, but what I'm getting is the green square. Is there any way to achieve this?

If I create a geography polygon it returns the expected area but with curves, which I don't want. Why is the result of my geometry polygon different from my geography polygon? Is it possible to create a geometry polygon that crosses the dateline?

I know that geography polygons should be anticlockwise. I tried both clockwise and anticlockwise direction for my geometry polygon and the result was the same. How can I set the direction of the geometry polygon?

declare @maxLat  varchar(50); 
declare @minLong varchar(50);
declare @minLat  varchar(50);
declare @maxLong varchar(50);

set @maxLat ='-17.041470974676777'
set @minLong ='107.1076781691894'
set @minLat ='-41.6281068235708'
set @maxLong='-169.2204468308106'


declare @boundingRectGeography varchar(1000)
SET @boundingRectGeography = 'POLYGON((' +   @minLong + ' '  + @minLat + ', ' +
                                        @maxLong + ' ' + @minLat + ', ' + 
                                        @maxLong + ' ' + @maxLat + ', ' + 
                                        @minLong + ' ' + @maxLat + ', ' + 
                                        @minLong + ' ' + @minLat + '))'

DECLARE @BoundsGeography AS Geography =GEOGRAPHY::STPolyFromText(@boundingRectGeography,4326)

select @BoundsGeography




DECLARE @boundingRectGeometry varchar(1000)
SET @boundingRectGeometry = 'POLYGON((' +   @minLong + ' '  + @minLat + ', ' +
                                        @maxLong + ' ' + @minLat + ', ' + 
                                        @maxLong + ' ' + @maxLat + ', ' + 
                                        @minLong + ' ' + @maxLat + ', ' + 
                                        @minLong + ' ' + @minLat + '))'

DECLARE @BoundsGeometry AS geometry =geometry::STPolyFromText(@boundingRectGeometry,4326)

select @BoundsGeometry

---

To give you some background, I have list of landmarks (latitude, longitude) that I want to load on Google Maps. Since there are too many landmarks, I cannot return all of them at once. I need to return the landmarks that are in the areas that are visible to the user, in their viewing boundary.

I'm getting north/west (maximum latitude, mininmum longitude) and south/east (minimum latitude, maximum longitude) of a Google Maps boundary and sending it to my stored procedure to return back the list of the landmarks within that boundary. However, as I explained above I have issues and I need your help.

@dotMorten kindly looked into my issue and suggested to add +360 to my maxLong. I added the following longitude before creating my polygon but it's not working in all scenarios like this following set of latitudes and longitudes:

set @maxLat ='69.00668202899128'
set @minLong ='158.5892594884939'
set @minLat ='-17.38989745726571'
set @maxLong='133.2767594884939'

set @maxLong=convert(VARCHAR(20), convert(float,@maxLong) +360 )

Answer 1

To Begin

The difference between a geometry column and a geography column, is that a geometry column is applied to a plane (flat), while a geography is applied to a spheroid (curved).

To explain how these are different: Imagine walking forward 6,225 miles, turning left, walking forward 6,225 miles, turning left, and walking forward 6,225 miles.

On a plane, you have just made a U shape. |_| On the earth, you are back where you started (or relatively close).

If you start at the north pole, walk to the equator, turn left, walk along the equator, turn left, and now are walking north, you will end up back at the north pole.

This is why the determining the distance between 2 GPS coordinates includes a bunch of math, even though distance seems like a fairly simple thing.

Projections

There are a lot of different maps of the earth. There is the one you usually see, there are the one that that are curved around the edges, and there are the ones that have weird shapes. This is because we are trying to draw the surface of a ball on a piece of paper.

Imagine pealing off the surface of a globe and trying to lie it flat. If you want it to be a rectangle, you are going to have take the top and bottom parts, and stretch them, because there just isn't enough material. This is the concept behind the Mercator projection.

Relevant XKCD

Curved Results

When you map results in SQL Server, the default projection is equirectangular. The equirectangular projection draws the earth so that latitude and longitude vary evenly throughout the map, i.e. longitude = x and latitude = y.

When viewing the entire planet, this causes some distortion. Things that are further away from the equator are more affected than things closer, and things further away from (0,0) are affected. Australia fits both of these, and as a result, its shape is distorted.

When you draw a box in that area, SQL Server will, by default, distort the shape. Different projection systems will distort the shapes you see differently. On the right side of the spatial results tab in Management Studio, there will be three drop down boxes; the last drop down box allows you to change your projection. Try flipping between the different options to see how they change.

For Your Question

A rectangular box on a map seems like a simple request, but to translate that into a shape on the surface of a sphere is actually quite complicated for all the same reasons listed above, but in reverse. On a sphere, the shape you are describing is equally distorted. This means you have two options:

1. Accept the distortion (curviness) of your rectangle.

2. Describe your rectangle using many points. The more points you use, the closer to a rectangle your shape will look.

   Example:

vs.

Direction of Points

Once again, your issue here is that you are on a sphere.
For a geography data type, a polygon is any part of the surface of the sphere. This means that it can be the entire sphere except a tiny area, or it can be that tiny area. Direction lets you decide which shape you are describing.

For a geometry data type this does not apply. While longitude=181 is the same as longitude=-179, x=181 is NOT the same as x=-179. This means that for the shape you declare to actually be a shape (rather than an infinite plane with a tiny hole), SQL Server will automatically assume you wanted the square described by those points instead, i.e. the big box. For a geometry data type to create the shape you see in geography, a multipolygon would be necessary.

However, this is simply due to the geography datum. In a geometry data type, the plane extends forever, but in a geography data type it wraps around. This means that if you used a different location for (0,0), the shape would appear whole, rather than in two pieces.

Hopefully this has cleared some stuff up.