SQL Server 2012, UnionAggregate Missing Z and M

Question

Used union aggregate on multiple, ordered line geometries with M and Z but the final result is missing those Z and M values. I tried using geographies as well but no luck.

---

Query for testing

create table #test
(shape geometry)

insert into #test(shape)
values (geometry::STGeomFromText('LINESTRING (-89.831404 29.869888 2.5 28.58, -89.835404 29.869892 2.5 30.13)', 4269)), (geometry::STGeomFromText('LINESTRING (-89.835404 29.869892 2.5 30.13, -89.831403 29.869896 2.5 31.45)', 4269))

DECLARE @geom3 geometry = (select geometry::UnionAggregate(shape) FROM #test )
SELECT @geom3.AsTextZM()

drop table #test

---

This returns

LINESTRING (-89.831403 29.869896, -89.835404 29.869892, -89.831404 29.869888)

I would expect following result:

LINESTRING (-89.831403 29.869896 2.5 28.58, -89.835404 29.869892 2.5 30.13, -89.831404 29.869888 2.5 31.45)

Answer 1

UnionAgregate will generate a new geography values and it will remove in the process all Z and M (elevation and measure) data from source shapes.

What is important to notice, UnionAgregate will combine two points with the same X and Y coordinates but different Z and M into a single point with X and Y coordinates, so following script will return 2 points:

create table #test
(shape geometry)

insert into #test(shape)
values (geometry::STGeomFromText('POINT (-10 10 1 1)', 0)),
(geometry::STGeomFromText('POINT (-10 10 4 4)', 0)),
(geometry::STGeomFromText('POINT (-11 10 4 4)', 0));

select shape.AsTextZM() from #test

select geometry::UnionAggregate(shape).AsTextZM() FROM #test 

drop table #test