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

Question

Define a couple of points as follows:

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

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

I know I could do this by formatting the points as WKT, doing a bit of string manipulation, and then parsing it back, but that seems ridiculous. Surely there's some way to construct a linestring directly from a series of points?

(With "geometry" types, I can use @p2.STUnion(@p1).STConvexHull(), but there's no STConvexHull() for a geography type.)

Answer 1

There are two ways to do it 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)+')')