CODEWITHSUNDEEP
mysql
lisovaccaro
lisovaccaro

Reputation: 33956

Select rows with latitude-longitude values inside 10km radius?

I have a site which shows posts from a certain city. I was thinking of changing from Cities to a radius around the user's location (10km in this case).

I don't know how to query using a radius. I can only think of doing a query with two conditions and get a square. e.g:

SELECT fields
FROM points
WHERE lat BETWEEN LAT1 AND LAT2
AND lon BETWEEN LON1 AND LON2

Is it possible to select rows inside a radius of X km/mi from a certain location?

Upvotes: 3

Views: 3110

Answers (2)

Ahtesham Shah
Ahtesham Shah

Reputation: 65

$users = DB::table('users')
        ->select('id','name','phone','latitude','longitude')
        ->SQRT("POW(69.1 * (latitude - 24.900110), 2) +
        POW(69.1 * (67.099760 - longitude) * COS(latitude / 57.3), 2)) AS distance")
        ->having("distance < 25")
        ->orderBy("distance")
        ->get();

Upvotes: 0

Greg Hewgill
Greg Hewgill

Reputation: 992937

In general, you can use a WHERE query on any condition. So, supposing you have a function distance() that can compute the distance between two geographic points, you could do:

SELECT fields
FROM points
WHERE distance(lat, lon, city_lat, city_lon) <= 10

where city_lat and city_lon is the location of the city centre.

PostgreSQL contains a add-on function called earthdistance() that would help.

Upvotes: 2

Related Questions