Python-SqlAlchemy : Filter query by great circle distance?

Question

我正在使用 Python 和 Sqlalchemy 在 Sqlite 数据库中存储纬度和经度值。我创建了一个 hybrid method对于我的 Location 对象，

@hybrid_method
def great_circle_distance(self, other):
    """
    Tries to calculate the great circle distance between the two locations

    If it succeeds, it will return the great-circle distance
    multiplied by 3959, which calculates the distance in miles.

    If it cannot, it will return None.

    """
    return math.acos(  self.cos_rad_lat 
                     * other.cos_rad_lat 
                     * math.cos(self.rad_lng - other.rad_lng)
                     + self.sin_rad_lat
                     * other.sin_rad_lat
                     ) * 3959

cos_rad_lat 和sin_rad_lat 等所有值都是我预先计算的值，以优化计算。无论如何，当我运行以下查询时，

pq = Session.query(model.Location).filter(model.Location.great_circle_distance(loc) < 10)

我得到以下错误，

line 809, in great_circle_distance
    * math.cos(self.rad_lng - other.rad_lng)
TypeError: a float is required

当我打印 self.rad_lng 和 other.rad_lng 的值时，例如，

self.rad_lng: Location.rad_lng 
other.rad_lng: -1.29154947064

我做错了什么？

Answer 1

你不能真的那样使用 math 模块:

>>> c = toyschema.Contact()
>>> c.lat = 10
>>> c.lat
10
>>> import math
>>> math.cos(c.lat)
-0.83907152907645244
>>> math.cos(toyschema.Contact.lat)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: a float is required

您将在 @great_circle_distance.expression method 中组合 sqalchemy.func.* 代替 math.*对于所有这种聪明才智。不幸的是，你也不能用 sqlite 做到这一点；它doesn't provide trig functions您可以使用 PostgreSQL，它可以，或者您可以尝试 add these functions to sqlite yourself:

编辑 向 sqlite 添加函数实际上并不难:这未测试。

必须将数学函数添加到 sqlite:

engine = sqlalchemy.create_engine("sqlite:///:memory:/")
raw_con = engine.raw_connection()
raw_con.create_function("cos", 1, math.cos)
raw_con.create_function("acos", 1, math.acos)

class Location(...):
    ...
    @hybrid_method
    def great_circle_distance(self, other):
        """
        Tries to calculate the great circle distance between 
        the two locations by using the Haversine formula.

        If it succeeds, it will return the Haversine formula
        multiplied by 3959, which calculates the distance in miles.

        If it cannot, it will return None.

        """
        return math.acos(  self.cos_rad_lat 
                         * other.cos_rad_lat 
                         * math.cos(self.rad_lng - other.rad_lng)
                         + self.sin_rad_lat
                         * other.sin_rad_lat
                         ) * 3959

    @great_circle_distance.expression
    def great_circle_distance(cls, other):
        return sqlalchemy.func.acos(  cls.cos_rad_lat 
                         * other.cos_rad_lat 
                         * sqlalchemy.func.cos(cls.rad_lng - other.rad_lng)
                         + cls.sin_rad_lat
                         * other.sin_rad_lat
                         ) * 3959