With the spatial features of SQL 2008, how can I build a LINESTRING representing a line between two (or more) POINT instances?

Question

Define a pair of points as follows:

declare @p1 geography, @p2 geography
set @p1 = 'POINT(1 2)'
set @p2 = 'POINT(6 8)'

Now I would like to get the shortest line between these two points. What function can I use to get this string? (i.e. it should output LINESTRING (1 2, 6 8) or LINESTRING (6 8, 1 2))

I know I can do this by formatting the points as WKT, manipulating the lines a bit, and then parsing it, but that seems ridiculous. Of course, is there any way to build a linear line directly from a series of points?

(With the “geometry” types, I can use @ p2.STUnion (@ p1) .STConvexHull (), but there is no STConvexHull () for the geography type.

Answer 1

There are two ways to do this in T-SQL:

declare @p1 geography = 'POINT(1 2)', @p2 geography = 'POINT(6 8)';

-- using geometry
SELECT geography::Parse(geometry::Parse(@p2.STUnion(@p1).ToString()).STConvexHull().ToString())

-- using lat, long methods
SELECT geography::Parse('LINESTRING('+str(@p1.Long)+' '+str(@p1.Lat)+','+str(@p2.Long)+' '+str(@p2.Lat)+')')