Sql-server – Geography union via cursor returning null

nullsql-server-2008-r2t-sql

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

Best Answer

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;
Related Question