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.
