Constructing multipolygon from polygons

Question

Is there a better way to create multipolygon from polygons than text concatenation?

I have multiple polygons inside my database (one per one row in database) and for some of those I need to create multipolygons. The only solution I can see is creating function that will:

1. remove 'POLYGON' text from geometry displayed as text
2. concatenate it with other polygons displayed as text
3. add 'MULTIPOLYGON' text before
4. convert it to geometry type

I want from two polygons

polygon1: 'POLYGON((1 1, 1 -1, -1 -1, -1 1, 1 1))'
polygon2: 'POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))'

create one multipolygon:

result: 'MULTIPOLYGON(((1 1, 1 -1, -1 -1, -1 1, 1 1)), ((1 1, 3 1, 3 3, 1 3, 1 1)))'

It doesn't sound like a perfect solution so I'd like to know if there is a better way to do this.

Answer 1

You could use STUnion:

CREATE TABLE t(ID INT IDENTITY(1,1), c GEOMETRY);

INSERT INTO t(c)
VALUES ('POLYGON((1 1, 1 -1, -1 -1, -1 1, 1 1))')
      ,('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))');

DECLARE @g geometry = geometry::STGeomFromText('GEOMETRYCOLLECTION EMPTY', 0);
SELECT @g = @g.STUnion(c) FROM t

SELECT @g;

Rextester Demo