Solved

GPS Within Range

Posted on 2012-03-12
7
438 Views
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).

<?php
$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);

var_dump($results);
?>

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.
0
Comment
Question by:microvb
  • 4
  • 2
7 Comments
 
LVL 5

Expert Comment

by:eriksmtka
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:

http://www.movable-type.co.uk/scripts/latlong.html
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 37712030
Start your learning adventures with these two articles.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_4276-What-is-near-me-Proximity-calculations-using-PHP-and-MySQL.html
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_9854-Using-GeoCodes-to-Find-an-Average-Location.html

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
0
 
LVL 3

Author Comment

by:microvb
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 108

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!
0
 
LVL 3

Accepted Solution

by:
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.

<?php
$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);
mysql_select_db($data);
$results = mysql_query($sql,$cn);
if($results) {
   while($row = mysql_fetch_assoc($results)) {
     var_dump($row);
   }
}
mysql_close($cn);
?>

Open in new window

0
 
LVL 3

Author Comment

by:microvb
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.
0
 
LVL 3

Author Closing Comment

by:microvb
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.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now