CODEWITHSUNDEEP
sqlsql-serverlatitude-longitude
James Privett
James Privett

Reputation: 1109

SQL Server, Lat and Lon query

I'm running a query to get all locations in a table within a 15 mile radius. The query can take up to 15 seconds.

SELECT * 
FROM rmiukpostcodes
WHERE 3963.191 * ACOS( (SIN(PI()*50.8320711166062/180)*SIN(PI()*latitude/180))+(COS(PI()*50.8320711166062/180)*cos(PI()*latitude/180)*COS(PI()*longitude/180-PI()*-0.207548227491786/180))) < = 5

Is there anything I can do to speed it up?

Upvotes: 1

Views: 668

Answers (3)

Amy B
Amy B

Reputation: 110221

Your query reads every row in the table. Use some SARGABLE filter criteria and an index.

For example: before you run the query, precompute the max/min lat or long and use it.

WHERE
  codes.Lat BETWEEN @minLat AND @maxLat
  AND YourDistanceCalcHere(codes.Lat, codes.Long)

Then, an index on codes (Lat) would help the query

Upvotes: 0

vstrien
vstrien

Reputation: 2605

Use spatial data types.

Upvotes: 3

user596075
user596075

Reputation:

You might benefit here from using a computed persisted column. You're causing a table/index scan because of your WHERE clause, I believe.

Capture your execution plan. Try adding the computed column and alter your SELECT query.

Upvotes: 1

Related Questions