Geography producing incorrect rows

Question

We have a table of approximately 2m UK based data points as long/lat in a table. We have converted the long/lat to a geography data type. we can then query the table for the reference numbers within a polygon drawn by google maps.

We use the following code to query the table:

DECLARE @PolygonWKT VARCHAR(MAX) =  'POLYGON((-2.8940874872629085 53.18998211235127, -2.8929395018045345 53.19014603440105, -2.892751747173492 53.1898953298359, -2.8939641056482235 53.18979890461282, -2.8940874872629085 53.18998211235127))'

DECLARE @Polygon GEOGRAPHY = geography::STGeomFromText(@PolygonWKT, 4326).MakeValid()

SELECT ref_number
FROM MyTable WITH (INDEX(SI_GeoLocation))
WHERE GeoLocation.STIntersects(@Polygon) = 1 

It works but erratically. Most of the time we get the correct number of rows but in the example above it pulls nearly all the rows (2020398 out of 2020451) from the table.

Are we doing this the correct way?

It's a small polygon based in Chester UK and the first point and last point are the same.

Answer 1

Mine is not a full answer, but rather an addition to the one provided by @siggemannen.

A simpler, and possibly more efficient way to determine whether your polygon has a ring orientation problem or not, is to use the EnvelopeAngle() method:

DECLARE @PolygonWKT VARCHAR(MAX) =  'POLYGON((-2.8940874872629085 53.18998211235127, -2.8929395018045345 53.19014603440105, -2.892751747173492 53.1898953298359, -2.8939641056482235 53.18979890461282, -2.8940874872629085 53.18998211235127))'

DECLARE @Polygon GEOGRAPHY = geography::STGeomFromText(@PolygonWKT, 4326).MakeValid();

select @Polygon.EnvelopeAngle();

-- Invert if necessary
if @Polygon.EnvelopeAngle() > 179
    set @Polygon = @Polygon.ReorientObject();

select @Polygon.EnvelopeAngle();

Unless your polygons can legitimately cover an entire hemisphere, this will work. Moreover, you can reduce the threshold even further depending on the scale of your project.

Also, make sure to check the documentation on this method. IIRC, it doesn't return values larger than 180 degrees (i.e., a full hemisphere).