Compartilhar via

A Short Discussion On The ShortestLineTo() Method

  • Artigo

In a recent post to the SQL Server Spatial Forum, the subject of finding the closest point from one geometry to another resurfaced. While this was a difficult problem with SQL Server 2008, the new “Denali” CTP has introduced a method, ShortestLineTo(), which provides a solution to this problem.

Taking the geometries provided in the forum post by elangeo on Nov 16, 2010, @h and @g, I created a new geometry, @sl which contains the “shortest line” between the two geometries:

DECLARE @h GEOMETRY = 'POINT(175346.325596772 202579.692494631)';

DECLARE @g GEOMETRY = 'POLYGON((175373.37654079497 202663.47566268593, 175309.02370874584 202626.29556666315, 175328.32617276162 202595.99009463936, 175328.33046075702 202595.99284664169, 175335.22780476511 202600.4390546456, 175335.27100476623 202600.37697464228, 175335.33071676642 202600.29115064442, 175342.50012476742 202604.9433106482, 175343.06434877217 202605.3093906492, 175355.01686077565 202585.1888146326, 175355.22690878063 202584.8352146335, 175346.57487677038 202579.83649462834, 175350.90825277567 202572.54171062633, 175351.08706877381 202572.240654625, 175346.8919967711 202569.81697462127, 175345.81129277498 202569.19259062409, 175345.50646077097 202569.01646262407, 175352.43746877462 202558.13460661471, 175378.08418879658 202517.86843058839, 175378.47298879176 202517.2579985857, 175387.97513280064 202521.08609458804, 175392.46217280626 202523.75739059225, 175415.91702082008 202537.72103860229, 175429.48124483228 202545.79636660591, 175439.01698883623 202552.97799861059, 175438.9791008383 202553.04180661216, 175425.85634882748 202575.13236662746, 175373.37654079497 202663.47566268593), (175387.31574080139 202542.21063860506, 175387.92713280022 202542.56340660527,175385.82249280065 202546.25569460541, 175392.09526080638 202549.76961461082, 175391.75484480709 202550.37588660792, 175400.52566081286 202555.39905461296, 175402.95503681153 202551.16513460875, 175403.52956481278 202551.49115061015, 175408.33698881418 202543.07399860397, 175392.2850208059 202533.85300659761, 175387.31574080139 202542.21063860506))';

DECLARE @sl GEOMETRY = @h.ShortestLineTo(@g);

Here is an illustration of how the geometries appear (note that I have buffered the point, @h, to make it more visible and located it with a red arrow).

clip_image001

If I zoom in on the point, you can see the new “shortest-line-to” linestring:

image

If the resulting linestring is displayed as Well-known Text (WKT), you can observe that the ShortestLineTo() method returns two coordinates in the “from, to” order. In this case, from the point @h, to the polygon @g:

SELECT @sl.STAsText();
--Result: LINESTRING
-- (175346.325596772 202579.692494631, “from” point
-- 175346.57487677038 202579.83649462834) “to” point

Thus, the second coordinate (“to” point), 175346.57487677038 202579.83649462834, is the nearest point on the polygon @g from the point @h. If you always contstruct your queries this fashion, you can reliably use the following “method” to retrieve the “closest point”:

SELECT @h.ShortestLineTo(@g).STPointN(2);

It should be noted that the ShortestLineTo() method does NOT “snap” between the nearest vertices on the input objects. This can be illustrated with the following simple example:

DECLARE @c GEOMETRY = 'CIRCULARSTRING(1.5 1, 1.6 1.1, 1.5 2)';
DECLARE @g GEOMETRY = 'POLYGON((2.2 1, 3.2 1, 3.2 2, 2.2 2, 2.2 1))';

SELECT @g
UNION ALL
SELECT @c.ShortestLineTo(@g)

To emphasize this point (no pun intended), I’ve drawn in the defining vertices on the following illustration:

clip_image003

The resulting points on the shortest-line-to linestring are unique and not part of the input object definitions:

SELECT @c.ShortestLineTo(@g).ToString();
--Result: LINESTRING (1.740312423743285 1.5, 2.2 1.5)

The careful reader will wonder how I drew the circularstring in the above illustration since circular arcs are not supported in the Spatial results tab in SSMS currently. To do this, I converted the circularstring @c into a linestring using the new STCurveToLine() method, as follows:

DECLARE @c GEOMETRY = 'CIRCULARSTRING(1.5 1, 1.6 1.1, 1.5 2)';
DECLARE @s GEOMETRY = @c.STCurveToLine();
DECLARE @g GEOMETRY = 'POLYGON((2.2 1, 3.2 1, 3.2 2, 2.2 2, 2.2 1))';

SELECT @s
UNION ALL
SELECT @g
UNION ALL
SELECT @c.ShortestLineTo(@g)

If there are many solutions to the closest point between two geometries, the ShortestLineTo() method will pick one of them in an unpredictable way. Consider the following two polygons whose nearest points to each other result in multiple solutions:

DECLARE @g1 GEOMETRY = 'POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))';
DECLARE @g2 GEOMETRY = 'POLYGON((2.2 1, 3.2 1, 3.2 2, 2.2 2, 2.2 1))';

SELECT @g1
UNION ALL
SELECT @g2
UNION ALL
SELECT @g1.ShortestLineTo(@g2)

clip_image004

Lastly, if the distance between the two spatial objects being compared is 0 then ShortestLineTo() will return an empty geometry.

DECLARE @g1 GEOMETRY = 'POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))';
DECLARE @g2 GEOMETRY = 'POLYGON((2 1, 3 1, 3 2, 2 2, 2 1))';
DECLARE @sl GEOMETRY = @g1.ShortestLineTo(@g2);

SELECT @g1
UNION ALL
SELECT @g2
UNION ALL
SELECT @sl

clip_image005

SELECT @g1.ShortestLineTo(@g2).ToString();
--Result: LINESTRING EMPTY

To learn about other new spatial features in the “Denali” CTP1 release, please see the post: https://blogs.msdn.com/b/edkatibah/archive/2010/11/09/new-spatial-features-in-sql-server-code-named-denali-ctp1.aspx.

Technorati Tags: Technorati Tags: Spatial,SQL Server,Denali,CTP1,ShortestLineTo,STCurveToLine,Nearest Point,Closest Point

Comments

  • Anonymous
    August 29, 2012
    is this function included in the Entity Framework 5?