Django filter on annotation from custom aggregate function

Question

I'm trying to find all zipcodes within a certain distance of a given zipcode. I'm doing this by calculating distance using a custom-made aggregate function, annotating the queryset with the distance, and filtering the queryset based on that 'distance' field.

The aggregate function calculates the distance correctly, and the annotation creates the 'distance' field in the queryset correctly. However, the filter always returns an empty queryset. It works fine when I filter using other fields such as 'zipcode' or 'state', but returns empty when I use the annotated 'distance' value as the filter value. What am I doing wrong?

Here is the custom aggregate function:

from django.db import models
from django.db.models import Aggregate
from django.db.models.sql.aggregates import Aggregate as AggregateImpl

class DistanceFromImpl(AggregateImpl): 
sql_function = ''
is_computed = True
is_ordinal = True

sql_template = ('3959 * acos( cos( radians(%(t_lat)f) ) * cos( radians( latitude ) ) * '
                'cos( radians( longitude ) - radians(%(t_lon)f) ) + sin( radians(%(t_lat)f) ) * '
                'sin( radians( latitude ) ) )')

def __init__(self, col, target, **extra): 
    self.col = col
    self.target = target
    self.extra = extra 

def _default_alias(self): 
    return '%s__%s' % (str(self.target), self.__class__.__name__.lower()) 

default_alias = property(_default_alias) 

def add_to_query(self, query, alias, col, source, is_summary): 
    super(DistanceFrom, self).__init__(col, source, is_summary, **self.extra) 
    query.aggregate_select[alias] = self

def as_sql(self, qn, connection):
    "Return the aggregate, rendered as SQL."

    return self.sql_template % { 't_lon': self.target.longitude,
                                 't_lat': self.target.latitude }


class DistanceFrom(Aggregate):
name="DistanceFromImpl"

def add_to_query(self, query, alias, col, source, is_summary):
    aggregate = DistanceFromImpl(col, source=source, is_summary=is_summary, **self.extra)
    query.aggregates[alias] = aggregate

I grabbed this and the other code from here: https://github.com/elfsternberg/django-zipdistance/blob/master/zipdistance/models.py

My model for a zipcode is called ZipDistance. I can easily get a queryset with annotated distances from a given ZipDistance. So this works fine:

>>> zip1 = ZipDistance.objects.get(zipcode='01234')
>>> qs = ZipDistance.objects.annotate(distance=DistanceFrom('zipcode', target=zip1))
>>> qs[1].distance
5  # Second entry in queryset is 5 miles away from zipcode '01234'

But any filtering by distance always returns empty:

>>> qs.filter(distance__lte=99999)
[]

I'm using my own fixture to populate my database (which is MySQL). The problem could be that I'm using Django version 1.5, and the code was written for an earlier version. I'm just not sure, I've been trying everything I can think of for a few days now.

Answer 1

Still not sure what was causing this error, but I got around it by writing my own SQL code in a method:

from django.db import models, connection, transaction
from math import sin, cos, radians, acos

class ZipDistance(models.Model):
    zipcode = models.CharField(max_length=5, unique=True)
    state_short = models.CharField(max_length=2)
    latitude = models.FloatField()
    longitude = models.FloatField()
    province = models.CharField(max_length=50)
    state_long = models.CharField(max_length=20)

    def get_zips_within(self, dist):
        cursor = connection.cursor()
        cursor.execute("""SELECT id, (
                          3959 * acos( cos( radians(%s) ) * cos( radians( latitude ) ) *
                          cos( radians( longitude ) - radians(%s) ) + sin( radians(%s) ) *
                          sin( radians( latitude ) ) ) )
                          AS distance FROM myapp_zipdistance
                          HAVING distance <= %s""",
                       [self.latitude, self.longitude, self.latitude, dist])
        ids = [row[0] for row in cursor.fetchall()]
        return ZipDistance.objects.filter(id__in=ids)

Now, all I have to do to get a list of zipcodes within a certain distance is the following:

>>> zip1 = ZipDistance.objects.get(zipcode='20001')
>>> zip_list = zip1.get_zips_within(dist=100)

This gives me a list of all the zipcodes in my database within 100 miles of Washington D.C. (zipcode 20001)