SqlAlchemy / Sqlite Distance Calculation

Question

I am using sqlAlchemy ORM and would like to calculate and return the distance from a given point and stored points.

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)

.... and my request:

    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)

This seems to return the objects in the correct order, but I don't have access to the distance attribute. How do I access this value - or what is the best way to accomplish this?

Answer 1

Problem 1: degrees of latitude and longitude have different lengths ... the same at the equator (assuming the earth is a sphere), but at the poles longitude has zero length.

Update To give an example of the severity of your problem:

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?

Answer 2

: SQLAlchemy

Answer 3

, , , , 100 .

( ..), . . , , SQLAlchemy - .