2

I have database tables containing many (70,000+) GEOGRAPHY polygons. (The polygons are property parcels.) We need to perform a number of calculations on the aggregate shape (a MULTIPOLYGON) which consists of the geographic union of all of these parcels, such as "what percentage of the convex hull is covered by these polygons"? (Note: this isn't as simple as it sounds. Overlapping parcels do occur, and we don't want to double-count them, so we can't simply add up the area of the parcels.)

In order to perform these calculations, we want to generate a new shape which represents the geographic union of all of the polygons. Based on the answer to this question, I tried the following query:

DECLARE @Shape GEOGRAPHY
SET @Shape = GEOGRAPHY::STGeomFromText('MULTIPOLYGON EMPTY', 4326)

SELECT @Shape = @Shape.STUnion(Shape)
FROM Parcel

But the query takes forever (60 minutes and counting, so far; no answer yet). I'm wondering what other techniques others can think of which are more efficient. This query doesn't ever need to be a real-time query, but 60+ minutes isn't going to work either.

Community
  • 1
  • 1
jbeldock
  • 2,755
  • 3
  • 18
  • 31
  • My feeling is that the way the optimizer is fulfilling this request is it's taking g1 ∪ g2, taking that result ∪ g3, taking *that* result ∪ g4, etc. As a test, see how long 1, 10, and 100 rows take. If it's roughly linear, I'd say my theory is valid and we'll have to trick the engine into doing it efficiently. – Ben Thul Jan 16 '14 at 22:25

1 Answers1

3

Ideally you should use the UnionAggregate function, although that only exists since SQL Server 2012.

http://technet.microsoft.com/en-us/library/ff929095.aspx

Example from previous link:

SELECT City,
geography::UnionAggregate(SpatialLocation) AS SpatialLocation
FROM Person.Address
WHERE PostalCode LIKE('981%')
GROUP BY City;
psousa
  • 6,676
  • 1
  • 32
  • 44
  • +1 for cluing me in to this method! The only thing that I have to add is that there's also a ConvexHullAggregate which would seem to go well with the OPs problem. – Ben Thul Jan 16 '14 at 22:51