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.

with locations_with_distance as (
  select
    *,
    (
      acos (
        cos (
          radians(%(latitude)s::float)
        ) * cos(
          radians(latitude)
        ) * cos(
          radians(longitude) - radians(%(longitude)s::float)
        ) + sin(
          radians(%(latitude)s::float)
        ) * sin(radians(latitude))
      ) * 3959
    ) as distance_miles
  from
    location
)
select
  *
from
  locations_with_distance
order by
  distance_miles
limit
  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.

Translated to Django

Here's that same formula using the Django ORM:

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

locations = Location.objects.annotate(
    distance_miles = ACos(
        Cos(
            Radians(input_latitude)
        ) * Cos(
            Radians(F('latitude'))
        ) * Cos(
            Radians(F('longitude')) - Radians(input_longitude)
        ) + Sin(
            Radians(input_latitude)
        ) * Sin(Radians(F('latitude')))
    ) * 3959
).order_by('distance_miles')[:10]

Created 2021-03-22T22:19:31-07:00, updated 2021-03-23T13:00:03-07:00 · History · Edit

Closest locations to a point

Translated to Django

Related