We help IT Professionals succeed at work.

GPS Within Range

microvb asked
Medium Priority
Last Modified: 2012-04-05
I would like to find any records that are within a specific RANGE (in feet) of a specific LAT and LON (parameters) from a MySQL database.

For example, I would specify the following in the MySQL query (php created so $variables preferred).

$latititude = 23.155177;
$longitude = 23.378906;
$range_in_feet = 400;

$params = calculations go here and conversions to make the parameters work.

$sql = "SELECT lat, long FROM table WHERE ' . $params . ' AND datetimestamp BETWEEN '2012-02-03' AND '2012-02-04';";
$results = mysql_query($sql);


Open in new window

There is no "feet" recorded in the MySQL table;  only Latitude and Longitude GPS co-ords along with a Date & Timestamp (UNIX format), and an index field unique integer.

Ideally, the resultset should not be exact, but to allow for a plus/minus fuzzy logic range.
Watch Question

You're asking quite a bit for only 500 points.  There are simple answers, and there are complete/accurate answers.  See this page for more guidance about how to approach your problem:

Most Valuable Expert 2011
Author of the Year 2014

Start your learning adventures with these two articles.

Given a LAT/LON pair, also called a "geocode" you can compute the distance to any other LAT/LON pair.  With distances like 400 feet, plane geometry is sufficient.  If the distances are potentially much larger (like 100 miles) the Haversize formula is your friend.  Please read those articles over and post back if you have any questions.

Best of luck with your project, ~Ray


@eriksmtka:  In case you were unaware, 500 is the maximum allowed to be assigned and the points actually awarded depend upon the grade of the answer, with grade A single solution being the highest ranking at around 4 x or more the points assigned -- I can not recall the specifics and am feeling lazy after doing all the mathematical research.

@Ray_Paseur: Hello again :).  There are a lot of examples on how to query the database based on Zip code and return the results within X miles of Zip code, however what I am looking for is a bit narrower in scope and works solely on GPS co'ords.  The fuzzy logic bit would be as simple as adding a +/- factor to the amount of FT the range is within.

I found some documentation on it, but in my humble opinion is rather convoluted.  I wrote a function (rather modified someones nasty function code) that would do the Zip code lookup quite simply, however both the method and the math seem to be radically different when looking at a different precision/metric, and the queries vary substantially when looking up co'ords base on co'ords only.

Would be every so grateful as I really do not wish to mess with this math any longer as it is rather frustrating for me on a tedious scale.  I am not that great at converting measurement formulas into PHP, and the one example I have found that closest matches this is written in Pure SQL, and contains Double sets of co'ords as parameters, so definitely no good.  The param is a single pair which should return all pairs that are within a certain radius +/- X distance in feet.  KM / Mile / Double Co'ords / Zip code based / etc, are all no good for the purpose.
Most Valuable Expert 2011
Author of the Year 2014

If you are uncomfortable doing the math yourself, please consider hiring a professional developer to help you with this application.  The principles of distance calculation are the same whether the distances are miles, meters, feet or less.  Your assertion that KM / Mile / Double Co'ords / Zip code based / etc, are all no good for the purpose misses the principles completely.  

A professional can have you on firm ground in a matter of minutes.  But this is not really a question that is likely to find a free answer in an online forum like EE.  We are good at answering succinct questions but not so good at developing your applications for free.

So my recommendation is to find a professional developer with enough time and willingness to step up to your requirement.  And engage that developer to write the parts of your code that you are unsure about.  It will save you a lot of money and time, I promise!
Was thinking someone could come up with a simpler solution, and if you are as familiar as you claim by writing those articles, then this really shouldn't be that complex.  Yes, the distance in FT is very different than in KM / Miles ----  although logically, it seems not, however you must take in to calculation the circumference of the planet in whichever model you wish the results in first. The radicals in the math also vary depending on the metric returned.

As for the ZIP code lookup, this manifests a significant change in the SQL query in which there is no originating GPS co'ords as they are obtained as a subset of the initial query then calculated on -- again, in the same metric as originally used.

This is probably why you choose not to assist with this.  And if that is the case that this site is for only answers not requiring code snippets (Not writing the entire project as this is a VERY tiny piece), then I am honestly suprised to see this system doesn't comprise of only spelling assistance and "does my essay look stolen" questions.

I come on here to help people with code, as it appears that you do as well, and some minor things are more complex than other minor things, however I will still assist if I can offer a solution.  External links to articles that are related only based on a preliminary scan of the question and not a thorough read are more than welcome, but are not acceptable solutions.

As a professional coder of many years, sometimes I get lazy/annoyed at tedious and meticulous stuff even as simple (mundane) as copy/paste routines.  I do sub stuff out when there is an inherent benefit and potential for long term relationship, so unless you can honestly forsee this mathematical calculation becoming something that would require ongoing maintenance (perhaps you are thinking something in the Quantum Mechanics field related to Dark Matter anomalies ?!), then I will end with this being something I will do myself.

On a side note, if the Internet was so riddled with festering slime that consists of tweets and Facebook updates of what someone had for breakfast, information of this nature would be more readily obtained --- alas .....  there is always the library.....  but thats..... *cough*  ... offline !

I do so sorely miss the BBS days where mostly the nerdiest of us were online while the remainder of the spammers ended up on AOL / Compuserve.

As I can not stand perusing through forums or Questions that remain unanswered for years, or the answers are similar to  --- "but why do you want to do that" ...  or   ...  "don't ask that here", and thus wasting who knows how many peoples time, I have decided to write a solution myself  (albeit very rudimentary and untested), using a slight variation on the calculation from "eriksmtka" above.

Unfortunately Ray, as I have already reviewed your posts (and countless others including one of uncanny precision written in pure MySQL) and it does not come close to what I need. Although your post does serve a decent function (in miles) for radius of which city is closest, it does not work very well on a more precise level to determine if you are within X feet of a specific set of co'ords, and you have failed to show in your post (although in fairness this was not what your thread was about) how such calculations might apply on a more precise and non-predefined index (aka zipcode) precurser as the lookup parameter -- being that it should be raw geocode lookup from a geocode.

$host = 'localhost';
$user = 'root';
$pass = '';
$data = 'test';

$lat = 23.155177;
$lon = 23.378906;
$range = 400;
$fuzzy = 50; // Distance for variation allowance in feet

$sql = 'SELECT latitude, longitude FROM `geo` WHERE
(131332796.6 x (ArcCos{Cos(' . $lat . ')*Cos(latitude)*Cos(' . $lon . ')*Cos(longitude) + Cos(' . $lat . ')*Sin(latitude)*Cos(' . $lon . ']*Sin(longitude) + Sin(' . $lat . ')*Sin(' . $lon . '))/360)) <= ' . $range + $fuzzy . ';';

$cn = mysql_connect($host,$user,$pass);
$results = mysql_query($sql,$cn);
if($results) {
   while($row = mysql_fetch_assoc($results)) {

Open in new window


I will await your responses before I close this off as the final solution, and sincerely do appreciate any real time given to this.  If no one every asked complicated questions, then we would never have simple solutions created for complicated problems.


No other responses produced code examples. This answer was the closest to a complete solution and will benefit future users searching for similar information.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.