使用球面余弦法在Django中按距离过滤邮政编码

Question

我正在尝试在Django中处理一个简单的店铺定位功能，想要实现附近搜索。为了使用GeoDjango的距离过滤器，我不想把PostGIS这个工具带到我的应用里。于是，我想在模型查询中使用球面余弦定律的距离公式。我希望所有的计算都能在数据库中一次性完成，这样效率会更高。

网上有一个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;

这个查询需要引用每个店铺的邮政编码外键，以获取它们的经纬度值。我该如何在Django模型查询中实现这一切呢？

Answer 1

接着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条件中就无法使用它。

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)

Answer 3

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

我的建议是，先写一个查询来获取一系列的ID（看起来你现在就是这么做的），然后再用这些ID去获取相关的模型（使用普通的Django查询，而不是原始SQL查询）。尽量让你的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了，但大致方向应该是对的。