Answered by:
Geography STIntersects problem finding point in polygon
Question

Hi everyone,
I'm having a bit of a problem wrapping my head around a simple Spatial query.
Here is the definition of my polygon (I am storing both as GEOGRAPHY types):
LINESTRING (146.726250178315 35.9857541214508, 146.716637141206 36.2220878985602, 147.14235735605 36.2508874376896, 147.167076594331 36.0213050336224, 146.840233332612 36.0101970954368, 146.726250178315 35.9857541214508)
And here is the definition of the point (which I KNOW is inside the polygon):
POINT (146.92393 36.07494)
I have been led to believe that STIntersects will be able to determine this for me, but my testing so far always returns a value of 0. I also tried this with two polygons (one inside the other) and got the same result.
Am in incorrect in using STIntersects for this task? The MSDN documentation is not entirely clear on it.
FURTHER INFORMATION: I am drawing/storing these polygons on the Silverlight Bing Map control and storing them in SQL2008. I've come across the following post in another thread:
When you're drawing rectangles on a Bing/Google/OpenStreetMap/InsertYourFavMapServerHere map, you're drawing onto a flat surface that shows a projected image of the earth.
If you then want to import the shape you've drawn into SQL Server 2008 (so that you can find out which points lie within the polygon you've drawn, etc.), then you have to use the geometry datatype,So should I then be using geography NOT geometry? My project uses polygons not more than 10km in diameter so I might be able to get away with it?
Thank you.
 Kearnsy
Friday, May 28, 2010 5:08 AM
Answers

Hi there,
The answer to your problem lies in the following lines: "Here is the definition of my polygon... LINESTRING(146...." < That's not a polygon
If that were a polygon, then the WKT would begin POLYGON((146.... since it begins LINESTRING then what you're doing is defining a linestring  a series of connected lines that form the outside perimeter of your polygon but does not include any of the space contained within that boundary. Therefore you wouldn't expect STIntersects() to return 1 unless the point you were testing lay on the line itself.
To help with your second question  you can use either geometry or geography (although be consistent and use the same for both your point and polygon). The quote you mention is referring to the fact that "straight lines" as drawn on Bing Maps are only "straight" when drawn on the projected surface of the map. If you were to define them using the geography datatype then they would represent great elliptic arcs between the start and end point (which is possibly more accurate for what you're trying to do).
Consider the following two polygons, both defined between exactly the same points at POLYGON((125 49, 125 30, 80 30, 80 49, 125 49)). Using the geometry datatype, you'd interpret that polygon by drawing the straight lines that connect each point on a flat plane, as follows:
But, if you use the geography datatype, then you'd use the shortest path to connect each point in the polygon on the ellipsoid surface, which would actually create the following polygon:
The thing to try to get your head around is that the geography datatype operates an an elliptical model. That means that the line (arc) of shortest distance between two geography points is not necessarily the same as the shortest length line when drawn on a map. Horizontal lines drawn on Bing Maps always follow a line of a given latitude, but that is not the case using the geography datatype.
Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290 Marked as answer by Kearnsy Friday, May 28, 2010 7:53 AM
Friday, May 28, 2010 6:14 AMAnswerer
All replies

Hi there,
The answer to your problem lies in the following lines: "Here is the definition of my polygon... LINESTRING(146...." < That's not a polygon
If that were a polygon, then the WKT would begin POLYGON((146.... since it begins LINESTRING then what you're doing is defining a linestring  a series of connected lines that form the outside perimeter of your polygon but does not include any of the space contained within that boundary. Therefore you wouldn't expect STIntersects() to return 1 unless the point you were testing lay on the line itself.
To help with your second question  you can use either geometry or geography (although be consistent and use the same for both your point and polygon). The quote you mention is referring to the fact that "straight lines" as drawn on Bing Maps are only "straight" when drawn on the projected surface of the map. If you were to define them using the geography datatype then they would represent great elliptic arcs between the start and end point (which is possibly more accurate for what you're trying to do).
Consider the following two polygons, both defined between exactly the same points at POLYGON((125 49, 125 30, 80 30, 80 49, 125 49)). Using the geometry datatype, you'd interpret that polygon by drawing the straight lines that connect each point on a flat plane, as follows:
But, if you use the geography datatype, then you'd use the shortest path to connect each point in the polygon on the ellipsoid surface, which would actually create the following polygon:
The thing to try to get your head around is that the geography datatype operates an an elliptical model. That means that the line (arc) of shortest distance between two geography points is not necessarily the same as the shortest length line when drawn on a map. Horizontal lines drawn on Bing Maps always follow a line of a given latitude, but that is not the case using the geography datatype.
Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290 Marked as answer by Kearnsy Friday, May 28, 2010 7:53 AM
Friday, May 28, 2010 6:14 AMAnswerer 
Thank you so much for a) pointing out that highly embarassing error :D, and b) for the nicely detailed explanation.
Kearnsy
Friday, May 28, 2010 7:54 AM