STDistance between point and polygon always returns 0 even though they are miles away

user_13708029 0
Viewed 362

I'm trying to get the distance from a point to a polygon in MSSQL using the geography type. However there are a whole bunch of polygons that always come back with a distance of 0 even though they are on opposite ends of the country.

In this example the point is in Bournemouth (South Coast of England) and the polygon is in Aberdeen (Scotland). Distance comes back as 0.

declare @point geography = 'POINT(-1.776684 50.735450)';
declare @polygon geography = 'POLYGON ((-2.092672379931 57.150644202815, -2.0919910988412 57.150551088059, -2.0921198448739 57.150289201548, -2.0927850327096 57.150379407111, -2.092672379931 57.150644202815))';
select @polygon.STDistance(@point) as distance

Version is

Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64) 
    Aug 15 2017 10:23:29 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 14393: ) (Hypervisor)
spatial geospatial sql-server sql
1 Answers

I suspect that you have what is a common problem with your polygon. Specifically, a ring orientation problem. The order in which the vertices of the polygon are defined has meaning. Polygons in SQL Server follow the "left hand rule". Meaning that if you were driving around the boundary of your polygon in vertex order, that which is to the left of your car is what's considered the interior of the polygon.

To check, what happens if you call .EnvelopeAngle() against it? If it says 180, your polygon (as you've defined it) is "the entire globe minus Aberdeen". All is not lost though! There's a simple fix - call .ReorientObject() against the polygon and it should correct the issue.

user_5682090
Related