Spatial SQL -> MS-SQL 2012. Create shortest line from point to polyline

In this example we will test new spatial method of MS-SQL 2012 .ShortestLineTo(). We will use a number of geometries stored in milano sql database.

USE milano
GO

/* ===================================================================
  [1] Create shortest line from point to polyline
  [2] Find intersection of this line with polyline
===================================================================== */

DECLARE @pnt GEOGRAPHY @line GEOGRAPHY;

-- select spatial objects
select @pnt = g from dbo.milano_pnt where id=75;
select @line = g from dbo.milano_str where id=1;


-- show map with shortest line and intersection

select id, name, g.STBuffer(8) AS geog
from dbo.milano_str
where id=1
  union all
select id, name, g.STBuffer(40) AS geog
from dbo.milano_pnt
where id=75
  union all
select 0 AS id, '' AS name,
@pnt.ShortestLineTo(@line).STBuffer(3) AS geog
  union all
select 0 AS id, '' AS name,
@pnt.ShortestLineTo(@line).STIntersection(@line).STBuffer(15) AS geog



Spatial results will look like this:




sqlexamples.info