Coordinates and near city

[Welling]

I have a SQL table with a list of cities and their coordinates x, y (latitude and longitude).

city | lat | long

....  | ... | .....

I can get the coordinates of the IP of the user, How can I select from the cities table the nearest to the user?

 

Thank you.

[The Little Guy]

This is all SQL... I need to get on my laptop to get the code for you so give me a moment.

 

Edit:

 

Make sure you have access to the mysqli functions

[The Little Guy]

OK...

 

There are a few steps involved

 

1. Download and install MySQL Administrator http://dev.mysql.com/downloads/gui-tools/5.0.html

2. Open a connection to your database(s)

3. On the left hand side click on "Catalogs"

4. On the bottom left hand side click on your database with the zip codes

5. On the right hand side click on the tab labeled "Stored Procedures"

6. On the bottom click the button that says "Create Store Proc"

7. Enter "GetNearbyZipCodes" in the pop-up window

8. Click "Create PROCEDURE"

9. In the Text window place this code:

 

Replace the following:

databaseName with your database name

lat with your latitude column

log with your longitude column

zip with your zip column

state with your state column

County wit your county column

 

CREATE DEFINER=`databaseName`@`%` PROCEDURE `GetNearbyZipCodes`(
    zipbase  varchar (6),
    `range`  numeric (15)
)
BEGIN
DECLARE  lat1  decimal (5,2);
DECLARE  long1  decimal (5,2);
DECLARE  rangeFactor  decimal (7,6); 
SET  rangeFactor = 0.014457;
SELECT  `lat`,`log`  into  lat1,long1  FROM  zipCodes  WHERE  zip = zipbase;
SELECT  B.zip, B.city, B.state, B.County, GetDistance(lat1,long1,B.`lat`,B.`log`) as dist
FROM  zipCodes  B
WHERE
B.`lat`  BETWEEN  lat1-(`range`*rangeFactor)  AND  lat1+(`range`*rangeFactor)
  AND  B.`log`  BETWEEN  long1-(`range`*rangeFactor)  AND  long1+(`range`*rangeFactor)
  AND  GetDistance(lat1,long1,B.`lat`,B.`log`) <= `range` ORDER BY dist;
END

 

10. Press "Execute SQL"

11. Press "Create Stored Proc"

12. In the window type "GetDistance"

13. Press "Create FUNCTION"

14. In the text window place this code:

 

Replace the following:

databaseName with your database name

 

CREATE DEFINER=`databaseName `@`%` FUNCTION `GetDistance`(lat1  numeric (9,6),
lon1  numeric (9,6),
lat2  numeric (9,6),
lon2  numeric (9,6)  ) RETURNS decimal(10,5)
BEGIN
  DECLARE  x  decimal (20,10);
  DECLARE  pi  decimal (21,20); 
  SET  pi = 3.14159265358979323846;
  SET  x = sin( lat1 * pi/180 ) * sin( lat2 * pi/180  ) + cos( 
lat1 *pi/180 ) * cos( lat2 * pi/180 ) * cos( (lon2 * pi/180) -
(lon1 *pi/180)  );
  SET  x = atan( ( sqrt( 1- power( x, 2 ) ) ) / x );
  RETURN  ( 1.852 * 60.0 * ((x/pi)*180) ) / 1.609344;
END

 

15. Press "Execute SQL"

 

Alright! Your done with MySQL Administrator! (Keep it open though)

 

Now for the PHP

 

1. Open an editor and make a test php file.

2. Change "databaseName" to the name of your database

 

<?php
$zip = '12345'; // This is your current zip code
$radius = '10'; // This is a 10 mile radius

$dbHost = 'myhost.com';
$dbUser = 'myUserName';
$dbPass = 'myPassword';
$dbDatabase = 'myDataBase';

$db = mysqli_connect($dbHost, $dbUser, $dbPass, $dbDatabase);
$rZip = "CALL databaseName.GetNearbyZipCodes('{$zip}','{$radius}')";

if(mysqli_multi_query($db,$rZip)){
    do {
        if ($result = mysqli_store_result($db)) {
            while ($row = mysqli_fetch_array($result)) {
                echo $row['zip'].'<br />';
            }
            mysqli_free_result($result);
        }
    } while (mysqli_next_result($db));
}
?>

 

2. Save and run the file

 

 

Good LUCK!

[Welling]

But I want to use the cities I have, from all the world, there aren't a method to get the nearest with the coordinates?

[printf]

Something like...

 

/* center city name from ip lookup */

$center_city = '';

/* center city latitude from ip lookup */

$center_latitude = '';

/* center city longitude from ip lookup */

$center_longitude = '';

/* the name of the database table to perform the lookup on */

$radius_table = '';

/* the database table column / field name the holds the geo latitude */

$radius_latitude = 'latitude';

/* the database table column / field name the holds the geo longitude */

$radius_longitude = 'longitude';

/* the database table column / field name the holds the geo city */

$radius_city = 'city';

/* the number of miles radius that you want to return */

$radius_miles = 20; /* find all places from the center IP look up to this many miles */

/* The query... */

$radius_degrees = ( $radius_miles / 69.172 );

$result = mysql_query ( "SELECT " . $radius_city . ", round((degrees(acos(" . sin ( deg2rad ( $center_latitude ) ) . " * sin(radians(" . $radius_latitude . ")) + " . cos ( deg2rad ( $center_latitude ) ) . " * cos(radians(" . $radius_latitude . ")) * cos(radians(" . $center_longitude . " - " . $radius_longitude . ")))) * 69.172),2) AS miles FROM " . $radius_table . " WHERE " . $radius_city . " = '" . $center_city . "' AND " . $radius_longitude . " BETWEEN " . ( $radius_longitude - $radius_degrees ) . " AND " . ( $radius_longitude + $radius_degrees ) . " AND " . $radius_latitude . " BETWEEN " . ( $center_latitude - $radius_degrees ) . " AND " . ( $center_latitude + $radius_degrees ) . " AND (((degrees(acos(" . sin ( deg2rad ( $center_latitude ) ) . " * sin(radians(" . $radius_latitude . ")) + " . cos ( deg2rad ( $center_latitude ) ) . " * cos(radians(" . $radius_latitude . ")) * cos(radians(" . $radius_longitude . " - " . $radius_longitude . ")))) * 69.172) / (7 - ABS(" . $p1 . " - 3) / 5)) + (degrees(acos(" . sin ( deg2rad ( $center_latitude ) ) . " * sin(radians(" . $radius_latitude . ")) + " . cos ( deg2rad ( $center_latitude ) ) . " * cos(radians(" . $radius_latitude . ")) * cos(radians(" . $radius_longitude . " - " . $radius_longitude . ")))) * 69.172)) <= " . $radius_miles . " ORDER BY miles ASC;" );

 

If you just want to do the look up based on the lat & long returned by the IP look up, then remove this from the query...

 

" . $radius_city . " = '" . $center_city . "' AND

[printf]

Sorry in the query you will see " . $p1 . " that was for variable radius that changes based on the center lat and long, seeing you don't have that replace  *" . $p1 . "* with the number 3

[Zane]

http://www.phpfreaks.com/forums/index.php/topic,208965.0.html

 

A Good Thread on this

[Psycho]

It's been many years since I took a math class, but wouldn't this be as simple as using the Pythagorean theorem?

 

a(squared) + b(squared) = c(squared)

 

Where a is the difference in lattitude and b is the difference in longitude. Then just calculate c to determine the distance between the two points. You could incorporate that calculation in the query and sort by the result to determine the closest point.

 

$ip_longitude & $ip_lattitude must be defined prior to running the query. The values should be self explanatory.

SELECT city_id, city_name,
       SQRT(POW(ABS($ip_longitude - city_longitude) ,2) + POW(ABS($ip_lattitude - city_lattitude), 2)) AS distance

FROM cities

ORDER BY distance

LIMIT 1

 

Not sure how the performance would be on this. Perhaps the other methods above would be much faster.

[Psycho]

EDIT: Mis-posted. Deleted.

[Welling]

Something like...

[...]

If you just want to do the look up based on the lat & long returned by the IP look up, then remove this from the query...

[...]

Wow! This code seems  to be too complicated! I cann't test it now because my server is down... but I would know how it works to be able to modify thinks like the units (I want km not milles). Can you explain it a bit?

[printf]

The $row['miles'] can easily be converted in the query or when returning the result...

 

//outside

echo sprintf ( '%0.2f', ( $row['miles'] * 1.61 ) );

 

Don't have time to change the query now, but I will later...

[printf]

I am fixing the query because I made a real dumb mistake, a few places I had the table_latitude and table_longitude where center_latitude and center_longitude should have been...

 

fixed...

 

$result = mysql_query ( "SELECT *, round((degrees(acos(" . sin ( deg2rad ( $center_latitude ) ) . " * sin(radians(" . $radius_latitude . ")) + " . cos ( deg2rad ( $center_latitude ) ) . " * cos(radians(" . $radius_latitude . ")) * cos(radians(" . $center_longitude . " - " . $radius_longitude . ")))) * 69.172),2) AS miles FROM " . $radius_table . " WHERE " . $radius_longitude . " BETWEEN " . ( $center_longitude - $radius_degrees ) . " AND " . ( $center_longitude + $radius_degrees ) . " AND " . $radius_latitude . " BETWEEN " . ( $center_latitude - $radius_degrees ) . " AND " . ( $center_latitude + $radius_degrees ) . " AND (((degrees(acos(" . sin ( deg2rad ( $center_latitude ) ) . " * sin(radians(" . $radius_latitude . ")) + " . cos ( deg2rad ( $center_latitude ) ) . " * cos(radians(" . $radius_latitude . ")) * cos(radians(" . $center_longitude . " - " . $radius_longitude . ")))) * 69.172) / (7 - ABS(3 - 3) / 5)) + (degrees(acos(" . sin ( deg2rad ( $center_latitude ) ) . " * sin(radians(" . $radius_latitude . ")) + " . cos ( deg2rad ( $center_latitude ) ) . " * cos(radians(" . $radius_latitude . ")) * cos(radians(" . $center_longitude . " - " . $radius_longitude . ")))) * 69.172)) <= " . $radius_miles . " ORDER BY miles ASC;" ) or die ( mysql_error () );

 

Here a quick example using this query and a rather old IP database...

 

CITY INFORMATION WITH 10 MILE RADIUS OF YOUR IP