TSQL: What is the most efficient way to query spacial data?

Question

TSQL: What is the most efficient way to query spacial data?

moondaddy 916

I have millions of address which I can store as points using both decimal lat/long and geography data type so we have all options available to us. I need to be able to query all addresses (points) with in a radius of x miles (such as .5, 200, etc.). Obviously a table with many rows and searching a radius of 200 miles will take be good task, and we're hoping for instant results.

We're using sql server 2019.

Any recommendations on the fastest way to do this?

Thank you.

Accepted answer

2 additional answers

Your answer

Answer 1

Hi @moondaddy ,

Thank you so much for posting here.

You could refer David's answer.

Only adding some notes here:

According to Microsoft Learn, spatial indexes will be used with geography types on the following methods when they appear at the beginning of a comparison predicate with a WHERE clause:

STIntersects
STDistance
STEquals

You could also refer Improving the performance of STIntersects and check whether it is helpful to you.

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

Answer 2

That's what Spatial Indexes are for. In particular

Spatial indexes support the following predicate forms:

geography1.STIntersects(geography2)= 1

geography1.STEquals(geography2)= 1

geography1.STDistance(geography2) < number

geography1.STDistance(geography2) <= number

So something like

 DECLARE @g geography;    
 SET @g = geography::Point(29.76, -95.38, 4326)  
      
 SELECT top 10 *, Co_LocationPoint.STDistance(@g) distance  
 FROM tbCompany  
 where  Co_LocationPoint.STDistance(@g) < 20 * 1000  
 order by distance desc

Answer 3

moondaddy 916

I think that is assuming the data is stored a geometries. From my reading it seems better to store the data points as geography

https://www.mssqltips.com/sqlservertip/1965/sql-server-geography-data-type/.

So this is what I'm doing to get a distance. any thoughts on which is better, storing location points as geography rather than geometry data type?

(note, Co_LocationPoint is a geography data type)

DECLARE @g geography;  
SET @g = geography::Point(29.76, -95.38, 4326)

SELECT 
@g.STDistance(Co_LocationPoint)
FROM tbCompany

David Browne 111 Reputation points Microsoft Employee

2020-12-06T20:57:22.647+00:00

Yes, you would normally use Geography. Spatial indexes support both. In fact I meant to paste the geography methods, which are documented in the same article. See updated answer.
Ronen Ariely 15,206 Reputation points

2020-12-06T23:17:27.05+00:00

Hi,

Just to add a comment, that special indexes had an awesome algorithm behind the scenes and can provide huge performance improving. They are so good that sometimes I solve questions which are not related to geographic/geometric using special types.

Share via

TSQL: What is the most efficient way to query spacial data?

2 additional answers

Your answer