CODEWITHSUNDEEP
phpmysqlgeolocation
Stefa168
Stefa168

Reputation: 35

Get places near geographical coordinates

I have a database filled with geographical coordinates (DB 2), like 45.062792, 8.892737 , and another that has other coordinates (DB 1).

I want to use one coordinate of DB 1 and then I want to calculate in a radius given by a user (like 10 km, 5 km, 500 mt) all the rows in DB 2 that are inside the radius previously given.

I'd like to calculate all the results without (e.g.) Google Maps or other online maps because there are limits over the usage and I want to be able to manage the results by php code on my server.

Upvotes: 0

Views: 532

Answers (1)

Yeroon
Yeroon

Reputation: 3243

Use the Haversine formula using a lat/long coordinate and a radius as input. (I assume you have coordinates stored as separate lat and lon columns).

Your query roughly looks like this:

SELECT *, 3956 * 2 * ASIN(SQRT(POWER(SIN((@orig_lat - abs(tablename.lat)) * pi()/180 / 2),2) + COS(@orig_lat * pi()/180 ) * COS( 
abs
(tablename.lat) *  pi()/180) * POWER(SIN((@orig_lon – tablename.lon) *  pi()/180 / 2), 2) ))
as distance
FROM tablename
HAVING distance < @radius
ORDER BY distance 
LIMIT 10;

Replace @orig_lat with the given lat value and @orig_lon with the given lon value and @radius with a value in kilometers.

This will return all the coordinates from DB2 based on your coordinate from DB1 within the given radius.

More info here: http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

Upvotes: 2

Related Questions