I have a shape file that I have imported in to MSSQL as a table with a geometry column. The geometry column is essentially the outline of the UK coastline. What I have been asked to do is to be able to supply latitude/longitude and calculate the closest distance to the coast
I have established that the problem I am having is that the point I want to find the distance from to the closest edge of the shape is within the shape and therefore always returns a distance of zero. If I try a point outside the shape (at sea) I get a distance
My question is, is there a way to get SQL to return the distance from with inside? If not then is there a way to invert the shape?
Advertisement
Answer
The answer to both of your questions is an enthusiastic “yes!”. Here. We. Go.
(I)s there a way to get SQL to return the distance from with inside?
There sure is. You need a way to calculate the boundary of your polygon (which, in this case, represents the UK landmass). While there isn’t a STBoundary() method like there is with the geometry data type, we can get what we’re looking for with the STRingN() method. Specifically, we’re going to use STRingN(1) to get the first ring in the polygon (which should represent the outer boundary of the polygon). One caveat is that if you have a MULTIPOLYGON instead of a regular POLYGON (say, to represent the real world fact that mainland England and Ireland are separate landmasses), you’ll have do a little work around getting the boundary for each polygon separately with STGeometryN() and then get the first ring from each of those
If not then is there a way to invert the shape?
Fortunately, we don’t need to revert to this solution in this case (but I do applaud your thinking outside of the box!). I say “fortunately” because it doesn’t lend itself to a good/generic solution. That is, what if the point were outside of the UK? Inverting the UK polygon puts you right back in the situation you were in. Which you could dance around and do something like “get the distance to both the polygon and its inversion and take whichever number isn’t zero”. But inverting polygons is a useful technique generally (since the order in which you specify points in a shape matters, sometimes you provide them in “reverse” order, and end up with a polygon of the entire world with a UK-shaped hole in it). All of this is a long-winded way to say: the ReorientObject() method is what you’re looking for.
And now for something completely different, some code:
declare @g geography = geography::STGeomFromText(
'POLYGON ((10 11, 10 10, 11 10, 11 11, 10 11))',
4326
),
@p geography = geography::Point(10.5, 10.5, 4326);
-- get the distance from @p to the boundary of @g
select @g.RingN(1).STDistance(@p);
-- because I think it's neat, here's the
-- shortest line between @g and @p
select @p.ShortestLineTo(@g.RingN(1)).STLength();
-- for academics' sake, find the "inverse" of @g
select @g, @g.ReorientObject();