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
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
%(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.
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