Jump to content

Handling Latitudes & Longitudes (PHP,MySQL)


sepoto
 Share

Recommended Posts

 <?php$dbh = new PDO('mysql:host=localhost;dbname=mydata', 'root', '**********'); $tLat = $_GET['tLat'];$tLng = $_GET['tLng'];  foreach($dbh->query('select name, lat, lng from locations') as $row) {	$main_arr[] = $row;} echo json_encode($main_arr); ?>

So I have some code above which I am working on. I am thinking about what is the best way to handle this. I suppose I could develop a simple solution that just adds and subtracts from "$tLat" and "$tLng" and then use a "where" clause in the query but I am looking for a better way. I know that there are GIS functions built into MySQL that perhaps could be used. My goal is to find all rows where lat and lng are a specified radius from "tLat,tLng". ? Thanks! P.S. I have already existing code for PHP that implements Haversine's that I could use but that would require each row to be processed individually. Is there not a way to handle the distance calculation within the query directly?

Edited by sepoto
Link to comment
Share on other sites

I use this to find zipcodes out of the zipcodes_table so I can display nearby records. I hope it helps you figure out what you are trying to do

SET @orig_lat = $latitude; // e.g: 121.9763SET @orig_lon = $longitude; // e.g: 40445SET @dist = $distanceInMiles; //e.g: 1000 SELECT b.zipcode, 3956 * 2 * ASIN(SQRT(POWER(SIN((@orig_lat - abs(b.latitude)) * pi()/180 / 2),2) + COS(@orig_lat * pi()/180 ) * COS(abs(b.latitude) *pi()/180) * POWER(SIN((@orig_lon - b.longitude) *pi()/180 / 2), 2) )) as distance FROM zipcodes_table as bhaving distance < @distORDER BY distance limit 10

Edited by astralaaron
  • Like 1
Link to comment
Share on other sites

I am trying to determine the meaning of "b.zipcode". I see a calculation is performed and it has something to do with "b.zipcode" but I don't know what all that fully means yet. In the last part of the code I see that "b.zipcode" is then referred to as just "b" which is slightly confusing to me although it looks like excellent code I need to understand it.

Edited by sepoto
Link to comment
Share on other sites

The identifier b is an alias for the zipcodes_table table, and zipcode is one of the fields in that table. It's just another field that the query is getting, it doesn't have anything to do with the distance calculation. That query selects 2 values, the zipcode field and the distance value.

  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...