SqlAlchemy / Sqlite 距离计算

Question

我正在使用sqlAlchemy ORM，并希望计算并返回给定点与存储点之间的距离。

class Event(Base):

    __tablename__ = 'events'
    # Schema
    id = Column(Integer, primary_key=True)
    title = Column(String(150))
    description = Column(Text)
    url = Column(String(800))
    lat = Column(Float)
    lng = Column(Float)

...以及我的查询：

    nearest = """SELECT *, ((lat - '-41.288889') * (lat - '-41.288889')
 + (lng  - 174.777222) * (lng  - 174.777222)) AS distance FROM events 
ORDER BY distance ASC """

e = Event.query.from_statement(nearest)

这个方法似乎按正确的顺序返回对象，但我无法访问距离属性。如何访问这个值-或者完成这个任务的最佳实践是什么？

- reech

Answer 1

问题1：纬度和经度的长度不同...在赤道上（假设地球是一个球体）它们相同，但在极点，一个经度的长度为零。

更新：为了举例说明问题的严重性：

向南或向北移动一度纬度大约相当于行驶111.2公里（假设地球是一个球体）。向东或向西移动一度经度在赤道上大约相当于行驶111.2公里。在纬度为-41.3度时，只有83.5公里。

从(-41.3,174.8)到东边的(-41.3,174.92)距离为10.0公里。您的计算将其视为13.3公里——错误率达33%。

通过一个相当简单的近似值，您可以达到4米的误差。

from math import pi, sqrt, radians, cos
Earth_radius_km = 6371.009
km_per_deg_lat = 2 * pi * Earth_radius_km / 360.0

# what your SQL query is in effect doing
def approx_dist_1(lat1, lon1, lat2, lon2):
    return km_per_deg_lat * sqrt((lat1 - lat2) ** 2 + (lon1 - lon2) ** 2)

# better version    
def approx_dist_2(lat1, lon1, lat2, lon2):
    # calculate km_per_deg_lon for your central station in Python and 
    # embed it in your query
    km_per_deg_lon = km_per_deg_lat * cos(radians(lat1))
    return sqrt((km_per_deg_lat *(lat1 - lat2)) ** 2 + (km_per_deg_lon * (lon1 - lon2)) ** 2)

这个大概的距离对于“寻找最近的披萨店”等应用程序来说足够好，而且它的优点是可以在像SQLite这样不支持正弦/余弦/正切及其反函数的基本环境中使用。

问题2：SQLite是宽容的，但你不应该养成使用引号的习惯，比如 (lat - '-41.288889')

问题3：我无法在SQLlite级别上重现你的问题：

sqlite> create table foo (lat float, lon float);
sqlite> insert into foo values(99.9, -170.1);
sqlite> select * from foo;
99.9|-170.1
sqlite> SELECT *, ((lat - '-41.288889') * (lat - '-41.288889')
   ...>  + (lon  - 174.777222) * (lon  - 174.777222)) AS distance from foo;
99.9|-170.1|138874.600631492
sqlite>

也许您应该详细说明“This of seems to return the objects in the correct order, but I have no access to the distance attribute”... introspecting e 告诉您什么？

也许您应该进一步解释“这似乎按正确顺序返回对象，但我无法访问距离属性”的含义... introspecting e 对您有何启示？

- John Machin

---

嗨 - 首先，这是一个 sqlAlchemy 特定的问题。除了具体的实现之外，我真正需要的是能够通过 sqlAlchemy ORM 查询方法访问“计算出”的列（派生列？）的能力 - 即某种将距离附加到每个返回对象的方式。e 是一个 sqlAlchemy 查询对象。 - reech

@reech：尽管如此，我们仍然感到有必要指出，鉴于您的“基站”的纬度，您的排序键可能会偏差多达33%。 - John Machin

Answer 2

你可能会在这里找到一些帮助：SQLAlchemy自定义查询列

- Kylotan

Answer 3

正如John Machin所指出的那样，您的距离算法有些奇怪，如果您的点之间相距超过大约100公里，将会导致不正确的结果。

您需要转换为米（或英尺等）才能得到合理的结果。寻找Haversine或Vincenty算法。更好的方法是查看SQLAlchemy是否对您有用的东西。

- Sarge

---

正如我所解释的那样，距离并不是主要问题。请查看我的修订答案。 - John Machin