0

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?

Jeroen Mostert
  • 27,176
  • 2
  • 52
  • 85
noClue
  • 958
  • 1
  • 13
  • 34
  • Are you using the `GEOMETRY` type or really just manipulating everything as strings? There's a [rich set of methods available on geometry](https://learn.microsoft.com/sql/t-sql/spatial-geometry/spatial-types-geometry-transact-sql), including effective ways to combine them. – Jeroen Mostert Dec 11 '18 at 11:42
  • @JeroenMostert I'm actually using the Geography type. – noClue Dec 11 '18 at 11:43
  • AFAICT there's no efficient way to do this in SQL Server other than string concatenation. You could optimize writing at the expense of reading by storing the polygons individually and creating the multipolygon on the fly, but usually you don't want to penalize reading as it's more common than writing. – Jeroen Mostert Dec 11 '18 at 12:08
  • Have you looked at using either `STUnion()` or `UnionAggregate()` to do what you're trying to do? – Ben Thul Dec 11 '18 at 12:54
  • 1
    And @Shnugo, since we're all linking to community guidance, here's one for you: https://stackoverflow.blog/2018/04/26/stack-overflow-isnt-very-welcoming-its-time-for-that-to-change/ – Ben Thul Dec 11 '18 at 12:56
  • @BenThul From what I've seen, STUnion() combines the polygons, which isn't what I'm looking for. I want to keep all the polygons separate inside the multipolygon. UnionAggregate() seems to do the same thing, although I couldn't test that one. – noClue Dec 11 '18 at 13:17
  • Re: concatenation: you may want to experiment with `.write()` on a `MAX` type as opposed to simple string manipulation, if you're not already doing that. `UPDATE @t SET x.Write(value, NULL, NULL)` is semantically equivalent to `UPDATE @t SET x += value`, but may well be more efficient in practice because it avoids an intermediate string (disclaimer: not actually tested myself). With a multipolygon string, this would involve replacing the end of the string. In theory, you can update the binary representation for even less data shuffling, but that's more involved. – Jeroen Mostert Dec 11 '18 at 13:26
  • @JeroenMostert, thx for pointing to `.write()`. Did not stumble over this before... Seems to work rather similiar to `STUFF()`, but with some differences (zero-based, handling of `NULL` parameters). Good to know... – Shnugo Dec 11 '18 at 14:11
  • 1
    @Shnugo: `STUFF` is fundamentally different in the implementation, though, in that it generates an intermediate value (just like any other string operation). `.write()` operates directly on the blob with a separate query operator (much like `UPDATETEXT`/`WRITETEXT` did for the deprecated types). Because you need some truly huge strings for it to matter and an effective use case for partial replacement, I've not had any need for `.write()` in production code myself, but it undoubtedly has its uses -- the problem raised in the question may be one of them. – Jeroen Mostert Dec 11 '18 at 14:23
  • @noclue - Fair enough. Can I ask what you're ultimately trying to do that keeping the polygons separate in important? I'm not doubting you, just seeing too understand. – Ben Thul Dec 11 '18 at 20:22
  • @BenThul These multipolygons are intended to be edited later on and we may make most of these polygons smaller, so two polygons close to each other wouldn't touch anymore. Merging the polygons like that would mean we would have to re-add a lot of polygons later in order to properly split the polygons by hand. Better to keep the polygons separate in the first place instead of opening that can of worms. – noClue Dec 12 '18 at 15:40
  • @noClue - Sure. But let's explore that further. If that's the case, why not just keep them as truly separate polygon instances and edit the singletons rather than trying to edit the n-th polygon in a multipolygon? You could tag them with a common identifier in your table to keep them associated if you wanted to. – Ben Thul Dec 12 '18 at 20:33
  • @BenThul So you're suggesting I save each polygon in a separate row? That would cause even more trouble for me, keeping track of each polygon would be a nightmare, especially if new polygons are added in a new import. Adjusting the system we already have to conform to this new structure isn't really an option at this point. – noClue Dec 13 '18 at 17:29
  • @noClue - that is what I was suggesting with the implication that you aggregate them when you need to for what ever purpose you need that aggregation. As it is, you're in violation of first normal form in that you're saving multiple values in a single "cell". But there are no "normal form police" that will knock on your door and I'm in no way offended that you're not taking me up on my suggestion. Real World™ concerns trump academic ones almost every time. – Ben Thul Dec 13 '18 at 17:58
  • @BenThul Well, I wasn't the one who implemented multipolygons into T-SQL, you'll have to lay the blame at Microsoft's door. :) – noClue Dec 13 '18 at 18:11
  • Oh... to the contrary. First, multipolygons are OCGS standard. And second (and probably more importantly), there are valid cases where they represent a singleton (which is probably why they're part of the standard). Take, for instance, the coastline of the Philippines. It's a series of islands that can be referenced with one name. To extend that into my suggested implementation, you could make an argument that (for example) Luzon and Visayas (two of those islands) should be represented as individual polygons and only aggregated when needed. It all depends on the application. :) – Ben Thul Dec 13 '18 at 19:00
  • Did you ever get an answer @noClue? I've just had to split, validatate and reorient a bunch of multipolygons, and I need to put them back together – Hecatonchires Sep 10 '20 at 01:19

0 Answers0