SQL Server spatial join - left outer

LooneyOMooney 1 Reputation point

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.

{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 72,226 Reputation points MVP

    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.

  2. MelissaMa-MSFT 24,131 Reputation points

    Hi @LooneyOMooney

    Thank you so much for posting here.

    Please refer below and check whether it is helpful:

    select A.*, 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

    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