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

It's possible the execute raw SQL queries in Django.

My suggestion is, write the query to pull a list of IDs (which it looks like you're doing now), then use the IDs to pull the associated models (in a regular, non-raw-SQL Django query). Try to keep your SQL as dialect-independent as possible, so that you won't have to worry about one more thing if you ever have to switch databases.

To clarify, here's an example of how to do it:

def get_models_within_25 (self):
    from django.db import connection, transaction
    cursor = connection.cursor()

    cursor.execute("""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;""")
    ids = [row[0] for row in cursor.fetchall()]

    return MyModel.filter(id__in=ids)

As a disclaimer, I can't vouch for this code, as it's been a few months since I've written any Django, but it should be along the right lines.