1

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")

1 Answer 1

1

It looks like 'port.longitude' and 'fac.latitude' are correctly replaced in your f-string, but 'Warehouse.longitude' and 'Warehouse.latitude' are not and are left as string. The reason is you try to create a geom from text so the input is a string, not columns.

If you want to create a geom from fields you should use for example the function ST_MakePoint and pass your 2 columns as parameters.

1
  • This worked easier than expected
    – StackEdd
    May 27 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.