Printing the calculated distance in SQLAlchemy

Question

I am using Flask-SQLAlchemy with Postgres, Postgis and GEOAlchemy. I am able to sort entries in a table according to a point submitted by the user. I wonder how I could also return the calculated distance...

This is how I sort the items:

result = Event.query.order_by(func.ST_Distance(Event.address_gps, coordinates_point)).paginate(page, 10).items

for result in results:
    result_dict = result.to_dict()

return result_dict

according to the users position (coordinates_point). I would like to add an entry in each result in the result_dict which also contains the distance that the item was ordered by. How do I do that? What does func.ST_Distance return?

I tried to add this in the for loop above:

current_distance = func.ST_Distance(Event.address_gps, coordinates_point)
result_dict['distance'] = current_distance

But that did not seem to work.

Answer 1

You can use column labels

query = Event.query.with_entities(Event, func.ST_Distance(Event.address_gps, coordinates_point).label('distance')).order_by('distance')
results = query.paginate(page, 10).items
for result in results:
    event = result.Event
    distance = result.distance
    result_dict = event.to_dict()
    result_dict['distance'] = distance