3

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?

Wizardskills
  • 155
  • 1
  • 12

1 Answers1

2

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

AakashM
  • 62,551
  • 17
  • 151
  • 186
  • 1
    (just new to all this myself, so there may be another even easier / more explanatory blogpost out there somewhere) – AakashM Jun 26 '12 at 15:58
  • 2
    This is most odd as i would expect a diffrent exception to be thrown for this - Microsoft.SqlServer.Types.GLArgumentException: 24205 (As metioned in your linked article). I am trapping for this error before i get the code in my example and recitifying the points order if needed. This may be of use to you and myself http://webcache.googleusercontent.com/search?q=cache:TxX_WoKGZBYJ:jasonfollas.com/blog/archive/2008/11/24/spatial-determining-ring-orientation.aspx+sqlgeography+polygon+orientation&cd=1&hl=en&ct=clnk&gl=uk – Wizardskills Jun 26 '12 at 16:15
  • Hmm, so I see... that exception type is `internal` though so how are you catching it? – AakashM Jun 26 '12 at 16:25
  • I have only used SQL in the example as it was easier to show the error. In my program i have included the Microsoft.SqlServer.Types dll which exposes the same types and methods for my C# code, which is where i am getting the 24200 error. It also allows for me to trap the 24205 error and handle it, which i dont think would be possible in SQL. As such i think i will have to implement the code i linked to so a cleaner solution is found rather than trap for specific exceptions as it is evident that more then once can be thrown for the same problem. – Wizardskills Jun 26 '12 at 16:40
  • 1
    Orientation was the clue that i was missing. Thanks. – Giannis Paraskevopoulos Jun 07 '14 at 19:12
  • It worked for me when I listed the points `Counter Clockwise`. – L_7337 Jan 31 '17 at 17:33