Расчет расстояния SqlAlchemy / Sqlite

Question

Расчет расстояния SqlAlchemy / Sqlite

Я использую 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)

Это, кажется, возвращает объекты в правильном порядке, но у меня нет доступа к атрибуту расстояния. Как я могу получить доступ к этой ценности - или каков наилучший способ ее достижения?

591 3

python sqlite sqlalchemy geospatial

3 ответов:

Comments

Ничего не найдено.

John Machin · Accepted Answer · 2011-03-12 09:06:01

Задача 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, которые не поддерживайте sin / cos/tan и их инверсии из коробки.

Проблема 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>
Возможно, вам следует подробнее остановиться на "этот Из, кажется, возвращает объекты в правильном порядке, но у меня нет доступа к атрибуту расстояния" ... что вам говорит интроспекция e?

Kylotan · Accepted Answer · 2017-05-23 13:26:49

Вы можете найти некоторую помощь здесь: столбец пользовательского запроса SQLAlchemy

Sarge · Accepted Answer · 2011-03-10 19:04:51

Как указывал Джон Мэчин, ваш алгоритм расстояния немного странный и приведет к неверным результатам, если ваши точки находятся более чем, грубо говоря, в 100 км друг от друга.

Вам нужно преобразовать в метры (или футы и т. д.), Чтобы получить разумный результат. Ищите алгоритмы Хаверсина или Винсенти. Лучше посмотрите, есть ли у SQLAlchemy что-то полезное для вас.