• Status: Solved
• Priority: Medium
• Security: Public
• Views: 946
• Last Modified:

Find closest X locations from center point using lat/long

I've got an application that does a simple radius search using lat/long to find trucks within the radius distance.  This works great, but now  I'm trying to build a new search option that will allow them to enter the number of trucks they want to find and the system will search for the nearest X amount of trucks from the center point.

How can I do this most efficiently?  Would I first have to calculate the distance for each truck and then pull the top X trucks closest to the center point?
``````<cfset center.latitude = 44.2041>
<cfset center.longitude = -94.9908>
<cfset center.distance = 50>

<cfquery name="radius">
SELECT trucks.truck_id
FROM trucks
WHERE active = 'YES'
AND 3963.191 * ACOS((SIN(PI() * #center.latitude# / 180) * SIN(PI() * trucks.origin_lat / 180)) + (COS(PI() * #center.latitude# /180) * cos(PI() * trucks.origin_lat / 180) * COS(PI() * trucks.origin_long / 180 - PI() * #center.longitude# / 180)) ) <= #center.distance#
</cfquery>
``````
0
MFredin
Asked:
1 Solution

Commented:
<cfquery name="radius">
SELECT trucks.truck_id, 3963.191 * ACOS((SIN(PI() * #center.latitude# / 180) * SIN(PI() * trucks.origin_lat / 180)) + (COS(PI() * #center.latitude# /180) * cos(PI() * trucks.origin_lat / 180) * COS(PI() * trucks.origin_long / 180 - PI() * #center.longitude# / 180)) ) as truck_distance
FROM trucks
WHERE active = 'YES'
ORDER BY truck_distance
LIMIT #how_many#
</cfquery>
0

Author Commented:
You da man! Thanks!
0

Featured Post

Tackle projects and never again get stuck behind a technical roadblock.