Geography::STIntersects doesn't contain point

I've got a table of Geography polygons base on a minimum & maximum lat & lon. When using STIntersects on the polygon for a point lying on the border of the polygon the value returned is false.

This is completely expected behaviour as a line joining two points on the same latitude will use the Great Circle as it's the shortest distance between the two, causing it to arc away from the equator.

There are two obvious solutions:
Use Geometry as the field's datatype, though I'd prefer not to do this as I lose the Geography functionality.
Convert the Geography to a Geometry, and do the comparison. Doing this will lose any indexing benefits.

Is there a 3rd more efficient solution that I've missed out? I think I know what the answer will be, but it'd be nice to get a different perspective.
-- Basic are covering british isles
SET @region = Geography::STGeomFromText('POLYGON((
-11 49,
2 49,
2 61,
-11 61,
-11 49
))', 4326);
-- Point on the lower bound of the polygon
SET @point = Geography::STGeomFromText('POINT(
-4.5 49
)', 4326);
SELECT @region, @point,
	@region.STIntersects(@point) AS [Geog],
	Geometry::STGeomFromText(@region.STAsText(), 4326).STIntersects(Geometry::STGeomFromText(@point.STAsText(), 4326)) AS [Geom]

Open in new window

LVL 15
Who is Participating?
pcelbaConnect With a Mentor Commented:
It seems you would need some special SRID for Canadian border :-)

The geometry substitution is great simplification for borders on parallels but it is more likely exception. Not so rare, of course. I would rather prefer more points (depending on accuracy required) because it is easier for coding.

The casted geometry is not possible to use everywhere. How do you implement these exceptions?
Are you trying to find the point on the lower bound that is at lat -4.5 or are you trying to determine if -4.5 49 is on the lower bound of your polygon?  You already know the answer of the latter.

To find the point at lat -4.5 on the lower bound of your polygon, intersect that lower bound at -4.5.

What is the practical purpose of what you are trying to do?
oobaylyAuthor Commented:
The latter. The practical reason for this is the following:
I have a set of polygons describing country bounds, so given a point I can tell what country it is. For something like the US / Canada border which follows the 49th parallel, a point midway along the border (49.5N 105W) will appear to be inside the the US rather than Canada.

Adding points along the boundary will reduce the likelihood of errors, but will bloat the data.

Interestingly enough, the execution plan didn't appear to change that much when comparing the Geography search to the casted Geometry search. In both cases 96% of the cost was done on a filter, so either my indexes aren't set up correctly or my concerns about losing indexing benefits are unfounded.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

oobaylyAuthor Commented:
Strange, my last post isn't there. Can you give an example of a Geometry that cannot be converted to a Geography? So far I've received no exceptions, however you did make me realise that a Polygon crossing the 180th meridian will cause issues.
Think I'll have to go with the adding points method.
I don't have exact data and/or coordinates but it seems to me some borders which looks as a line on the map (examples are in Africa or even California - Nevada border) could rather use geographical system than pure geometry. But who knows? Of course, the equator and the 180th meridian is a problem. Fortunately, it is far away from my location.

I am just starting to study this new SQL feature and trying to export it to other (not so sopfisticated) database system.
oobaylyAuthor Commented:
Made me think about why Casting a Geography to Geometry was not the way to go.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.