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.
Andy Murphy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
5
Your polygon should probably look like:
POLYGON((-2.8940874872629085 53.18998211235127, -2.8939641056482235 53.18979890461282, -2.892751747173492 53.1898953298359, -2.8929395018045345 53.19014603440105, -2.8940874872629085 53.18998211235127))
ie, in other direction.
Think that you’re walking with a long chalk stick around a commercial stand. The chalk is in your left hand. To outline the stand, you have to walk counter clockwise. If you walk clock-wise, you are outlining everything but the stand in SQL Server point of view.
The reason it matches too much, is that most inverted polygons will match everything. If it sometimes matches correctly, well, either the polygons are perhaps correctly drawn, or there’s something else going on.
You can use: GEOGRAPHY.ReorientObject()-method to “fix” simple polygons.
One idea is to do something like:
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 @geo geography = cast(@PolygonWKT as geography)
select case when @geo.STArea() > @geo.ReorientObject().STArea() THEN @geo.ReorientObject() ELSE @geo END
This checks if area of polygon is larger than reoriented one, and uses smaller of them. This is a bit untested, so i’d probably keep the backup 🙂 I think most online plotting tools have support to doing this conversion too.
You can use the following to debug things (if you’re on late enough SQL Server version):
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 @geo geography = cast(@PolygonWKT as geography)
select cast('POINT(' + CONCAT(STR(@geo.STPointN(x.Value).Long,38,19), ' ', STR(@geo.STPointN(x.Value).Lat, 38,19)) + ')' as GEOGRAPHY), x.Value
from GENERATE_SERIES(1, @geo.STNumPoints(), 1) x
Then you can use SSMS or other tools to plot the points and see the direction they’re going to:
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).