0

I need to combine multiple geography records into a single entity to store in a spatial data table, but I've learned that the only available function in MSSQL2008R2 that accomplishes this task is STUnion which only works for two static geography records. I have upwards of 200 that I need to union and I'm loathe to hand code that kind of query.

On that note, I wrote up a one-off cursor to iteratively union each geography object from my staging table. However, when executed, the cursor returns a null value without any errors or messages.

DECLARE
    @ShapeUnion GEOGRAPHY,
    @Shape GEOGRAPHY

DECLARE curSpatial CURSOR FOR
SELECT
    geom
FROM
    dbo.Boundaries_Staging

OPEN curSpatial

FETCH NEXT FROM curSpatial INTO @Shape

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @ShapeUnion = @ShapeUnion.STUnion(@Shape)

    FETCH NEXT FROM curSpatial INTO @Shape

END

CLOSE curSpatial
DEALLOCATE curSpatial

SELECT @ShapeUnion

Edit: Updated script for anyone curious about unioning multiple geography records. As stated in the comments, I initialized @ShapeUnion prior to the loop. Also, I added .STBuffer() to the field being unioned; this ensures that the miniscule spaces between the spatial data are completely filled.

DECLARE
    @ShapeUnion GEOGRAPHY,
    @Shape GEOGRAPHY

DECLARE curSpatial CURSOR FOR
SELECT
    geom
FROM
    dbo.Boundaries_Staging
WHERE
    geom IS NOT NULL

OPEN curSpatial

FETCH NEXT FROM curSpatial INTO @Shape

SET @ShapeUnion = @Shape

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @ShapeUnion = @ShapeUnion.STUnion(@Shape.STBuffer(1))

    FETCH NEXT FROM curSpatial INTO @Shape

END

CLOSE curSpatial
DEALLOCATE curSpatial


SELECT @ShapeUnion
  • You're entirely correct, I didn't even notice that. Worked fine when I initialized @ShapeUnion before the loop. – Davenport Oct 2 '13 at 17:45
4

Try inside the loop:

SET @ShapeUnion = COALESCE(@ShapeUnion.STUnion(@Shape), @Shape);

(Or initializing @ShapeUnion somehow first.)

You'll also want to filter the cursor query to not include any rows where geom is NULL (if that is possible in your data model).

You can also do this without a cursor, I think:

DECLARE @ShapeUnion GEOGRAPHY;

SELECT @ShapeUnion = COALESCE(@ShapeUnion.STUnion(geom), geom) 
  FROM dbo.Boundaries_Staging
  -- WHERE geom IS NOT NULL
  ;

SELECT @ShapeUnion;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.