How to search nearby location with kilometer in MySQL

Question

I have the database like as below structure. And how can I get location_id in list within 5 kilometer. There have latitude and longitude numbers are already in the database table. Please see my database structure image.

- school_id
- location_id
- school_name
- lat
- lng

Here is the database structure image:

I have already searched from this link How to find nearest location using latitude and longitude from sql database? and i don't understand the code.

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;

Answer 1

The constant literal 3959 represents an approximation of the radius of the earth, in miles. That's why the "great circle distance" expression is returning a value in miles.

To get distance in kilometers, just replace 3959 with 6371, an approximation of the earth's radius in km.

Reference: https://en.wikipedia.org/wiki/Great-circle_distance

---

What the query is doing is calculating a distance (in miles) between two points on the earth, represented by degrees latitude and degrees longitude.

One the points is represented by literal values in the GCD expression (37.000000,-122.000000). The other point is (lat,lng) (degrees latitude and degrees longitude) from the row in the database.

The query cranks through every row in the table, and evaluates the GCD expression to calculate a distance. (The length of shortest line along the surface of the sphere between the two points.)

The HAVING distance < 25 clause excludes any row where the calculated distance is either greater than or equal to 25 or NULL.

The ORDER BY distance clause returns the rows in sequence by ascending values of distance, the closest points first.

The LIMIT 20 clause restricts the return to the first twenty rows.

---

FOLLOWUP

Within five kilometers of what? The Santa Monica Pier Aquarium?

That's latitude 34.010396, longitude -118.496029.

We can set user-defined variables (to avoid spreading literals in our query text):

 SET @lat =   34.010396 ;
 SET @lng = -118.496029 ;

Our SQL text include in the SELECT list the columns we want to return from our table. We'll also included a complicated looking "Great Circle Distance" expression that returns a distance in kilometers.

Something Like this:

 SELECT m.school_id
      , m.location_id
      , m.school_name
      , m.lat
      , m.lng

      , ( ACOS( COS( RADIANS( @lat  ) ) 
              * COS( RADIANS( m.lat ) )
              * COS( RADIANS( m.lng ) - RADIANS( @lng ) )
              + SIN( RADIANS( @lat  ) )
              * SIN( RADIANS( m.lat ) )
          )
        * 6371
        ) AS distance_in_km

  FROM mytable m
 ORDER BY distance_in_km ASC
 LIMIT 100

The GCD formula in the expression is calculating a distance between two points.

In this query, one of the points is a constant (@lat,@lng), which we previously set to the coordinates of the Santa Monica Pier Aquarium.

The other point is (m.lat,m.lng), the latitude and longitude from the row in the table.

So in this query, distance_in_km represents the distance between (lat,lng) of the row in the table and the Santa Monica Pier Aquarium.

Because distance_in_km value is not available at the time the rows are accessed, we can't reference that in a WHERE clause.

But we can reference it in a HAVING clause. That's simliar to a WHERE in that it filters out rows, but is much different, because it is evaluated much later in the query execution. And it can reference expressions that aren't available when the rows are being accessed, when the WHERE clause is evaluated.

We can modify our query to include the HAVING clause. In this case, we're limiting to rows that are within 100 kilometers, and we'll return only the closest 12 rows...

  FROM mytable m
HAVING distance_in_km <= 100
 ORDER BY distance_in_km ASC
 LIMIT 12

If we want to find the distance to some point other than the Santa Monica Pier, we set @lat and @lng for that point, and re-execute the SQL.

Answer 2

SELECT *, ((ACOS(SIN(inputLat * PI() / 180) * 
SIN(tableColLat * PI() / 180) + COS(inputLat * PI() / 180) * 
COS(tableColLat * PI() / 180) * COS((inputLng - tableColLng) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) 
as distance FROM gm_shop HAVING distance <= 5 ORDER BY distance ASC;

distance is you can change. it is KM