具有距离值的GeoDjango距离滤波器存储在模型中 - 查询 [英] GeoDjango distance filter with distance value stored within model - query
问题描述
我有一个订单
模型,它有一个来源
PointField和范围
IntegerField。此外,还有一个 UserProfile
模型,它有一个 geo_location
PointField。现在,我有一个用户
实例,用户
。我想选择所有订单
,其距离 Order.origin
和 user.userprofile之间的距离。地理位置
小于 Order.range
模型字段中的值(米)。
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.
再次简化的模型:
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)))
我的下一个(不成功)尝试使用 F( )表达式使用 Order.range
字段中的值:
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>", line 1, in <module>
File "/Users/danger/devel/.virtualenvs/proj/lib/python2.7/site-packages/django/contrib/gis/measure.py", line 165, in __init__
self.m, self._default_unit = self.default_units(kwargs)
File "/Users/danger/devel/.virtualenvs/proj/lib/python2.7/site-packages/django/contrib/gis/measure.py", line 49, in default_units
if not isinstance(value, float): value = float(value)
TypeError: float() argument must be a string or a number
我认为问题是D()不是懒惰地运行 - 我可以理解。
所以我试图从原来的$ code>范围字段(整数),我应该工作,但是:
I think the problem is that the D() isn't ran lazily - I can understand that.
So I tried to just take the raw value from the range
field (integer) which I supposed to work, but:
>>> Order.objects.filter(origin__distance_lte=(user.profile.geo_location, F('range')))
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/Users/danger/devel/.virtualenvs/proj/lib/python2.7/site-packages/django/db/models/query.py", line 69, in __repr__
data = list(self[:REPR_OUTPUT_SIZE + 1])
File "/Users/danger/devel/.virtualenvs/proj/lib/python2.7/site-packages/django/db/models/query.py", line 84, in __len__
self._result_cache.extend(self._iter)
File "/Users/danger/devel/.virtualenvs/proj/lib/python2.7/site-packages/django/db/models/query.py", line 273, in iterator
for row in compiler.results_iter():
File "/Users/danger/devel/.virtualenvs/proj/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 680, in results_iter
for rows in self.execute_sql(MULTI):
File "/Users/danger/devel/.virtualenvs/proj/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 725, in execute_sql
sql, params = self.as_sql()
File "/Users/danger/devel/.virtualenvs/proj/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 68, in as_sql
where, w_params = self.query.where.as_sql(qn=qn, connection=self.connection)
File "/Users/danger/devel/.virtualenvs/proj/lib/python2.7/site-packages/django/db/models/sql/where.py", line 92, in as_sql
sql, params = child.as_sql(qn=qn, connection=connection)
File "/Users/danger/devel/.virtualenvs/proj/lib/python2.7/site-packages/django/db/models/sql/where.py", line 95, in as_sql
sql, params = self.make_atom(child, qn, connection)
File "/Users/danger/devel/.virtualenvs/proj/lib/python2.7/site-packages/django/contrib/gis/db/models/sql/where.py", line 47, in make_atom
spatial_sql = connection.ops.spatial_lookup_sql(data, lookup_type, params_or_value, lvalue.field, qn)
File "/Users/danger/devel/.virtualenvs/proj/lib/python2.7/site-packages/django/contrib/gis/db/backends/postgis/operations.py", line 531, in spatial_lookup_sql
raise ValueError('Argument type should be %s, got %s instead.' % (arg_type, type(value[1])))
ValueError: Argument type should be (<class 'decimal.Decimal'>, <class 'django.contrib.gis.measure.Distance'>, <type 'float'>, <type 'int'>, <type 'long'>), got <class 'django.db.models.expressions.F'> instead.
那么我该怎么做呢?任何帮助是赞赏!
So how can I accomplish what I am trying to? Any help is appreciated!
推荐答案
我想你将不得不删除一些SQL来做你想要的,因为GeoDjango帮助者有一种方法可以制作一个合适的距离
对象,它同时是Django F
对象(即字段查找)。你不会说你使用哪个数据库,但是在PostGIS中你可以这样做:
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()
允许您添加额外的字段和限制进查询;这里我们添加一个限制 -
ST_Distance()
是PostGIS函数,GeoDjangodistance_lte
operator convert into(from Django文档) -
ST_PointFromText()
将WKT语法转换为PostGIS几何对象 -
4326
是 Django的默认SRID ,但不适用于PostGIS,所以我们必须指定它 - 我们必须
CAST
你的领域要加倍精度,因为你使用一个整数 - 那么我们必须除以1000,从米转换成公里,我 它给出了他们的WKT表示,我们之前在
ST_PointFromText()
调用
.extra()
allows you to add extra fields and restrictions into a query; here we're adding a restrictionST_Distance()
is the PostGIS function that the GeoDjangodistance_lte
operator converts into (from the Django documentation)ST_PointFromText()
converts from WKT syntax into a PostGIS Geometry object4326
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 ourST_PointFromText()
call
请注意,根据PostGIS文档, ST_Distance()
不使用索引,因此您可能需要使用 ST_DWithin()
(在 ST_Distance()
之后)记录。
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距离滤波器存储在模型中 - 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!