使用余弦定理球面公式在Django中按接近程度过滤邮政编码

Question

我正在尝试在Django中为基本的店铺定位器处理近距离搜索。我不想使用PostGIS来使用GeoDjango的距离过滤器，而是想在模型查询中使用球面余弦定理距离公式。我希望所有计算都在数据库中一次性完成以提高效率。

互联网上有一个示例MySQL查询，使用球面余弦定理如下：

SELECT id, ( 
    3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * 
    cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * 
    sin( radians( lat ) ) ) 
) 
AS distance FROM stores HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

查询需要引用每个商店的Zipcode外键来获取其经纬度值。我该如何在Django模型查询中使所有这些工作正常运行?

- spiffytech

---

(a) 那不是“haversine”公式，而是“球面余弦定理”公式；请参考（例如）http://www.movable-type.co.uk/scripts/latlong.html并查看相关的维基百科文章。 (b) 我相信你会用变量替换硬编码的用户坐标 :-) (c) 温馨提示读者，你所使用的距离单位有些过时（与罗马军团标准步伐长度的1000倍有关，我相信） :-) - John Machin

天啊，GeoDjango 有什么难的？只需要安装它 :) - Andrew Johnson

Answer 1

在Django中，可以执行原始的SQL查询。

我的建议是，编写一个查询来获取ID列表（看起来你现在正在这样做），然后使用这些ID来获取相关的模型（在常规的、非原始的SQL Django查询中）。尽量使你的SQL与方言无关，这样如果你需要切换数据库，就不用再担心其他事情了。

为了澄清，这里有一个示例：

def get_models_within_25 (self):
    from django.db import connection, transaction
    cursor = connection.cursor()

    cursor.execute("""SELECT id, ( 
        3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * 
        cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * 
        sin( radians( lat ) ) ) )
        AS distance FROM stores HAVING distance < 25
        ORDER BY distance LIMIT 0 , 20;""")
    ids = [row[0] for row in cursor.fetchall()]

    return MyModel.filter(id__in=ids)

作为免责声明，我不能保证这段代码的准确性，因为我已经有几个月没有编写 Django 代码了，但它应该是正确的方向。

- jakeboxer

---

它运行得非常好，只需要三引号（或转换为单个字符串）。 - Tom

这只是跟进一下。原始查询返回一个“距离”字段（显示两组日志/纬度之间的距离）。如何执行第二部分，但带有这个额外的“距离”字段？ - dotty

我假设"ids = [row[0] for row in cursor.fetchall()] return MyModel.filter(id__in=ids)"从第一个查询中获取了所有的IDS，然后使用Django的ORM来选择模型中的所有行。 - dotty

Answer 2

回复Tom的答案，SQLite默认情况下不起作用，因为SQLite默认情况下缺少数学函数。 没问题，很容易添加：

class LocationManager(models.Manager):
    def nearby_locations(self, latitude, longitude, radius, max_results=100, use_miles=True):
        if use_miles:
            distance_unit = 3959
        else:
            distance_unit = 6371

        from django.db import connection, transaction
        from mysite import settings
        cursor = connection.cursor()
        if settings.DATABASE_ENGINE == 'sqlite3':
            connection.connection.create_function('acos', 1, math.acos)
            connection.connection.create_function('cos', 1, math.cos)
            connection.connection.create_function('radians', 1, math.radians)
            connection.connection.create_function('sin', 1, math.sin)

        sql = """SELECT id, (%f * acos( cos( radians(%f) ) * cos( radians( latitude ) ) *
        cos( radians( longitude ) - radians(%f) ) + sin( radians(%f) ) * sin( radians( latitude ) ) ) )
        AS distance FROM location_location WHERE distance < %d
        ORDER BY distance LIMIT 0 , %d;""" % (distance_unit, latitude, longitude, latitude, int(radius), max_results)
        cursor.execute(sql)
        ids = [row[0] for row in cursor.fetchall()]

        return self.filter(id__in=ids)

- Clint Sharp

---

这似乎像是魔法，它是如何工作的？我猜这相当低效？ - nisc

可以通过使用 'from django.conf import settings' 来使 'from mysite import settings' 更加通用化。 - Aaron C. de Bruyn

Answer 3

关于Tom的问题，如果你想要一个在postgresql中也能工作的查询，不能使用AS语句，否则会出现“distance不存在”的错误。

应该将整个球面法表达式放在WHERE语句中，就像这样（在mysql中也适用）：

import math
from django.db import connection, transaction
from django.conf import settings

from django .db import models

class LocationManager(models.Manager):
    def nearby_locations(self, latitude, longitude, radius, use_miles=False):
        if use_miles:
            distance_unit = 3959
        else:
            distance_unit = 6371

        cursor = connection.cursor()

        sql = """SELECT id, latitude, longitude FROM locations_location WHERE (%f * acos( cos( radians(%f) ) * cos( radians( latitude ) ) *
            cos( radians( longitude ) - radians(%f) ) + sin( radians(%f) ) * sin( radians( latitude ) ) ) ) < %d
            """ % (distance_unit, latitude, longitude, latitude, int(radius))
        cursor.execute(sql)
        ids = [row[0] for row in cursor.fetchall()]

        return self.filter(id__in=ids)

请注意，您必须选择纬度和经度，否则无法在WHERE子句中使用。

- specialunderwear

Answer 4

为了跟进jboxer的答案，这里提供一个自定义管理器的完整示例，其中一些硬编码的内容已经转换成了变量：

class LocationManager(models.Manager):
    def nearby_locations(self, latitude, longitude, radius, max_results=100, use_miles=True):
        if use_miles:
            distance_unit = 3959
        else:
            distance_unit = 6371

        from django.db import connection, transaction
        cursor = connection.cursor()

        sql = """SELECT id, (%f * acos( cos( radians(%f) ) * cos( radians( latitude ) ) *
        cos( radians( longitude ) - radians(%f) ) + sin( radians(%f) ) * sin( radians( latitude ) ) ) )
        AS distance FROM locations_location HAVING distance < %d
        ORDER BY distance LIMIT 0 , %d;""" % (distance_unit, latitude, longitude, latitude, int(radius), max_results)
        cursor.execute(sql)
        ids = [row[0] for row in cursor.fetchall()]

        return self.filter(id__in=ids)

- Tom

Answer 5

根据jboxer的回复

def find_cars_within_miles_from_postcode(request, miles, postcode=0):

    # create cursor for RAW query
    cursor = connection.cursor()

    # Get lat and lon from google
    lat, lon = getLonLatFromPostcode(postcode)

    # Gen query
    query = "SELECT id, ((ACOS(SIN("+lat+" * PI() / 180) * SIN(lat * PI() / 180) + COS("+lat+" * PI() / 180) * COS(lat * PI() / 180) * COS(("+lon+" - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM app_car HAVING distance<='"+miles+"' ORDER BY distance ASC"

    # execute the query
    cursor.execute(query)

    # grab all the IDS form the sql result
    ids = [row[0] for row in cursor.fetchall()]

    # find cars from ids
    cars = Car.objects.filter(id__in=ids)

    # return the Cars with these IDS
    return HttpResponse( cars )

这个函数可以返回我的汽车在x英里范围内，效果很好。但是原始查询返回了它们距离某个位置的距离，我想字段名是“distance”。

我该如何将这个“distance”字段与我的汽车对象一起返回？

- dotty

Answer 6

使用Django的数据库函数也可以实现这一点，这意味着您可以使用.annotate()调用添加一个distance_miles列，然后按照它进行排序。以下是一个示例：

from django.db.models import F
from django.db.models.functions import ACos, Cos, Radians, Sin

locations = Location.objects.annotate(
    distance_miles = ACos(
        Cos(
            Radians(input_latitude)
        ) * Cos(
            Radians(F('latitude'))
        ) * Cos(
            Radians(F('longitude')) - Radians(input_longitude)
        ) + Sin(
            Radians(input_latitude)
        ) * Sin(Radians(F('latitude')))
    ) * 3959
).order_by('distance_miles')[:10]

- Simon Willison

Answer 7

使用上面提供的一些答案，我得到了不一致的结果，因此我决定再次检查公式，使用 [这个链接]http://www.movable-type.co.uk/scripts/latlong.html 作为参考，该公式为 d = acos(sin(lat1)*sin(lat2) + cos(lat1)*cos(lat2)*cos(lon2-lon1) ) * 6371，其中d是要计算的距离，lat1,lon1是基点的坐标，lat2,lon2是其他点的坐标，我们的情况下是数据库中的点。

根据以上答案，LocationManager 类如下。

class LocationManager(models.Manager):
def nearby_locations(self, latitude, longitude, radius, max_results=100, use_miles=True):
    if use_miles:
        distance_unit = 3959
    else:
        distance_unit = 6371

    from django.db import connection, transaction
    from mysite import settings
    cursor = connection.cursor()
    if settings.DATABASE_ENGINE == 'sqlite3':
        connection.connection.create_function('acos', 1, math.acos)
        connection.connection.create_function('cos', 1, math.cos)
        connection.connection.create_function('radians', 1, math.radians)
        connection.connection.create_function('sin', 1, math.sin)

    sql = """SELECT id, (acos(sin(radians(%f)) * sin(radians(latitude)) + cos(radians(%f))
          * cos(radians(latitude)) * cos(radians(%f-longitude))) * %d)
    AS distance FROM skills_coveragearea WHERE distance < %f
    ORDER BY distance LIMIT 0 , %d;""" % (latitude, latitude, longitude,distance_unit, radius, max_results)
    cursor.execute(sql)
    ids = [row[0] for row in cursor.fetchall()]

    return self.filter(id__in=ids)

使用网站[链接]http://www.movable-type.co.uk/scripts/latlong.html进行检查，我的结果是一致的。

- gbozee

Answer 8

@classmethod def nearby_locations(cls, latitude, longitude, radius, max_results=1000, use_miles=False): if use_miles: distance_unit = 3959 else: distance_unit = 6371000

from django.db import connection, transaction
from django.conf import settings
cursor = connection.cursor()

sql = """SELECT id, (%f * acos( cos( radians(%f) ) * cos( radians( latitude ) ) *
cos( radians( longitude ) - radians(%f) ) + sin( radians(%f) ) * sin( radians( latitude ) ) ) )
AS distance FROM yourapp_yourmodel 
GROUP BY id, latitude, longitude
HAVING (%f * acos( cos( radians(%f) ) * cos( radians( latitude ) ) *
cos( radians( longitude ) - radians(%f) ) + sin( radians(%f) ) * sin( radians( latitude ) ) ) ) < %d
ORDER BY distance OFFSET 0 LIMIT %d;""" % (distance_unit, latitude, longitude, latitude, distance_unit, latitude, longitude, latitude, int(radius), max_results)

cursor.execute(sql)
ids = [row[0] for row in cursor.fetchall()]

return cls.objects.filter(id__in=ids)

- Abel Ayalew