Closest locations to a point#

Here’s a PostgreSQL SQL query that returns the closest locations to a point, based on a brute-force approach where the database calculates the distance (in miles) to every single row and then sorts by that distance.

It’s adapted from this StackOverflow answer, which helpfully points out that if you want kilometers rather than miles you can swap the 3959 constant for 6371 instead.

There are much more efficient ways to do this if you are using PostGIS, described in this Nearest-Neighbour Searching tutorial - but if you’re not using PostGIS this works pretty well.

I ran this against a table with over 9,000 rows and got results back in less than 20ms.

1
with locations_with_distance as (
2
  select
3
    *,
4
    (
5
      acos (
6
        cos (
7
          radians(%(latitude)s::float)
8
        ) * cos(
9
          radians(latitude)
10
        ) * cos(
11
          radians(longitude) - radians(%(longitude)s::float)
12
        ) + sin(
13
          radians(%(latitude)s::float)
14
        ) * sin(radians(latitude))
15
      ) * 3959
16
    ) as distance_miles
17
  from
18
    location
19
)
20
select
21
  *
22
from
23
  locations_with_distance
24
order by
25
  distance_miles
26
limit
27
  20

The %(latitude)s and %(longitude)s bits are named parameters when working with the Python psycopg2 library - they also work with django-sql-dashboard which I used to prototype this query.

1
from django.db.models import F
2
from django.db.models.functions import ACos, Cos, Radians, Sin
3

4
locations = Location.objects.annotate(
5
    distance_miles = ACos(
6
        Cos(
7
            Radians(input_latitude)
8
        ) * Cos(
9
            Radians(F('latitude'))
10
        ) * Cos(
11
            Radians(F('longitude')) - Radians(input_longitude)
12
        ) + Sin(
13
            Radians(input_latitude)
14
        ) * Sin(Radians(F('latitude')))
15
    ) * 3959
16
).order_by('distance_miles')[:10]