Some of the functionality of my cycle computer that I really like is the ability to see currently accumulated height and distance. So obviously this is something I wanted to add to hypecycle.
Initially I found a great function in the python gpxpy which would give me a bunch of data on the current ride including distance covered, height climbed and decended, and avg/max speeds. This was perfect and gave me all that I needed. Unfortunately...
The problem with this function is that it requires a GPX data file object and that means that each time I want to calculate these totals I need to pull all the data for the current ride out of the DB and then convert it to GPX format to use in this library. This was fine when I was doing small test rides with only a 100 points or so, but you can imagine how this would quickly get out of control and not be something we can do every couple of seconds (which would be desireable!).
So now...
The new idea is to use the underlying distance and height functions in the gpxpy library to get a distance and height differential between two GPS points and save that as part of our GPS reading in the DB. So the GPS table will now have a "distance_to_prev" and "height_to_prev" column.
To calculate the distance and height to our previous point, we do something like this in our data recording task:
active_ride = await Rides.objects.filter(active=True).get_or_none()
if active_ride:
# Fetch the last location row from DB if it exists
prev_location = await active_ride.gpsreadingss.order_by(Gpsreadings.id.desc()).limit(1).get_or_none()
if not prev_location:
# If there is no previous location, default to zeros
distance_to_prev = 0.0
height_to_prev = 0.0
else:
if state.fix_quality: # Only run the calculation if we have GPS fix, otherwise assume no movement.
distance_to_prev = geo.distance(latitude_2=state.latitude, longitude_2=state.longitude, elevation_2=state.altitude, latitude_1=prev_location.latitude, longitude_1=prev_location.longitude, elevation_1=prev_location.altitude)
if distance_to_prev > 50: # if we have moved more than 50m in 1 second, we are moving at > 180km/h, so assume GPS issues and that we aren't moving.
distance_to_prev = 0.0
# Get the height differential and make sure previous altitude is not none
height_to_prev = state.altitude - (prev_location.altitude if prev_location.altitude else 0)
else: # No active ride so set things to zero
distance_to_prev = 0.0
height_to_prev = 0.0
The most important bit here is the fetching of the previous row of GPS data. We want to make sure we only get one row and that it was the latest one entered into the DB. In the case we just started a ride and there is no reading, then we want to just get a None. To do this we can use the .order_by() and limit() functions of [ormar](https://collerek.github.io/ormar/). Like so:
active_ride.gpsreadingss.order_by(Gpsreadings.id.desc()).limit(1).get_or_none()
This will grab all the gpsreadings data associated with our active_ride and then sort it in descending order based on the primary key id. The descending order allows us to use a .get_or_none() to grab the last entry we put into the DB. We then just limit() it to 1 row and voila!!
Once we have our current location and our previous location we can just run them through the gpxpy.geo.distance() library function and it will give us an accurate 3D distance between our two GPS points and we can just wack that piece of data into our Gpsreadings table when we write the new row.
So now after adding our height and distance to previous location, our Gpsreadings object has the following form:
{
"id": 279,
"timestamp": "2022-11-12T12:11:36.637816",
"latitude": 41.41247666666667,
"longitude": 2.1527533333333335,
"altitude": 122.90926850399954,
"speed": 1.51,
"distance_to_prev": 46.10144481042731,
"height_to_prev": -45.27135085785788,
"ride_id": {
"id": 3
}
}
Now, when we want to get the distance of the current ride we just need to take the sum of all the entries in the distance_to_prev column. For this we use the SUM aggregation function in our ORM lib. Since we have defined a relationship between Rides and GPSreadings in our DB we can do this very simply with:
active_ride = await Rides.objects.filter(active=True).get_or_none()
distance = await Gpsreadings.objects.filter(ride_id=active_ride.id).sum("distance_to_prev")
This returns us a nice floating point number in meters which. We can do the same thing with "height_to_prev", but that is not exactly what we want, since for height we will have both positive and negative values.
What we really want here is to get the amount we have ridden uphill and downhill. To do this we can use another trick from the ormar library called filtering. This allows us to query all the entries of Gpsreadings that fit some criteria. In the case of getting uphill movement, the criteria is that "height_to_prev" is greater that zero. So all we need to do is the following:
up_hill = await active_ride.gpsreadingss.filter(Gpsreadings.height_to_prev > 0).sum("height_to_prev")
Obviously, for downhill we just flip it to be less than zero.
So now we have accumulated distance, meters of climbing and decending, but we wanted to add some more interesting analytics for our other data. Fortunately this is super easy to do. Here again, we just use some of the built-in aggregators.
To get the average data, we just need to run a query for all the rows on a specific table for our ride and then use the .avg() on the specific columns we want an average for. For example:
avg_speed = await active_ride.gpsreadingss.avg("speed")
Similarly, we can get the max using .max():
max_speed = await active_ride.gpsreadingss.max("speed")
This all seems to work well and we can now push all this new data to our websocket and get it surfaced in the frontend :D