I have a table of cities with an ID and geometry (200K rows) and a table of travels, with only two ID: idfrom, idto (7K rows)

I'm wondering how could I select the geometries for each one in order to make a line later. What I'm finding is something like:

idfrom, geom_from, idto, geom_to

So, I can use the ST_makeline postgis function.

I've tried with this SQL expression:

select r1.idfrom, city.geom as from_geom, r2.idto,ity.geom as to_geom
from routes as r1, routes as r2, city
where r1.idfrom=city.id
and r2.idto=city.id

But it seems that it does not work, It returns the same values in 'from' and in 'to' columns. I've tried more queries, but still have not found the solution

Any clue or suggestion?



I think you need two city tables and one route table in your query, so something like:

select r.idfrom, c1.geom as from_geom, r.idto, c2.geom as to_geom 
from routes r, city c1, city c2
where c1.id=r.idfrom and c2.id=r.idto

This way you are using your single routes to join the two city locations to make the line.


Presuming these are your data:

(the City table)

enter image description here

(and the Routes table)

enter image description here

another solution is, to retain your tables as they are, and performing this query:

SELECT id_from, from_geom, from_city, id_to, geom AS to_geom, name AS to_city
    (SELECT r.id_from, c.geom from_geom, c.name from_city, r.id_to
        FROM routes r, city c
        WHERE r.id_from = c.id) AS qry
    INNER JOIN city ON qry.id_to = city.id

enter image description here

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.