[SOLVED] Selecting and ordering by distance with GeoAlchemy2. Bad ST

Selecting and ordering by distance with GeoAlchemy2. Bad ST_AsBinary wrap

I'm trying to select and order stores by their distance to a point with GeoAlchemy2 / PostGIS but for some reason I keep getting an error.

It seems GeoAlchemy2 wraps things with ST_AsBinary, but when I try to select the distance it tries to wrap the result of the distance calculation. I have no idea how to fix this.

I use this ORM query.

distance = (
    Store.coordinates.cast(Geometry)
    .distance_centroid(query_centroid)
    .label('distance')
)

stores = stores.order_by(distance).add_columns(distance)

The model.

class Store(db.Model):
    __tablename__ = 'stores'

    store_id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    address_details = db.Column(db.String)
    coordinates = db.Column(Geography('POINT'))

The error I get.

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) function st_asbinary(double precision) does not exist
LINE 1: ...Binary(stores.coordinates) AS stores_coordinates, ST_AsBinar...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
[SQL: 'SELECT stores.store_id AS stores_store_id,
    stores.name AS stores_name,
    stores.address_details AS stores_address_details,
    ST_AsBinary(stores.coordinates) AS stores_coordinates,
    ST_AsBinary(CAST(stores.coordinates AS geometry(GEOMETRY,-1)) <-> ST_GeomFromEWKT(%(param_1)s)) AS distance
    FROM stores ORDER BY distance']13 -46.730347)'}]
[parameters: {'param_1': 'POINT(-23.3569

The problem is precisely in this part...

ST_AsBinary(
    CAST(stores.coordinates AS geometry(GEOMETRY,-1))
    <->
    ST_GeomFromEWKT(%(param_1)s)
) AS distance

Notice how ST_AsBinary wraps the distance between the two points instead of wrapping just the geom, for example? (I'm not sure it should wrap the geom in this case, either)

Can anyone help? I just want to know how far things are.

Solution

An average user at freenode answered it for me.

GeoAlchemy2 will convert columns of type Geometry if they are in the select statement. Even though the result of the distance expression is a double, and not a Geometry, GeoAlchemy2 isn't smart enough to figure that out.

The column needs to be explicit cast in the ORM.

The fixed query:

distance = (
    Store.coordinates.cast(Geometry)
    .distance_centroid(query_centroid)
    .cast(db.Float)
    .label('distance')
)

stores = stores.order_by(distance).add_columns(distance)