GeoDjango distance filter with distance value stored within model - query

Question

I have an `Order` model, that has an `origin` PointField and a `range` IntegerField. Furthermore, there is an `UserProfile` model, which has a `geo_location` PointField. Now, I have an `User` instance, `user`. I want to select all `Orders`, whose distance between `Order.origin` and `user.userprofile.geo_location` is less then the value (meters) in the `Order.range` model field. So again, simplified models: ``` class Order(models.Model): origin = models.PointField() range = models.IntegerField(blank=True, default=10000) class UserProfile(models.Model): geo_location = models.PointField() ``` I've got this working (passing the distance statically): ``` >>> Order.objects.filter(origin__distance_lte=(user.profile.geo_location, D(m=3000))) ``` My next (unsuccessful) try was to use an F() expression to use the value from the `Order.range` field: ``` >>> Order.objects.filter(origin__distance_lte=(user.profile.geo_location, D(m=F('range')))) Traceback (most recent call last): File "<console>",…

Accepted Answer

I think you're going to have to drop some SQL in to do what you want, as the GeoDjango helpers don't have a way of you making a suitable `Distance` object that is simultaneously a Django `F` object (ie field lookup). You don't say which database you're using, but here's how you do it with PostGIS:

```
Order.objects.all().extra(
    where=['ST_Distance(origin, ST_PointFromText(%s, 4326)) <= CAST(range AS double precision) / 1000'],
    params=[user.profile.geo_location.wkt]
)
```

Let's explain what's happening here, because it's pretty thorny. Going from the left:

- `.extra()` allows you to add extra fields and restrictions into a query; here we're adding a restriction

- `ST_Distance()` is the PostGIS function that the GeoDjango `distance_lte` operator converts into (from the Django documentation)

- `ST_PointFromText()` converts from WKT syntax into a PostGIS Geometry object

- `4326` is the default SRID for Django, but not for PostGIS so we have to specify it

- we have to `CAST` your field to double precision because you're using an integer

- then we have to divide by 1000 to convert from metres into kilometres, which I believe is what we need

- GeoDjango `Point` fields have an accessor `.wkt` which gives their WKT representation, which we needed earlier in our `ST_PointFromText()` call

Note that according to the PostGIS documentation, `ST_Distance()` doesn't use indexes, so you may want to investigate using `ST_DWithin()` instead (it's documented right after `ST_Distance()`).

GeoDjango distance filter with distance value stored within model - query

Solution

Problem

Related problems