in Education by (1.8m points)
I am currently in the process of integrating Google Maps into one of our applications. One of the requirements is to store a user defined area into the database. To do this I have been using the Geography type in SQL Server 2008. While I have got it working for the most part I have come across a blocking issue.

Below I have some example code. I am populating the Geography object with a GML XML string and some instances work and some don’t and I can’t see a logical reason why they don’t.

DECLARE @geoWork GEOGRAPHY

DECLARE @geoNotWork GEOGRAPHY

DECLARE @Work NVARCHAR(MAX)

DECLARE @DoesntWork NVARCHAR(MAX)

SET @Work = '<Polygon xmlns="http://www.opengis.net/gml">' +

             '<exterior>' +

             '<LinearRing>' +

             '<posList>' +

             '61.52269494598361 -6.50390625 50.84757295365389 -36.5625 32.69486597787505 -17.40234375 46.31658418182218 23.90625 61.52269494598361 -6.50390625' +

             '</posList>' +

             '</LinearRing>' +

             '</exterior>' +

             '</Polygon>'

SET @DoesntWork = '<Polygon xmlns="http://www.opengis.net/gml">' +

                  '<exterior>' +

                  '<LinearRing>' +

                  '<posList>' +

                  '51.8591074413996 -0.8425140380859375 51.790355567911845 -0.7051849365234375 51.75381501386028 -0.8191680908203125 51.80564283054998 -0.9180450439453125 51.8591074413996 -0.8425140380859375' +

                  '</posList>' +

                  '</LinearRing>' +

                  '</exterior>' +

                  '</Polygon>'

SET @geoWork = GEOGRAPHY::GeomFromGml(@Work, 4326)

SET @geoNotWork = GEOGRAPHY::GeomFromGml(@DoesntWork, 4326)

SELECT @geoWork.AsGml()

SELECT @geoNotWork.AsGml()

The polygon that is working defines a huge area (spanning the size of several countries) whereas the one that doesn’t defines one about the size of a large town. It is only these “smaller” areas that are failing. Annoyingly this smaller one doesn’t go small enough for what I need it for. Can anyone tell me why this is happening?

JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

1 Answer

0 votes
by (1.8m points)
In your failing example, your data has the wrong orientation.

In order to be able to track the inside and outside of polygons, the SQL geography type requires polygons to have the correct ring orientation. More about this, from which:

In an ellipsoidal system, a polygon has no meaning, or is ambiguous, without an orientation. For example, does a ring around the equator describe the northern or southern hemisphere? If we use the geography data type to store the spatial instance, we must specify the orientation of the ring and accurately describe the location of the instance. The interior of the polygon in an ellipsoidal system is defined by the left-hand rule.

How to fix it:

"Fixing" Polygon Ring Orientation in SQL Server 2008 using T-SQL

Related questions

0 votes
asked Dec 23, 2021 in Education by JackTerrance (1.8m points)
...