Styjun

I have a situation where I have a model called trip. Each trip has a departure_airport and an arrival_airport which are related fields and both part of the airport model. Each object in the airport model has a location represented by latitude and longitude fields.

I need to be able to take inputs from two (potentially) separate departure and arrival airport locations using something like the Haversine formula. That formula would calculate the distance from each departure/arrival airport in the database to the location of the airports that have been taken as input.

The difficult part of this query is that I annotate the trip queryset with the locations of the departure and arrival airports, however because there's two sets of latitude/longitude fields (one for each airport) with the same name and you can't use annotated fields in a sql where clause, I'm not able to use both sets of airports in a query.

I believe the solution is to use a subquery on the annotated fields so that query executes before the where clause, however I've been unable to determine if this is possible for this query. The other option is to write raw_sql.

Here's what I have so far:

GCD_FORMULA_TO = """3961 * acos(
 cos(radians(%s)) * cos(radians(arrival_lat))
 * cos(radians(arrival_lon) - radians(%s)) +
 sin(radians(%s)) * sin(radians(arrival_lat)))""" 

GCD_FORMULA_FROM = """3961 * acos(
 cos(radians(%s)) * cos(radians(departure_lat))
 * cos(radians(departure_lon) - radians(%s)) +
 sin(radians(%s)) * sin(radians(departure_lat)))"""

location_to = Q(location_to__lt=self.arrival_airport_rad)
location_from = Q(location_from__lt=self.departure_airport_rad)

qs = self.queryset
 .annotate(arrival_lat=F('arrival_airport__latitude_deg'))
 .annotate(arrival_lon_to=F('arrival_airport__longitude_deg'))
 .annotate(departure_lat=F('departure_airport__latitude_deg'))
 .annotate(longitude_lon=F('departure_airport__longitude_deg'))
 .annotate(location_to=RawSQL(GCD_FORMULA_TO, (self.arrival_airport.latitude_deg, self.arrival_airport.longitude_deg, 
 self.arrival_airport.latitude_deg)))
 .annotate(location_from=RawSQL(self.GCD_FORMULA_FROM, (self.departure_airport.latitude_deg, self.departure_airport.longitude_deg, 
 self.departure_airport.latitude_deg)))
 .filter(location_to and location_from)

return qs

Any ideas? Also open to other ways to go about this.

You're doing this the hard way.

If your python code has a pair of locations, use this:

from geopy.distance import distance

loc1 = (lat1, lng1)
loc2 = (lat2, lng2)
d = distance(loc1, loc2).km

If you're querying a database, perhaps you would prefer that it runs PostGIS / Postgres, rather than mysql,
so you can compute distance and shape membership.
The syntax sometimes is on the clunky side, but the indexing works great.
Here is an example for departing from London Heathrow:

SELECT a.airport_name,
 ST_Distance('SRID=4326; POINT(-0.461389 51.4775)'::geography,
 ST_Point(a.longitude, a.latitude)) AS distance
FROM arrival_airports a
ORDER BY distance;

As a separate matter, you might consider defining an arrival and/or departure VIEW on your table, and then JOIN, with a distinct model for each view.