GPS Within Range

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:
Ray PaseurCommented:
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
microvbAuthor Commented:
@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.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Ray PaseurCommented:
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!
microvbAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
microvbAuthor Commented:
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.
microvbAuthor Commented:
No other responses produced code examples. This answer was the closest to a complete solution and will benefit future users searching for similar information.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.