SqlAlchemy / Sqlite Distance calculation

Question

I'm using sqlAlchemy ORM and would like to like to calculate and return the distance from a given point and the 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 query:

    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 of seems to return the objects in the correct order, but I have no access to the distance attribute. How do I access this value - or what is the best-practice way of accomplishing this?

Answer 1

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

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

Travelling north or south by one degree of latitude will cover about 111.2 km (assuming that the earth is a sphere). Travelling east or west by one degree of longitude will cover about 111.2 km at the equator. At a latitude of -41.3 degrees, it will cover only 83.5 km.

From (-41.3, 174.8) due east to (-41.3, 174.92) is 10.0 km. Your calculation treats it as though it is 13.3 km away -- an error of 33%.

You can get to within a 4 metre error by a fairly simple approximation:

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)

This approximate distance is good enough for "find the nearest pizza shop" applications, and has the advantage that it can be used in bare-bones environments like SQLite which don't support sin/cos/tan and their inverses out of the box.

Problem 2: SQLite is tolerant, but you shouldn't make a habit of using quotes as in (lat - '-41.288889')

Problem 3: I can't reproduce your problem at the SQLlite level:

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>

Perhaps you should elaborate on "This of seems to return the objects in the correct order, but I have no access to the distance attribute" ... what does introspecting e tell you?