Welling Posted February 11, 2009 Share Posted February 11, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/144830-coordinates-and-near-city/ Share on other sites More sharing options...
The Little Guy Posted February 11, 2009 Share Posted February 11, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/144830-coordinates-and-near-city/#findComment-760012 Share on other sites More sharing options...
The Little Guy Posted February 11, 2009 Share Posted February 11, 2009 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! Quote Link to comment https://forums.phpfreaks.com/topic/144830-coordinates-and-near-city/#findComment-760039 Share on other sites More sharing options...
Welling Posted February 13, 2009 Author Share Posted February 13, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/144830-coordinates-and-near-city/#findComment-761296 Share on other sites More sharing options...
printf Posted February 13, 2009 Share Posted February 13, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/144830-coordinates-and-near-city/#findComment-761318 Share on other sites More sharing options...
printf Posted February 13, 2009 Share Posted February 13, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/144830-coordinates-and-near-city/#findComment-761330 Share on other sites More sharing options...
Zane Posted February 13, 2009 Share Posted February 13, 2009 http://www.phpfreaks.com/forums/index.php/topic,208965.0.html A Good Thread on this Quote Link to comment https://forums.phpfreaks.com/topic/144830-coordinates-and-near-city/#findComment-761333 Share on other sites More sharing options...
Psycho Posted February 13, 2009 Share Posted February 13, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/144830-coordinates-and-near-city/#findComment-761345 Share on other sites More sharing options...
Psycho Posted February 13, 2009 Share Posted February 13, 2009 EDIT: Mis-posted. Deleted. Quote Link to comment https://forums.phpfreaks.com/topic/144830-coordinates-and-near-city/#findComment-761347 Share on other sites More sharing options...
Welling Posted February 13, 2009 Author Share Posted February 13, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/144830-coordinates-and-near-city/#findComment-761360 Share on other sites More sharing options...
printf Posted February 13, 2009 Share Posted February 13, 2009 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... Quote Link to comment https://forums.phpfreaks.com/topic/144830-coordinates-and-near-city/#findComment-761368 Share on other sites More sharing options...
printf Posted February 13, 2009 Share Posted February 13, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/144830-coordinates-and-near-city/#findComment-761530 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.