SQL Server spatial join - left outer

LooneyOMooney 1 Reputation point
2020-12-17T16:45:26.91+00:00

Can anyone please tell me how to execute a left outer join-like query with a spatial join?

Current query:

select TableA., TableB.

from TableA, TableB

where TableA.Shape.STCentroid().STIntersects(TableB.Shape) = 1

This only returns me records from TableA if the shape centroid intersects a shape from TableB. Sometimes a TableA shape does not intersect a TableB shape. But I still want to return all records from TableA in the query with NULL values in the TableB columns.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 100.7K Reputation points MVP
    2020-12-17T22:49:38.387+00:00

    I have not worked with spatial at all, but this query executed without error:

    select A.*, B.*
    from A
    left join B ON A.Shape.STCentroid().STIntersects(B.Shape) = 1
    

    Since I don't know spatial, I was not able to add any test data to see whether it gives the correct result or not.

    0 comments No comments

  2. MelissaMa-MSFT 24,176 Reputation points
    2020-12-18T01:22:15.427+00:00

    Hi @LooneyOMooney

    Thank you so much for posting here.

    Please refer below and check whether it is helpful:

    select A.*, B.*  
    FROM TableA A WITH(INDEX([SPATIAL_INDEX_NAME]))  
    LEFT OUTER JOIN TableB B  
    ON A.Shape.STCentroid().STIntersects(B.Shape) = 1  
    

    You could consider to add an index hint " WITH(INDEX(...)) " as this will speed up the query.

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table

    0 comments No comments