SQL Geography point inside polygon not returning true on STIntersect (but returns true using Geometry)

Question

I don't want to resort in converting my geography data to geometry just so it returns true in STIntersect.

Here is the code in SQL:

DECLARE @point GEOGRAPHY = GEOGRAPHY::Point(1, 1, 4326)
DECLARE @polygon GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 4326)

SELECT @polygon.STIntersects(@point), @point.STIntersects(@polygon)

The following returns false (0), however if I use:

DECLARE @point GEOMETRY = GEOMETRY::Point(1, 1, 4326)
DECLARE @polygon GEOMETRY = GEOMETRY::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 4326)

SELECT @polygon.STIntersects(@point), @point.STIntersects(@polygon)

It returns true, is there something I am missing? All I know is geography is 3D plane and geometry is a flat map, however I am using the earth for calculation if the point is in the polygon.

PS: It doesn't work as well with STContains, STWithin, STOverlaps

Using Microsoft SQL Server 2012

Thorvaldsen

Answer 1

This works:

DECLARE @point GEOGRAPHY = GEOGRAPHY::Point(1, 1, 4326)
DECLARE @polygon GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 4326)

SELECT @polygon.STIntersects(@point), @point.STIntersects(@polygon)

You have to be careful with the "direction" in which you describe a polygon for geography - consider a polygon defined as a circle around the equator - did you intend to specify the northern hemisphere or the southern one?

See here:

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.

Leelah

This is a nice blog entry explaining the left-hand rule: danielwertheim.se/2012/12/03/… For example: if a point is to the left of all the lines of the polygon, it does intersect the polygon. – Equator

The URL to the blog post above explaining the left-hand rule should be: danielwertheim.se/… – Anabasis

Long story short, you should list your points counter-clockwise. – Heterocyclic

@JohnGietzen "always"? or do you list them clockwise when you want "whole surface of the earth except (the sahara desert)" ? (or do you still list the sahara counterclockwise and then ask "NOT within"...) – Brisesoleil

Answer 2

You need to apply ReorientObject to interchange interior regions and exterior regions.

DECLARE @point geography  = geography::Parse('POINT (-109.81715474571 32.2371931437342)');  
DECLARE @polygon geography  = geography::Parse('multipolygon (((-127.24365234375 37.944197500754,
-80.68359375 37.944197500754,
-80.68359375 24.966140159913,
-127.24365234375 24.966140159913,
-127.24365234375 37.944197500754)))
');  
SELECT @point.STIntersects(@polygon.ReorientObject())

The below images will demonstrate the difference

#Update_2023 Adding points should be counter The interior of the polygon in an ellipsoidal system is defined by the "left-hand rule": if you imagine yourself walking along the ring of a geography Polygon, following the points in the order in which they are listed, the area on the left is being treated as the interior of the Polygon, and the area on the right as the exterior of the Polygon.

Counter-clockwise

DECLARE @point geography  = geography::Parse('POINT (-109.81715474571 32.2371931437342)');  
    DECLARE @polygon geography  = geography::Parse('multipolygon (((
    -127.24365234375 37.944197500754,
    -127.24365234375 24.966140159913,
    -80.68359375 24.966140159913,
    -80.68359375 37.944197500754,
    -127.24365234375 37.944197500754)))
    ');  
    select @polygon
    SELECT @point.STIntersects(@polygon)

Priam