Custom SQL for Geodjango on ForignKey

Question

I have a following model:

class UserProfile(models.Model):
    user = models.OneToOneField(User)
    location = models.PointField(blank=True, null=True, srid=CONSTANTS.SRID)

    objects = models.GeoManager()

class Item(models.Model):
    owner = models.ForeignKey(UserProfile)

    objects = models.GeoManager()

Now I need to sort the Items by distance to some point:

p = Point(12.5807203, 50.1250706)
Item.objects.all().distance(p, field='owner__location')

But that throws me an error:

TypeError: ST_Distance output only available on GeometryFields.

From GeoDjango GeoQuerySet.distance() results in 'ST_Distance output only available on GeometryFields' when specifying a reverse relationship in field_name I can see there is already ticket for this.

Now I don't like the solution proposed in that question since that way I would not get the distance and I would lose the distances.

So I was thinking that I could achieve this by making a custom sql query. I know that this:

UserProfile.objects.distance(p)

will produce something like this:

SELECT (ST_distance_sphere("core_userprofile"."location",ST_GeomFromEWKB('\x0101000020e6100000223fd12b5429294076583c5002104940'::bytea))) AS "distance", "core_userprofile"."id", "core_userprofile"."user_id", "core_userprofile"."verified", "core_userprofile"."avatar_custom", "core_userprofile"."city", "core_userprofile"."location", "core_userprofile"."bio" FROM "core_userprofile"

So my question is: is there some easy way how to manually construct such query that would sort items by distance?

score 1 · Accepted Answer · answered Feb 12 '15 at 05:41

Since the geometry you're measuring distance to is on UserProfile, it makes sense to query for UserProfile objects and then handle each Item object they own. (The distance is the same for all items owned by a profile.)

For example:

all_profiles = UserProfile.objects.all()
for profile in all_profiles.distance(p).order_by('distance'):
   for item in profile.item_set.all():
       process(item, profile.distance)

You may be able to make this more efficient with prefetch_related:

all_profiles = UserProfile.objects.all()
all_profiles = all_profiles.prefetch_related('item_set')  # we'll need these
for profile in all_profiles.distance(p).order_by('distance'):
   for item in profile.item_set.all():  # items already prefetched
       process(item, profile.distance)

If it's important for some reason to query directly for Item objects, try using extra:

items = Item.objects.all()
items = items.select_related('owner')
distance_select = "st_distance_sphere(core_userprofile.location, ST_GeomFromEWKT('%s'))" % p.wkt
items = items.extra({'distance': distance_select})
items = items.order_by('distance')

Raw queries are another option, which let you get model objects from a raw SQL query:

items = Item.objects.raw("SELECT core_item.* FROM core_item JOIN core_userprofile ...")

Custom SQL for Geodjango on ForignKey

1 Answers1