[Django] #25499: Distance lookup not possible with a column value as distance
Django
----------------------------------+--------------------
Reporter: iambibhas | Owner:
Type: New feature | Status: new
Component: contrib.postgres | Version: master
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+--------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25499>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: iambibhas | Owner: nobody
Type: New feature | Status: new
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by claudep):
* needs_better_patch: => 0
* component: contrib.postgres => GIS
* needs_tests: => 0
* owner: => nobody
* needs_docs: => 0
* stage: Unreviewed => Accepted
Comment:
I didn't investigate yet, but supporting this use case does make sense.
--
Ticket URL: <https://code.djangoproject.com/ticket/25499#comment:1>
Django
Reporter: iambibhas | Owner: claudep
Type: New feature | Status: assigned
Component: GIS | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by claudep):
* owner: nobody => claudep
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/25499#comment:2>
Django
Reporter: iambibhas | Owner: claudep
Type: New feature | Status: assigned
Component: GIS | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by claudep):
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/25499#comment:3>
Django
Reporter: iambibhas | Owner: claudep
Type: New feature | Status: assigned
Component: GIS | Version: master
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/25499#comment:4>
Django
Reporter: iambibhas | Owner: claudep
Component: GIS | Version: master
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Claude Paroz <claude@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"37d06cfc46679759c47178a3380b12fbf22a4f25" 37d06cfc]:
{{{
#!CommitTicketReference repository=""
revision="37d06cfc46679759c47178a3380b12fbf22a4f25"
Fixed #25499 -- Added the ability to pass an expression in distance
lookups
Thanks Bibhas Debnath for the report and Tim Graham for the review.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25499#comment:5>
Django
Reporter: iambibhas | Owner: claudep
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* status: closed => new
* cc: jtiai (added)
* keywords: => 1.10
* has_patch: 1 => 0
* resolution: fixed =>
* stage: Ready for checkin => Accepted
Comment:
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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25499#comment:6>
Django
Reporter: iambibhas | Owner: claudep
Severity: Normal | Resolution:
Keywords: 1.10 | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by claudep):
Someone can try:
{{{
#!diff
diff --git a/django/contrib/gis/db/backends/oracle/operations.py
b/django/contrib/gis/db/backends/oracle/operations.py
index b443ae0..bb98846 100644
--- a/django/contrib/gis/db/backends/oracle/operations.py
+++ b/django/contrib/gis/db/backends/oracle/operations.py
@@ -28,7 +28,7 @@ class SDOOperator(SpatialOperator):
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):
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25499#comment:7>
Django
Reporter: iambibhas | Owner: claudep
Severity: Normal | Resolution:
Keywords: 1.10 | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"00db71d452d04e417bcc2a88757af1c9e22b8e74" 00db71d]:
{{{
#!CommitTicketReference repository=""
revision="00db71d452d04e417bcc2a88757af1c9e22b8e74"
Refs #25499 -- Fixed expressions in distance lookups on Oracle.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25499#comment:8>
Django
Reporter: iambibhas | Owner: claudep
Component: GIS | Version: master
Severity: Normal | Resolution: fixed
Keywords: 1.10 | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* status: new => closed
* resolution: => fixed
Comment:
Thanks Claude, that test now passes locally for me on Oracle.
--
Ticket URL: <https://code.djangoproject.com/ticket/25499#comment:9>
Django
Reporter: Bibhas C Debnath | Owner: Claude Paroz
Type: New feature | Status: closed
Component: GIS | Version: master
Severity: Normal | Resolution: fixed
Keywords: 1.10 | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"bb9e82f2748ace292a584841ab9af8696df27f53" bb9e82f2]:
{{{
#!CommitTicketReference repository=""
revision="bb9e82f2748ace292a584841ab9af8696df27f53"
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25499#comment:10>