Spatial Intersect of two SQL Server layers/tables

Question

I need to do a spatial intersection between two spatial tables with a geometry column in each within SQL Server 2016. I have a piece of T-SQL where I take a single set of polygons from one table (tableA), and then use each polygon and perform a STIntersects against the other table (tableB). I generate a series of independent tables corresponding to the number of polygons from tableA. These tables have the same columns.

declare @g geometry

declare @t Cursor 

Set @t = CURSOR FOR select Shape from admin where NAME1 = 'Jefferson'
open @t

FETCH Next FROM @t INTO @g
while @@FETCH_STATUS = 0
BEGIN
    Select * from ANC where ANC.Shape.STIntersects(@g) = 1
    FETCH Next FROM @t INTO @g  
END;

This returns the correct information and has an results table and individual map for each intersected polygon. The next step would be to then combine the above individual outputs into a single output table, but I am having difficulty with it. I have tried the following:

declare @g geometry

declare @t Cursor 

Set @t = CURSOR FOR select Shape from admin where NAME1 = 'Jefferson'

open @t

declare @firstone bit 
Set @firstone = 1 
FETCH Next FROM @t INTO @g
while @@FETCH_STATUS = 0
BEGIN
    IF @firstone = 1
    BEGIN
        Select * INTO #myTempTable from ANC where ANC.Shape.STIntersects(@g) = 1
        Set @firstone = 0
    END
    ELSE
    BEGIN
        Select * from #myTempTable UNION Select * from ANC where ANC.Shape.STIntersects(@g) = 1
    END;

    FETCH Next FROM @t INTO @g  
END;

Select * From #myTempTable

which returns an error that we can't do a union with a geography data type. There is a STUnion function, but I haven't seen that work with two tables. A better option would to be able to perform a spatial identity function between two spatial tables, but haven't found an example of that. However, I can get a rough equivalent if I can do a spatial intersection between the two layers and then a union of the results. Therefore, my first step is to get the spatial intersect to work across two spatial layers.

Answer 1

you don't need a Cursor. A Join will do here

SELECT *
FROM table1 A 
INNER JOIN table2 b ON b.Shape.STIntersects(A.Shape) = 1
where A.NAME1 = 'Jefferson'