在geodjango中按距离（整个表）排序的效率如何 [英] How efficient is it to order by distance (entire table) in geodjango
问题描述
假设我具有以下数据模型
Assume that I have the following data model
Person(models.Model):
id = models.BigAutoField(primary_key=True)
name = models.CharField(max_length=50)
location = models.PointField(srid=4326)
还假设我有一个应用程序对此django后端进行查询，并且该应用程序的唯一目的是从最远到最远返回一个（分页）注册用户列表。 。
Assume also that I have an app that makes queries to this django backend, and the only purpose of this app is to return a (paginated) list of registered users from closest to farthest.
当前我在考虑这个查询：
Currently I have this query in mind:
# here we are obtaining all users in ordered form
current_location = me.location
people = Person.objects.distance(current_location).order_by('distance')
# here we are obtaining the first X through pagination
start_index = a
end_index = b
people = people[a:b]
虽然可以，但是速度不如我想要的快。
Although this works, it is not as fast as I would like.
我对速度该查询。如果表很大（超过一百万），则数据库（Postgres SQL w / PostGIS）不必测量 current_location 与每个之间的距离位置，然后对随后的100万行执行 order_by ？
I have some concerns over the speed of this query. If the table were large (1 million+) then wouldn't the database (Postgres SQL w/ PostGIS) have to measure the distance between current_location and every location in the database before performing an order_by on that subsequently 1 million rows?
有人可以建议如何有效地按距离适当返回附近的用户吗？
Can somebody suggest on how to properly return nearby users ordered by distance in an efficient manner?
推荐答案
如果要对每个条目进行排序在桌子上按距离进行操作，那么它会按预期的那样慢，并且什么也做不了（我现在已经知道和我的知识了。）！
If you want to sort every entry on that table by distance then it will be slow as expected and there is nothing that can be done (that I am aware of at this point of time and my knowledge.)!
您可以按照以下步骤并做一些假设来提高计算效率：
You can make your calculation more efficient by following this steps and making some assumptions:
Enable spatial indexing on your tables. To do that in GeoDjango, follow the doc instructions and fit them to your model:
注意
Note
在PostGIS中，ST_Distance_Sphere不限制执行地理距离查询的几何类型。 [4] 但是，这些查询可能需要很长时间，因为必须为查询中的每一行动态计算大圆距离。这是因为不能使用传统几何字段上的空间索引。
In PostGIS, ST_Distance_Sphere does not limit the geometry types geographic distance queries are performed with. [4] However, these queries may take a long time, as great-circle distances must be calculated on the fly for every row in the query. This is because the spatial index on traditional geometry fields cannot be used.
要在WGS84距离查询上获得更好的性能，请考虑使用地理列，因为它们能够使用其空间索引在距离查询中。您可以通过在字段定义中设置 geography = True 来告诉GeoDjango使用地理列。
For much better performance on WGS84 distance queries, consider using geography columns in your database instead because they are able to use their spatial index in distance queries. You can tell GeoDjango to use a geography column by setting geography=True in your field definition.
现在您可以使用一些逻辑约束来缩小查询范围：
Now you can narrow down your query with some logical constrains:
Ex：我的用户不会寻找人们距他当前位置超过50公里。
Ex: My user will not look for people more than 50km from his current position.
使用 dwithin 空间查找，它利用了上述空间索引，因此速度非常快。
Narrow down the search using dwithin spatial lookup which utilizes the above mentioned spatial indexing, therefore it is pretty fast.
最后在其余行上按距离排序。
Finally apply the distance order by on the remaining rows.
最终查询如下：
current_location = me.location
people = People.objects.filter(
location__dwithin=(current_location, D(km=50))
).annotate(
distance=Distance('location', current_location)
).order_by('distance')
PS ：而不是创建自定义分页尝试，利用为django视图提供的分页方法更有效：
P.S: Rather than creating a custom pagination attempt, it is more efficient to utilize the pagination methods provided for the django views:
- Docs
您可以使用Django Rest Framework并使用它的分页：
Or you can use Django Rest Framework and use it's pagination:
- Docs and a DRF pagination Q&A example
这篇关于在geodjango中按距离（整个表）排序的效率如何的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
