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)

You're going to need to provide more information than that or people will downvote your question. Try adding the relevant code as well as describing your data format. Maybe even data examples as well. — robbpriestley, Dec 11 '19 at 16:56
Thanks. This is my first post and don't really know what to include. — I_Tried, Dec 11 '19 at 17:05
As far as I know 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. — Piotr Palka, Dec 11 '19 at 18:18
Do you know anything that works about the same/ a work-around but preserves Z and M? CollectionAggregate keeps them but it gives me an unordered geometry collection, which is not what I want — I_Tried, Dec 11 '19 at 18:23

score 0 · Accepted Answer · answered Dec 11 '19 at 18:37

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

SQL Server 2012, UnionAggregate Missing Z and M

1 Answers1