Guides‎ > ‎

Haversine Formula (Distance Query) with Django/PostgreSQL

I needed to run a query in Django to find users which have latitudes and longitudes near a given point. Google has an example for MySQL at http://code.google.com/apis/maps/articles/phpsqlsearch.html#findnearsql which doesn't work as-is in Postgres. Greg Smith provides an almost working adaptation at http://www.mail-archive.com/pgsql-general@postgresql.org/msg136564.html. In the end, I used:

SELECT * FROM
  (SELECT id, lat, lon, (3959 * acos(cos(radians(%s)) * cos(radians(lat)) *
                                     cos(radians(lon) - radians(%s)) +
                                     sin(radians(%s)) * sin(radians(lat))))
   AS distance
   FROM accounts_userlocation) AS distances
WHERE distance < 20
ORDER BY distance
OFFSET 0
LIMIT 20;

Don't forget to use the params list instead of string formatting as explained at http://docs.djangoproject.com/en/1.2/topics/db/sql/#passing-parameters-into-raw.
Comments