Query optimization for drivers/merchants within a map of how many kilometres to take

Some applications based on Baidu/gold maps often have queries for such a scenario:

Take the nearest Y-driver/merchant in the X-kilometer range.

A company is doing this: The driver has a driver information sheet driverinfo, and a Driver location information table Driverposition.

Driverposition will update the driver's current longitude and latitude in real time.

Get the driver within X km range using a scalar value function getdistance, the longitude and latitude of the incoming customer, and the driver's latitude and longitude

The result returns the distance between the two. So the typical query is this:

DECLARE @longitude  as FLOAT = 31.2373453826;DECLARE @latitude  as FLOAT = 121.4976583979;DECLARE @Range  as TINYINT = 3;SELECT TOP Tend.id, D.phone, Getdistance (@longitude,@latitudeDp. LONGITUDE,DP. Latitude) asDistance fromDriverinfo asDINNER JOINdriverposition asDP onD.id=dp.idWHEREGetdistance (@longitude,@latitudeDp. LONGITUDE,DP. Latitude)<= @RangeORDER  byGetdistance (@longitude,@latitudeDp. LONGITUDE,DP. Latitude)ASC

The problem is that the query is going to go to the table scan because the condition is that it is operating on fields longitude and latitude.

A large number of such concurrent queries will cause the database server CPU to keep exploding the table, how to do?

--------------------------------------------------------------------------------------------------------------

In fact getdistance This function is nothing more than the latitude and longitude of the customer @longitude,@latitude as the center, the radius of the @Range to draw a circle.

So in order to use the index, we can also take @longitude,@latitude as the center, to @Range * 2 for the edge, draw a square. such as:

As to how much longitude or latitude is 1 km, here we need to calculate a factor X. For example approximately 0.009 longitude or latitude variation is 1 km.

Then the optimized query becomes the following:

DECLARE @longitude  as FLOAT = 31.2373453826;DECLARE @latitude  as FLOAT = 121.4976583979;DECLARE @Range  as TINYINT = 3;DECLARE @longitudeLow  as FLOAT = 31.2373453826 - 3*0.009;DECLARE @longitudeHight  as FLOAT = 31.2373453826 + 3*0.009;DECLARE @latitudeLow  as FLOAT = 121.4976583979 - 3*0.009;DECLARE @latitudeHight  as FLOAT = 121.4976583979 + 3*0.009;SELECT TOP Tend.id, D.phone, Getdistance (@longitude,@latitudeDp. LONGITUDE,DP. Latitude) asDistance fromDriverinfo asDINNER JOINdriverposition asDP onD.id=dp.idWHERE
Dp. Longitude between @longitudeLow and @longitudeHight
and DP. Latitude between @latitudeLow and @latitudeHight         and Getdistance (@longitude,@latitudeDp. LONGITUDE,DP. Latitude)<= @RangeORDER  byGetdistance (@longitude,@latitudeDp. LONGITUDE,DP. Latitude)ASC

Query optimization for drivers/merchants within a map of how many kilometres to take