How to find closest locations using coordinates with SQL

Many existing apps nowadays are offering the ability to display nearest records, these records can be user profiles, actual places or anything that can be linked to a location. This is certainly a useful feature and there is a high chance you’d need to make such an implementation, either for a client, or for your own project.

The possibilities are endless, but I’m going to give you an example from a recent project I’ve been working on. A client approached me with a request to extend the networking area on their website. The work included many different features, one of them was “Find nearest profiles” where we needed to enable existing users to find other profiles within a certain radius. Obviously, we needed to have existing user locations stored in our database, so we had to use Google Geocoding API to extract coordinates from user addresses and store them in a dedicated database table with the profile ID for each record. The following steps included setting up the interface to allow users to search using their own address, custom address, or geolocation ( current address retrieved with JS via the browser’s Geolocation API ). When the search button is clicked, we take the submitted data, convert it to coordinates (latitude and longitude), and run a SQL query to retrieve the nearest profiles based on the provided coordinates & radius, prepare the results and show them to the user.

The Query

To keep this tutorial short, we’ll not go much into the details of how to set up the database structure, store coordinates, or use a specific programming language to connect to the database. We’ll only cover the SQL part responsible for searching and finding the nearest record.

We’ll assume you have a simple database table named `locations` that looks like this:

ID	profile_id	latitude	longitude
1	100	37.423021	-122.083739
2	200	3.688855	-11.894535

Note: the `latitude` and `longitude` database columns should be set with the correct datatype to store coordinates correctly. They should work fine with `FLOAT( 10, 8 )` for both of them, or `DECIMAL(10,8)` for latitude and `DECIMAL(11,8)` for longitude.

Now let’s assume the user searched using the following address:

• 1600 Amphitheatre Parkway, Mountain View, CA

After converting it to coordinates, this becomes:

• Latitude: 37.423021
• Longitude: -122.083739

After extracting coordinates from the search address, we’ll use it to build our SQL query.

SELECT 
profile_id, 
(
   3959 *
   acos(cos(radians(37.423021)) * 
   cos(radians(lat)) * 
   cos(radians(lng) - 
   radians(-122.083739)) + 
   sin(radians(37.423021)) * 
   sin(radians(lat )))
) AS distance 
FROM locations
HAVING distance < 100 
ORDER BY distance;

The SQL query above perform some calculations and retrieve all the nearest profile IDs within a radius of 100 miles to `37.423021`, `-122.083739` coordinates. It will order the results by distance, showing the closest results first.

Note 1: to search by KM instead of Mile, replace `3959` with `6371`.

Note 2: to limit results, simply add the a limit clause at the end of the query, eg; `LIMIT 20`

Note 3: to search within a specific range, replace `HAVING distance < 100` with `HAVING distance < 100 and distance > 50`, obviously after changing to the suitable range.

Bonus for WordPress

If you need to add search by location to a WordPress website, via a theme or plugin, this is a helper function that help you retrieve search the results by only providing latitude, longitude and range.

function ak_get_nearest_profiles($lat, $lng, $range)
{

    if ($lat && $lng && $range) {

        global $wpdb;
        $table = $wpdb->prefix . 'locations';
        $sql = $wpdb->prepare("SELECT profile_id, 
        (
            3959 * acos(cos(radians(%s)) * cos(radians(lat)) * cos(radians(lng) - radians(%s)) +  sin(radians(%s)) * sin(radians(lat)))
        ) AS distance 
        FROM `$table`
        HAVING distance < %d 
        ORDER BY distance;", $lat, $lng, $lat, $range);

        $result = $wpdb->get_results($sql);

        $found_ids = array();
        if (is_array($result) && !empty($result)) {
            foreach ($result as $profile) {
                $found_ids[] = (int)$profile->profile_id;
            }
        }

        return $found_ids;
    }


    return array();
}

That’s it, happy coding!