• 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>

Open in new window

0
MFredin
Asked:
MFredin
1 Solution
 
MarcLightCommented:
<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
 
MFredinAuthor Commented:
You da man! Thanks!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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