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((
-- Point on the lower bound of the polygon
SET @point = Geography::STGeomFromText('POINT(
SELECT @region, @point,
@region.STIntersects(@point) AS [Geog],
Geometry::STGeomFromText(@region.STAsText(), 4326).STIntersects(Geometry::STGeomFromText(@point.STAsText(), 4326)) AS [Geom]