Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


GPS Within Range

Posted on 2012-03-12
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.
Question by:microvb
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
  • 4
  • 2

Expert Comment

ID: 37711975
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:

LVL 111

Expert Comment

by:Ray Paseur
ID: 37712030
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

Author Comment

ID: 37712777
@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.
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

LVL 111

Expert Comment

by:Ray Paseur
ID: 37712828
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!

Accepted Solution

microvb earned 0 total points
ID: 37712937
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


Author Comment

ID: 37712946
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.

Author Closing Comment

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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

609 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