Django按distan排序

2024-06-13 17:04:40 发布

您现在位置:Python中文网/ 问答频道 /正文

我有以下型号:

class Vacancy(models.Model):
    lat = models.FloatField('Latitude', blank=True)
    lng = models.FloatField('Longitude', blank=True)

我应该如何查询以按距离排序(距离是无限的)?

在PosgreSQL上工作,GeoDjango如果需要的话。


Tags: true距离model排序modelsclasslnglat
3条回答

这是一个不需要GeoDjango的解决方案。

from django.db import models
from django.db.models.expressions import RawSQL


class Location(models.Model):
    latitude = models.FloatField()
    longitude = models.FloatField()
    ...


def get_locations_nearby_coords(latitude, longitude, max_distance=None):
    """
    Return objects sorted by distance to specified coordinates
    which distance is less than max_distance given in kilometers
    """
    # Great circle distance formula
    gcd_formula = "6371 * acos(least(greatest(\
    cos(radians(%s)) * cos(radians(latitude)) \
    * cos(radians(longitude) - radians(%s)) + \
    sin(radians(%s)) * sin(radians(latitude)) \
    , -1), 1))"
    distance_raw_sql = RawSQL(
        gcd_formula,
        (latitude, longitude, latitude)
    )
    qs = Location.objects.all() \
    .annotate(distance=distance_raw_sql))\
    .order_by('distance')
    if max_distance is not None:
        qs = qs.filter(distance__lt=max_distance)
    return qs

使用如下:

nearby_locations = get_locations_nearby_coords(48.8582, 2.2945, 5)

如果使用sqlite,则需要添加

import math
from django.db.backends.signals import connection_created
from django.dispatch import receiver


@receiver(connection_created)
def extend_sqlite(connection=None, **kwargs):
    if connection.vendor == "sqlite":
        # sqlite doesn't natively support math functions, so add them
        cf = connection.connection.create_function
        cf('acos', 1, math.acos)
        cf('cos', 1, math.cos)
        cf('radians', 1, math.radians)
        cf('sin', 1, math.sin)
        cf('least', 2, max)
        cf('greatest', 2, min)

首先,最好建立一个点域,而不是将lat和lnt分开:

from django.contrib.gis.db import models

location = models.PointField(null=False, blank=False, srid=4326, verbose_name="Location")

然后,可以这样过滤:

from django.contrib.gis.geos import *
from django.contrib.gis.measure import D

distance = 2000 
ref_location = Point(1.232433, 1.2323232)

res = yourmodel.objects.filter(location__distance_lte=(ref_location, D(m=distance))).distance(ref_location).order_by('distance')

在django>;=1.9中删除.distance(ref_location)应该改用注释。

from django.contrib.gis.db.models.functions import Distance
from django.contrib.gis.measure import D
from django.contrib.gis.geos import Point

ref_location = Point(1.232433, 1.2323232, srid=4326)
yourmodel.objects.filter(location__distance_lte=(ref_location, D(m=2000)))                                                     
    .annotate(distance=Distance("location", ref_location))                                                                
    .order_by("distance")

此外,还应使用使用空间索引的dwithin运算符缩小搜索范围,distance不使用减慢查询速度的索引:

yourmodel.objects.filter(location__dwithin=(ref_location, 0.02))
    .filter(location__distance_lte=(ref_location, D(m=2000)))
    .annotate(distance=Distance('location', ref_location))
    .order_by('distance')

有关location__dwithin=(ref_location, 0.02)的解释,请参见this post

相关问题 更多 >