CODEWITHSUNDEEP
sqlsql-servergeometrydistance
cjp666
cjp666

Reputation: 69

MSSQL Geometry, distance from point inside shape to closest edge

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?

Upvotes: 0

Views: 2438

Answers (3)

Petter Harnes
Petter Harnes

Reputation: 1

Convert the GEOGRAPHY object to GEOMETRY, use STBoundary(), then convert back and test against the new instance witch now is a LINESTRING:

DECLARE @geographyPolygon GEOGRAPHY = [geography]::[STGeomFromText]('POLYGON ((10 11, 10 10, 11 10, 11 11, 10 11))',4326);
DECLARE @point GEOGRAPHY = [geography]::[Point](10.5, 10.5, 4326);
-- convert to border linestring
DECLARE @geometry GEOMETRY = [geometry]::[STGeomFromWKB](@geographyPolygon.[STAsBinary](), 4326).[MakeValid]();
DECLARE @geographyBorder GEOGRAPHY = [geography]::[STGeomFromWKB](@geometry.[STBoundary]().[STAsBinary](), 4326);

SELECT
    LEFT(@geographyPolygon.[ToString](), CHARINDEX('(', @geographyPolygon.[ToString]()) - 1) AS [OriginalType]
   ,LEFT(@geographyBorder.[ToString](), CHARINDEX('(', @geographyBorder.[ToString]()) - 1) AS [BorderType]
   ,@point.[STDistance](@geographyBorder) AS [Distance];

Of couse you dont have to use all the variables I've used, this is just for readability, just put all the code in one select.

Upvotes: 0

SQLWim
SQLWim

Reputation: 1

if you want the distance or shortestline of linestrings inside the 'polygon' , then its best to work with MULTILINESTRING!

declare @poly geography = geography::STGeomFromText('MULTILINESTRING((
      2.644958 50.835404,  5.660705 50.779863, 5.776061 51.156926,  5.018005 51.411171, 
      4.254455 51.363178, 3.354564 51.383649,  2.545869 51.094872, 2.644958 50.835404 ))',4326)
declare @g geography = 0xE6100000011400000060487D49400000002079120C40000000005F804940000000A04B530C40

select @g
union all
select @g.ShortestLineTo(@poly)
union all
select @poly

Upvotes: 0

Ben Thul
Ben Thul

Reputation: 32737

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();

Upvotes: 1

Related Questions