# 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