In my table there is a geometry field that stores polygons.
The polygons that intersects each other must be attached to and form a single polygon (see below)
(source: location-world.com)
The results can not be calculated but must be executed on the flight. I've done it with a cursor but it takes too much time.
Is there a way to perform the task without using cursors? I'm using Sql Server 2008 R2.
I have attached the source code that uses cursors.
DECLARE @Results TABLE (ResultId INT, Shape GEOMETRY)
DECLARE @ExistingIds TABLE (Id VARCHAR(50))
DECLARE @Id VARCHAR(50), @Shape GEOMETRY
DECLARE ViasCursor CURSOR FOR
SELECT ID, shape
FROM [dbo].[layer.2502]
OPEN ViasCursor
FETCH ViasCursor INTO @Id, @Shape
DECLARE @ResultId INT = 0
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF NOT EXISTS ( SELECT Id FROM @ExistingIds WHERE Id = @Id )
BEGIN
DECLARE @Geo GEOMETRY = NULL
SELECT @Geo = COALESCE(@Geo.STUnion(Shape), Shape)
WHERE [Shape].STIntersects(@Shape) = 1
INSERT INTO @ExistingIds ( Id )
SELECT [Id]
FROM [dbo].[layer.2502]
WHERE [Shape].STIntersects(@Shape) = 1
DECLARE @ExistingId INT = NULL
SELECT @ExistingId = [ResultId]
FROM @Results
WHERE Shape.STIntersects(@Geo) = 1
IF @ExistingId IS NOT NULL
UPDATE @Results SET [Shape] = [Shape].STUnion(@Geo)
WHERE ResultId = @ExistingId
ELSE
BEGIN
INSERT INTO @Results ( [ResultId], [Shape] )
VALUES ( @ResultId, @Geo )
SET @ResultId = @ResultId + 1
END
END
FETCH ViasCursor INTO @Id, @Shape
END
CLOSE ViasCursor
DEALLOCATE ViasCursor
SELECT * FROM @Results
