CODEWITHSUNDEEP
sql-servert-sqlsqlgeography
user1734974
user1734974

Reputation:

Append polygon into multipolygon

Currently, when I save a multipolygon, I do this by combining all the polygons I have into one string before I save them into one cell. Worked like a charm so far.

However, I'm starting to get bigger and more polygons, which creates huge strings and slows down my merge sp. I would like to know if there is a way to add a polygon to an existing multipolygon. Example: I have two polygons. With the first polygon, I create a new entry and save that one polygon as a multipolygon. Then, I take the second polygon and add it to the existing data, updating the already existing record.

INSERT INTO MyTable
VALUES ('MULTIPOLYGON(((1 1, 1 -1, -1 -1, -1 1, 1 1)))')

Pseudo code:

UPDATE MyTable
SET PolygonData.Append('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))')
WHERE Id = 1

Note: The column PolygonData is of the Geography data type.

Is it possible to append polygons like that into an existing multipolygon, without reading out the entire data and stitching it together?

Upvotes: 0

Views: 1021

Answers (1)

Stephan Samuel
Stephan Samuel

Reputation: 697

SQL is declarative so if you need to add as you go, string concatenation is probably your only choice.

If you can get all of your objects in the same place, which it sounds like you already may have, and they are geometry or geography objects, you can use geometry::UnionAggregate (or the same for geography) to create a multipoint, multiline or multipolygon. The geography documentation example shows this query using GROUP BY (I've reformatted for clarity):

USE AdventureWorks2022  
GO

SELECT

  City,  
  geography::UnionAggregate(SpatialLocation) AS SpatialLocation

FROM
  Person.Address  
WHERE
  PostalCode LIKE('981%')  
GROUP BY
  City
;

This takes individual geography/geometry objects in SpatialLocation and creates a selected column with the collection object type.

I have not tried this with differing base geometry types, such as trying to combine a point with a line. My guess is that it would fail if you did so. Also, per standard T-SQL, the SRID of every constituent entity must be the same, at least according to the documentation.

Upvotes: 0

Related Questions