SQL Server spatial join - left outer

Question

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.

Answer 1

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.

Answer 2

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