Postgres: Search and Sort by Radial Distance

Posted on December 11, 2013 • 1 minutes • 189 words

Table of contents

Data
SQL

Request users by radial distance and sort by nearest to farthest, vice versa, or whatever other attributes you’d like to include.

Searching for a user by distance, in your database, is easy with Postgres. Just create a users table with, at minimum, columns named latitude and longitude.

Data

postgres=# SELECT * FROM users;
+----+------+----------+-----------+
| id | name | latitude | longitude |
+----+------+----------+-----------+

SQL

SELECT name, distance
FROM
( SELECT name, ((ACOS(SIN(#{latitude} * PI() / 180) * SIN(u.latitude * PI() / 180) + COS(#{latitude} * PI() / 180) * COS(u.latitude * PI() / 180) * COS((#{longitude} - u.longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) as distance
FROM users u ) d
WHERE distance <= 5
ORDER BY distance ASC;

Matches all users WHERE distance <= 5 (less than or equal to five miles).

To change the distance to kilometers—multiply the output number by 1.609344 (1 mile == 1.609344 kilometers).

Replace #{latitude} with the value for the latitude you want to match against the database and do the same for #{longitude}

This will work in any SQL relational database, including MySQL and Oracle.