From a set of geography points build a line

It seems that there is no simple solution. There are aggregation functions geography::CollectionAggregate() and geography::UnionAggregate() , but they do not allow you to specify the order of the points, which is important for the line.

It is possible through the text. In this case, I think it is better to use ToString() , from which to cut the substring, and not the cast from Lat and Long , so as not to depend on the decimal separator character. Then glue everything in line.

Also available via Gml and WKB .

Let, for example, @Points personify the points selected by us with the numbers of their following:

 declare @Points table (num int, point geography); declare @cnt int = 1000; with tally as (select top (@cnt + 1) N = row_number() over (order by @@spid) from sys.all_columns) insert into @Points (num, point) select N, geography::Point( (50 + 10 * sin(26 * t)) * sin(2 * t) + 15, (65 + 10 * sin(26 * t)) * cos(2 * t), 4326) from tally cross apply (select t = cast(N as float) / @cnt * pi()) p; 

Combine them into a line using the conversion to Gml format and back:

 -- line from points using Gml declare @line geography; with xmlnamespaces(default 'http://www.opengis.net/gml') select @line = geography::GeomFromGml(( select ( select point.AsGml().value('(/Point/pos/text())[1]', 'varchar(50)') + ' ' from @Points order by num for xml path(''), type ) for xml path('posList'), root('LineString')), 4326); 

Through the WKB format (here I had to resort to recursive CTE ):

 -- line from points using WKB declare @line geography; with line(num, geo) as ( select 0, cast(geography::STLineFromText('LINESTRING EMPTY', 4326).STAsBinary() as varbinary(max)) union all select l.num + 1, l.geo + substring(p.point.STAsBinary(), 6, 16) from @Points p join line l on l.num + 1 = p.num ) select top (1) @line = geography::STGeomFromWKB(cast(stuff(geo, 6, 4, iif(left(geo, 1) = 0x01, cast(reverse(cast(num as binary(4))) as binary(4)), cast(num as binary(4)))) as varbinary(max)), 4326) from line order by num desc option (maxrecursion 0); 

Check the result:

 select @line as Line;