'Parameter substitution when calculating the distance between each row and a common point results in psycopg2 parse error

I have two models with no relation Port and a WareHouse each with a lat and long fields. I want to be able to get the distance for each warehouse from the Port and maybe get the closest e.t.c.

This is part the error:

InternalError: (psycopg2.errors.InternalError_) parse error - invalid geometry
HINT:  "POINT(Wa" <-- parse error at position 8 within geometry

[SQL: SELECT warehouse.created_at AS warehouse_created_at, warehouse.updated_at AS warehouse_updated_at, warehouse.id AS warehouse_id, warehouse.name AS warehouse_name, warehouse.latitude AS warehouse_latitude, warehouse.longitude AS warehouse_longitude, warehouse.obs_lab_id AS warehouse_obs_lab_id, ST_Distance(Geometry(ST_GeomFromText(%(ST_GeomFromText_1)s)), Geometry(ST_GeomFromText(%(ST_GeomFromText_2)s))) AS distance 
FROM warehouse ORDER BY distance 
 LIMIT %(param_1)s]
[parameters: {'ST_GeomFromText_1': 'POINT(-10.8698502 9.3450003)', 'ST_GeomFromText_2': 'POINT(Warehouse.longitude Warehouse.latitude)', 'param_1': 1}]
(Background on this error at: https://sqlalche.me/e/14/2j85)

How should I go about it? This is what I currently have.

 port = session.query(Port).first()

 warehouse_distances = session.query(
     Warehouse,
     func.ST_Distance(
         func.Geometry(
             func.ST_GeomFromText(f"POINT({port.longitude} {fac.latitude})")
         ),
         func.Geometry(
             func.ST_GeomFromText(f"POINT({Warehouse.longitude} {Warehouse.latitude})")
         )
     ).label("distance"),
 ).order_by("distance")
pythonpostgresqlsqlalchemypostgispsycopg2


Solution 1:[1]

Answer from gis.stackexchange. Updated to this.

func.Geometry(
         func.ST_MakePoint(Warehouse.longitude, Warehouse.latitude)
     )

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 StackEdd

Related Questions