locked
geography.STContains() or STIntersects() doesn't work RRS feed

  • Question

  • The following simple query doesn't work (i.e. it doesn't give the correct result).

    DECLARE @g geography;
    DECLARE @h geography;
    SET @g = geography::STPolyFromText('POLYGON((-118.06852493286132 33.82165128304198, 
    -118.01101837158202 33.85159452508453, 
    -117.95025024414062 33.82550171608736, 
    -117.96466979980468 33.77143718451128, 
    -118.0480972290039 33.770723703910946, 
    -118.06852493286132 33.82165128304198))', 4326);
    SET @h = geography::STPointFromText('POINT(-118.04363403320312 33.823219998915484)', 4326);
    SELECT @g.STContains(@h);
    

    I have plotted all these points on a Bing map and they are valid. I also know that the point lies within the polygon. However, the Select statement above returns 0 (it should return 1). I have also used the STIntersects() function with the same result. I have also used the STIsValid() function to make sure that the input is valid. Can somebody help me resolve this issue?

    I'm using SQL Server 2012 with compatibility level of 110. By the way, in my SQL Management Studio, I see red squiggle under "STContains". When I put my mouse over it, it says: "Could not find method 'STContains' for type 'Microsoft.SqlServer.Types.SqlGeography' in assembly 'Microsoft.SqlServer.Types' ".

    Thanks.


    DK

    Monday, July 15, 2013 11:26 PM

All replies

  • Hello,

    The issue may be caused by the ring orientation of the geography polygon you defined.
    According to the book online, if we use the geography data type to store the spatial instance in SQL Server 2012, 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.

    You can refer to the description of polygon ring orientation  which post by Tanoshimi in the following thread 
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/499c6a42-caa4-420b-a1cf-17b869e0e242/wkt-point-ordering-weirdness

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click  here.


    Fanny Liu
    TechNet Community Support

    Wednesday, July 17, 2013 3:57 AM
  • Thanks, Fanny! IMHO, such an important information should have been clearly documented (and even emphasized) in the SQL documentation. But anyways, may be it's my ignorance.

    So, now the question arises: how can I make sure that my user entered the polygon data with the correct ring-orientation? My users draw the polygons using Bing map and submit the data to my middle tier (Entity Framework 5) and then I submit the data to the database. Is there any way I can verify the ring-orientation before committing the data to the database (before calling db.SaveChanges() )?

    Thanks again.


    DK

    Wednesday, July 17, 2013 6:58 PM
  • If I were being picky, I'd point out that there's no such thing as "incorrect" ring orientation as far as SQL2012 is concerned, so there's no way to correct it - it's just that listing the points of a polygon in one order defines the interior of the polygon to be on one side of the line, while listing the points in the reverse order defines the interior of the polygon to lie on the other side... but how do you know which your user intended?

    If you can make some further assumptions about the areas that your users are likely to define (for example, will they always be less than a certain size in area?) then you can test to see if the resulting polygon greatly exceeds this and, if so, you can assume that the polygon has been incorrectly oriented. Then you can use ReorientObject to fix before saving to the database.


    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    Tuesday, July 30, 2013 5:48 PM
    Answerer
  • The issue was how to figure out the ring-orientation of the polygon that my user has submitted to the server. And if is not in the desired direction, then how to reverse the direction. The Entity Framework uses the DbGeography class which has limited functionality It does not provide methods to perform these tasks. After some research I figured out that I can use the SqlGeography class first, which has a lot more functionality, and then convert the object into the DBGeography object and then submit it to the database. Here is what I did:

    string wkt = "POLYGON((...........))";
    SqlGeography sqlPolygon = SqlGeography.Parse(wkt);
    if (sqlPolygon.EnvelopeAngle() >= 90) {    // user has drawn the polygon in clock-wise direction (which means the area covering the rest of the earth!)
        sqlPolygon = sqlPolygon.ReorientObject();
    }
    DbGeography efPolygon = DbGeography.FromText(sqlPolygon.ToString(), 4326);


    DK

    Tuesday, July 30, 2013 6:28 PM
  • My SSMS environment also gave me an error and underlined in red the .STContains method.  But the error was misleading.  When the geography was valid (I used the .MakeValid() method to make sure), the query executed, despite the red underline in SSMS query editor.

    If it matters, sometimes the geography methods can be case sensitive, or so I've heard.  Like .STcontains with a small 'c' might not work, but .STContains with an uppercase 'C' would.


    • Edited by baodad1 Thursday, October 12, 2017 5:54 AM Found solution
    Wednesday, October 11, 2017 11:15 PM