Distance lookup not possible with a column value as distance

[Bibhas C Debnath]

Right now this is possible -

current_localities = Locality.objects.filter(centroid__distance_lte=(pnt, 1000))

which results in -

(0.005) SELECT "map_locality"."id", "map_locality"."name", "map_locality"."slug", "map_locality"."centroid", "map_locality"."radius" FROM "map_locality" WHERE ST_Distan
ce_Sphere("map_locality"."centroid", ST_GeomFromEWKB('\x0101000020e6100000865ad3bce3685340e9b7af03e7ec2940'::bytea)) <= 1000 LIMIT 21;

But if I have a column named radius which has the radius for each Locality and centroid has the center point and I want to find all the localities that my current location falls in, I want to do something like this -

current_localities = Locality.objects.filter(centroid__distance_lte=(pnt, F('radius')))

But it's not possible right now. It generates the error -

ProgrammingError at /api/services/
can't adapt type 'F'

But in reality, this is possible -

SELECT "map_locality"."id", "map_locality"."name", "map_locality"."slug", "map_locality"."centroid", "map_locality"."radius" FROM "map_locality" WHERE ST_Distance_Sphere("map_locality"."centroid", ST_GeomFromEWKB('\x0101000020e6100000865ad3bce3685340e9b7af03e7ec2940'::bytea)) <= radius*1000 LIMIT 21;

Here I'm using radius*1000 as the result of ST_Distance_Sphere() is in meter and my radius value is in km. I'm looking for suggestions on how to handle cases like this in the ORM, but I do think that the support for using a column value using F() would be *really* helpful.

[Claude Paroz]

I didn't investigate yet, but supporting this use case does make sense.

[Claude Paroz <claude@…>]

In 37d06cfc:

Fixed #25499 -- Added the ability to pass an expression in distance lookups

Thanks Bibhas Debnath for the report and Tim Graham for the review.

[Tim Graham]

This needs to be addressed on Oracle GIS. gis_tests.distapp.tests.DistanceTest.test_distance_lookups_with_expression_rhs fails like so:

Traceback (most recent call last):
  File "/mnt/jenkinsdata/workspace/pull-requests-oracle/database/oragis11/python/python3.5/django/test/testcases.py", line 1068, in skip_wrapper
    return test_func(*args, **kwargs)
  File "/mnt/jenkinsdata/workspace/pull-requests-oracle/database/oragis11/python/python3.5/tests/gis_tests/distapp/tests.py", line 332, in test_distance_lookups_with_expression_rhs
    self.get_names(qs),
  File "/mnt/jenkinsdata/workspace/pull-requests-oracle/database/oragis11/python/python3.5/tests/gis_tests/distapp/tests.py", line 34, in get_names
    cities = [c.name for c in qs]
  File "/mnt/jenkinsdata/workspace/pull-requests-oracle/database/oragis11/python/python3.5/django/db/models/query.py", line 258, in __iter__
    self._fetch_all()
  File "/mnt/jenkinsdata/workspace/pull-requests-oracle/database/oragis11/python/python3.5/django/db/models/query.py", line 1074, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/mnt/jenkinsdata/workspace/pull-requests-oracle/database/oragis11/python/python3.5/django/db/models/query.py", line 52, in __iter__
    results = compiler.execute_sql()
  File "/mnt/jenkinsdata/workspace/pull-requests-oracle/database/oragis11/python/python3.5/django/db/models/sql/compiler.py", line 839, in execute_sql
    cursor.execute(sql, params)
  File "/mnt/jenkinsdata/workspace/pull-requests-oracle/database/oragis11/python/python3.5/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/mnt/jenkinsdata/workspace/pull-requests-oracle/database/oragis11/python/python3.5/django/db/backends/oracle/base.py", line 477, in execute
    query, params = self._fix_for_params(query, params)
  File "/mnt/jenkinsdata/workspace/pull-requests-oracle/database/oragis11/python/python3.5/django/db/backends/oracle/base.py", line 473, in _fix_for_params
    query = convert_unicode(query % tuple(args), self.charset)
TypeError: not enough arguments for format string

[Claude Paroz]

Someone can try:

django/contrib/gis/db/backends/oracle/operations.py

@@ -28 +28 @@
 
 
 class SDODistance(SpatialOperator):
-    sql_template = "SDO_GEOM.SDO_DISTANCE(%%(lhs)s, %%(rhs)s, %s) %%(op)s %%%%s" % DEFAULT_TOLERANCE
+    sql_template = "SDO_GEOM.SDO_DISTANCE(%%(lhs)s, %%(rhs)s, %s) %%(op)s %%(value)s" % DEFAULT_TOLERANCE
 
 
 class SDODWithin(SpatialOperator):

[Tim Graham <timograham@…>]

In 00db71d:

Refs #25499 -- Fixed expressions in distance lookups on Oracle.

[Tim Graham]

Thanks Claude, that test now passes locally for me on Oracle.

[Mariusz Felisiak <felisiak.mariusz@…>]

In bb9e82f2:

Fixed #29955 -- Added support for distance expression to the dwithin lookup.

This was missed when adding support to other distance lookups in
refs #25499.

Thanks Peter Bex for the report and Mariusz for testcases.