python - Filter zipcodes by proximity in Django with the Spherical Law of Cosines

Question

I'm trying to handle proximity search for a basic store locater in Django. Rather than haul PostGIS around with my app just so I can use GeoDjango's distance filter, I'd like to use the Spherical Law of Cosines distance formula in a model query. I'd like all of the calculations to be done in the database in one query, for efficiency.

An example MySQL query from The Internet implementing the Spherical Law of Cosines like this:

SELECT id, ( 
    3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * 
    cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * 
    sin( radians( lat ) ) ) 
) 
AS distance FROM stores HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

The query needs to reference the Zipcode ForeignKey for each store's lat/lng values. How can I make all of this work in a Django model query?

Answer 1

To follow up on Tom's answer, it won't work in SQLite by default because of SQLite's lack of math functions by default. No problem, it's pretty simple to add:

class LocationManager(models.Manager):
    def nearby_locations(self, latitude, longitude, radius, max_results=100, use_miles=True):
        if use_miles:
            distance_unit = 3959
        else:
            distance_unit = 6371

        from django.db import connection, transaction
        from mysite import settings
        cursor = connection.cursor()
        if settings.DATABASE_ENGINE == 'sqlite3':
            connection.connection.create_function('acos', 1, math.acos)
            connection.connection.create_function('cos', 1, math.cos)
            connection.connection.create_function('radians', 1, math.radians)
            connection.connection.create_function('sin', 1, math.sin)

        sql = """SELECT id, (%f * acos( cos( radians(%f) ) * cos( radians( latitude ) ) *
        cos( radians( longitude ) - radians(%f) ) + sin( radians(%f) ) * sin( radians( latitude ) ) ) )
        AS distance FROM location_location WHERE distance < %d
        ORDER BY distance LIMIT 0 , %d;""" % (distance_unit, latitude, longitude, latitude, int(radius), max_results)
        cursor.execute(sql)
        ids = [row[0] for row in cursor.fetchall()]

        return self.filter(id__in=ids)