?
Solved

Find closest X locations from center point using lat/long

Posted on 2010-01-11
2
Medium Priority
?
945 Views
Last Modified: 2012-05-08
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
Comment
Question by:MFredin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 3

Accepted Solution

by:
MarcLight earned 1000 total points
ID: 26291624
<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 Comment

by:MFredin
ID: 26298942
You da man! Thanks!
0

Featured Post

Python: Series & Data Frames With Pandas

Learn the basics of Python’s pandas library of series & data frames and how we can use these tools for data manipulation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

FAQ pages provide a simple way for you to supply and for customers to find answers to the most common questions about your company. Here are six reasons why your company website should have a FAQ page
When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
The viewer will learn how to count occurrences of each item in an array.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question