CODEWITHSUNDEEP
mysqlselect
H3nry T33
H3nry T33

Reputation: 356

How to get distance in kilometer when pass in 2 value (langtitude and longitude) in mysql?

I have a table in mysql call BRANCH

===============================
Branch_id latitude longitude
===============================
1         3.109421  101.622913
2         3.101121  101.644913

How can i select kilometer calculation from this table when I pass in my current location latitude / longitude?

Example:

If I pass in my current location = 3.122221 101.343913

============================================
Branch_id latitude longitude   distance(km)
============================================
1         3.109421  101.622913   0.4
2         3.101121  101.644913   0.6

Edited (Solved):

SELECT p.title,p.subtitle,p.desc,p.image,p.promotion_id,p.merchant_id,p.date_from,p.date_to,m.merchant_name,p.view,mb.latitude,mb.longitude,
    (6371 * acos (cos( radians(3.158704)) * cos( radians(mb.latitude)) 
        * cos( radians(mb.longitude) - radians(101.713963)) + sin(radians(3.158704)) 
        * sin( radians(mb.latitude)))) AS distance
FROM merchant_branch as mb
left join merchant m on mb.merchant_id = m.merchant_id
left join promotion p on p.merchant_id = m.merchant_id
where p.promotion_id is not null order by distance asc

Upvotes: 0

Views: 69

Answers (1)

SyntaxGoonoo
SyntaxGoonoo

Reputation: 1070

From: https://developers.google.com/maps/solutions/store-locator/clothing-store-locator

Here's the SQL statement that finds the closest 20 locations within a radius of 25 miles to the -33, 151 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

Upvotes: 1

Related Questions