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?