Listing geo-places near a user location

I have a php webapp that allows people to insert their location via google maps api (v3). I then store the lat/lng coordinates to my db.

Another thing I do is allow people to add certain events to a database, I then insert the event to m开发者_Python百科y db with the same lat/lng of the user. for example:

Users table:

ID    U_Name    U_Location_ID
1     test      23

Events table:

ID    E_Name    E_Location_ID
1     testevent 23

Location table:

ID    L_LAT        L_LNG        L_Name
23    40.759139    -73.979723   30 Rockefeller Plaza, New York, NY 10112, USA

What I'm trying to do now, is to display a user with a list of events that are near his location.

I don't necessarily have his exact location, but usually just the country and city (not all visitors are registered members that filled in their exact location).

So when a member vists, I use an ip to location api that gives me his country name and city name. I have another way of getting the country/city lat/lng coordinates, but now I'm stuck when I need to display the near events to that city/country.

When I say 'near' I hope it's possible to set some sort of diameter in Kilometers or Miles and list the events that are within that diameter.

Thanks for your help

Here the full query in MySQL that works on every tables. Keep in mind that the lat / lon fields must be decimal fields, unless you will loose precision :

-- set your vars
SET @lat := $$$$$;
SET @lon := $$$$$;
SET @dist := $$$$$;

-- prepare a square in miles
SET @x1 := @lon - @dist / ABS(COS(RADIANS(@lat))*69);
SET @x2 := @lon + @dist / ABS(COS(RADIANS(@lat))*69);
SET @y1 := @lat - (@dist / 69);
SET @y2 := @lat + (@dist / 69);

SELECT  t.*, 
        3956 * 2 * ASIN(
            SQRT(
                POWER(
                    SIN(
                        (@lat - t.lat) * pi() / 180 / 2
                    ), 2
                ) +
                COS(
                    @lat * pi() / 180
                ) *
                COS(
                    t.lat * pi() / 180
                ) *
                POWER(
                    SIN(
                        (@lon - t.lon) * pi() / 180 / 2
                    ), 2
                )
            )
        ) as distance
FROM    `YourTable` t
WHERE   t.longitude BETWEEN @x1 AND @x2
AND     t.latitude BETWEEN @y1 AND @y2
HAVING  distance < @dist
ORDER BY distance
LIMIT 10;

You just need to replace the $$$$$ with your values. Now if you want a full explanation, see this slides by Alexander Rubin

And for kilometers, just replace the x1, x2 ... by

-- prepare a square or in kilometers
SET @x1 := @lon - @dist / ABS(COS(RADIANS(@lat))*110);
SET @x2 := @lon + @dist / ABS(COS(RADIANS(@lat))*110);
SET @y1 := @lat - (@dist / 110);
SET @y2 := @lat + (@dist / 110);

Its important to note that if you go with the wonderful answer by Xavier --and-- if you use his kilometers change you must also change 3956 * 2 to 6371 * 2 in the calculation or you're just changing the range, not the produced distance.

I'm not really understanding your problem. If you are using geolocation DB like MaxMind, you also get to every city a latitude/longititude. You just have to choose some delta for longitude and latitude, run the SQL query and calculate in your view the distance from the users location to the event location. It's not really complicated.

Are you aware of mysql spatial extensions?

Here is a query wich could help you get all locations within a certain diameter. {NUMBER OF KILOMETERS} will be the diameter. $lon AND $lat will bet the coordinates of the users' location.

SELECT D.*, DEGREES(acos(cos(RADIANS(90-latitude))*cos(RADIANS(90-$lat))+sin(RADIANS(90-latitude))*sin(RADIANS(90-$lat))*cos(RADIANS(longitude-$lon))))/360*40074 AS distance FROM location_table as D HAVING distance <= {NUMBER OF KILOMETERS}