CODEWITHSUNDEEP
phpjquerymysqlapache
Ben
Ben

Reputation: 62356

Geolocation to find nearest city

I have a database of city names (Chicago, New York, Nashville, etc.). Only a few of them have coordinates. What's the simplest way for me to programmatically match the closest city/area in my database to their current location whether on a mobile device or on a desktop?

I'm on a shared server so I can't install any apache modules.

Update

Lets say coordinates weren't a problem. If coordinates in my database weren't an issue, what would be the best way to do this? Use HTML 5 to get the current user's coordinates and just run a simple query to determine the closest location?

Upvotes: 1

Views: 2019

Answers (3)

pinaldesai
pinaldesai

Reputation: 1815

You can have a US Zipcode database using which you can get desire output.

Download Zipcode database from IPinfo DB. refer below URL http://ipinfodb.com/zipcode_database.php

Now USE Below MYSQL function to findout Distance from currunt geolocation (latitude and longitude).

DELIMITER $$    
DROP FUNCTION IF EXISTS `great_circle_distance`$$
    CREATE DEFINER=`root`@`localhost` FUNCTION `great_circle_distance`(
    lat1 DOUBLE(10,6),
    lon1 DOUBLE(10,6),
    lat2 DOUBLE(10,6),
    lon2 DOUBLE(10,6)
    ) RETURNS double(10,2)
        DETERMINISTIC
    BEGIN
                    DECLARE delta_lat DOUBLE(10,6);
                    DECLARE delta_lon DOUBLE(10,6);
                    DECLARE temp1 DOUBLE(10,6);
                    DECLARE EARTH_RADIUS DOUBLE(10,2);
                    DECLARE distance DOUBLE(10,2);
                    SET lat1 = RADIANS(lat1);
                    SET lon1 = RADIANS(lon1);
                    SET lat2 = RADIANS(lat2);
                    SET lon2 = RADIANS(lon2);
                    SET delta_lat = lat2 - lat1;
                    SET delta_lon = lon2 - lon1;

                    SET temp1 = pow(sin(delta_lat/2.0),2) + cos(lat1) * cos(lat2) * pow(sin(delta_lon/2.0),2);
                    SET EARTH_RADIUS = 3956.0;
                    SET distance = EARTH_RADIUS * 2 * atan2(sqrt(temp1),sqrt(1-temp1));
                    RETURN distance;
        END$$

    DELIMITER ;

Below Query will help you to get result or you can modify query as per your need.

Select TA.city, great_circle_distance(TA.lat,TA.log, 32.2342,-72.42342) AS Distance FROM zipcodes AS TA Order BY Distance Limit 1;

Upvotes: 0

Francis Yaconiello
Francis Yaconiello

Reputation: 10919

I would say get a better database. Look online for a US census zipcode database. It has coordinates for every zipcode across the US. From there, geolocate the user, and find the closest zipcode, city, or whatever using the circle distance formula.

OR

get fancypants. Geohash the entire zipcode database (see: http://en.wikipedia.org/wiki/Geohash). then geohash the user's coordinates. compare geohashes to find the closest zipcode, city, whatever. The best thing about geohash is it doesnt require any crazy circle distance formula.

This may not be the latest and greatest, but you get the point: http://www.census.gov/tiger/tms/gazetteer/zips.txt

Upvotes: 0

fire
fire

Reputation: 21531

Use a service like GeoIP you can download a free database or use their API.

* Edit *

Here is their city specific page - http://www.maxmind.com/app/geolitecity

Upvotes: 2

Related Questions