13Oct

Django-Zipdistance

Posted by Elf Sternberg as Design, programming, python

Recently, I had the pleasure of attending another of those Seattle Django meet-ups.  This one was a potpourri event, just people talking about what they knew and how they knew it.   I revealed that I’d written my first aggregator, and that seemed to be an impressive statement.  Apparently Django Aggregators (database conditionals that perform sub-selected summarizing or filtering events) is something of a black art, much like WordPress Treewalkers were a black art I figured out in just a few hours.

Aggregators consist of two parts: The Definition and the Implementation.  Unfortunately, Django’s idea is that these are two different objects, bound together not by inheritance but by aggregation (both the definition and the implementation are assembled in a generic context, one providing access to the ORM and the other to the SQL).   The definition is used by the ORM to track the existence and name of the aggregate, and is then used to invoke the implemenation, which in turn creates the raw SQL that will be added to the SQL string ultimately sent to the server, and ultimately parsed by the ORM.

I needed to use aggregation because I wanted to say, “For any two points’ latitude and longitude, give me the great circle distance between them,” and then say, “For a point (X, Y) on a map, give me every other place in the database within n miles great circle distance.”

The latter was not possible with Django’s Queryset.extra() feature.    You can add a WHERE clause, but not a HAVING clause, and this definitely requires a HAVING clause when running on MySQL.  Using an Aggregator with a limit forces the ORM to realize it needs a HAVING clause.  Besides, it was a good excuse to learn the basics of Aggregation. Ultimately, I was able to do what the task required: find the distance between any two US Zip Code regions without making third-party requests.

I make absolutely no promises that this code is useful to anyone else.  The Aggregator is definitely not pretty: it’s virtually a raw SQL injector.  But it was fun.  Enjoy: Django-ZipDistance.

Comment Form

Subscribe to Feed

Categories

Calendar

October 2010
M T W T F S S
« Sep   Nov »
 123
45678910
11121314151617
18192021222324
25262728293031