Python - SqlAlchemy:按大圆距离过滤查询? [英] Python - SqlAlchemy: Filter query by great circle distance?

问题描述

我使用 Python 和 Sqlalchemy 在 Sqlite 数据库中存储纬度和经度值.我为我的位置对象创建了一个 混合方法，

@hybrid_methoddef great_circle_distance(self, other):"""尝试计算两个位置之间的大圆距离如果成功，则返回大圆距离乘以 3959，即计算出以英里为单位的距离.如果不能，它将返回 None."""返回 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)

我收到以下错误，

第809行，在great_circle_distance中* math.cos(self.rad_lng - other.rad_lng)类型错误:需要一个浮点数

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

self.rad_lng:Location.rad_lngother.rad_lng:-1.29154947064

我做错了什么?

解决方案

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

<预><代码>>>>c = toyschema.Contact()>>>c.lat = 10>>>纬度10>>>导入数学>>>math.cos(c.lat)-0.83907152907645244>>>math.cos(toyschema.Contact.lat)回溯(最近一次调用最后一次):文件<stdin>"，第 1 行，在 <module> 中类型错误:需要一个浮点数

您将在 @great_circle_distance.expression 方法一种聪明.不幸的是，你也不能用 sqlite 做到这一点；它不提供触发函数您可以使用 PostgreSQL，它可以，或者您可以尝试自己将这些函数添加到 sqlite:

编辑向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)类位置(...):...@hybrid_methoddef great_circle_distance(self, other):"""尝试计算之间的大圆距离两个位置使用Haversine 公式.如果成功，它将返回Haversine公式乘以 3959，即计算出以英里为单位的距离.如果不能，它将返回 None."""返回 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.expressiondef great_circle_distance(cls, other):返回 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

I am using Python and Sqlalchemy to store latitude and longitude values in a Sqlite database. I have created a hybrid method for my Location object,

@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

All the values like cos_rad_lat and sin_rad_lat are values I pre-calculated to optimize the calculation. Anyhow, when I run the following query,

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

I get the following error,

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

When I print the values for self.rad_lng and other.rad_lng I get, for example,

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

What am I doing wrong?

解决方案

You can't really use the math module that way:

>>> 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

You'll have combine sqalchemy.func.* in place of math.* in a @great_circle_distance.expression method for all of that kind of cleverness. Unfortunately, you can't do that with sqlite, either; it doesn't provide trig functions You could use PostgreSQL, which does, or you can try to add these functions to sqlite yourself:

EDIT It's actually not to hard to add functions to sqlite: This is NOT tested.

Have to add the math functions to 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

这篇关于Python - SqlAlchemy:按大圆距离过滤查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！