CODEWITHSUNDEEP
phpmysql
mudassar031
mudassar031

Reputation: 83

How to calculate desired radius or distance with specific latitude and longitude in MYSQL with proper SQL standard?

I need to know about how to calculate desired radius or distance with specific latitude and longitude in MYSQL with proper SQL standard? I have found many solutions on internet but they are taking to much time they have cos and other terms involved.please help!

Upvotes: 5

Views: 1217

Answers (2)

Teneff
Teneff

Reputation: 32148

To calculate the distance between two GPS points I'm using a MySQL function:

CREATE FUNCTION `distance`(lat1 FLOAT(10,7), lng1 FLOAT(10,7), lat2 FLOAT(10,7), lng2 FLOAT(10,7)) RETURNS FLOAT(20,2)
    RETURN ROUND(6371 * 2 * ASIN(SQRT( POWER(SIN((lat1 -ABS( lat2 )) * PI()/180 / 2),2) + COS(lat1 * PI()/180 ) * COS( ABS ( lat2 ) *  PI()/180) * POWER(SIN((lng1 - lng2) *  PI()/180 / 2), 2) )), 2);

and then it's pretty easy to use it:

SELECT
    distance( city1.latitude, city1.longtitude, city2.latitude, city2.longtitude) AS distanceKM
FROM
    city1, city2 ...

You can read here how the cordinates to kilometers are being converted

Note: 6371 is the Earth radius in kilometers so if you need to the distance in miles you will have to replace it with 3959

Upvotes: 4

Muhammad Shoaib
Muhammad Shoaib

Reputation: 745

Have a look this link.This may help you.

Calculate Distance.

Upvotes: 3

Related Questions