PostGIS is very new to me, and I'm having difficulty performing an operation that probably isn't too difficult. I have vector layers each filled with the outline of a landmass through time. I want to query the distance between these landmasses for every matching timestep. I can do this manually:

select a.gid from_gid, b.gid to_gid, st_shortestline(a.geom,b.geom), st_distance(a.geom,b.geom) as distance
(select * from gavdos
where year=3) as a,
(select * from crete
where year=3) as b;

The 'year' value ranges in integers from 0 to 21. It would be convenient to do this query using a FOR loop. I am however highly unsuccessful in doing so, the pgAdmin III SQL thing already presents an error when I say DECLARE i integer;, thus before I am even ready to try and create a loop.

My question is if someone could give an example how I can (1) set-up the loop and (2) how to get the full output (as I suppose I need to update a table at every step in the loop..).

Thanks in advance! :-)

1 Answer 1


I would recommend to do this with a JOIN instead of a loop:

      a.gid AS from_gid
    , b.gid AS to_gid
    , st_shortestline(a.geom,b.geom)
    , st_distance(a.geom,b.geom) AS distance
    gavdos AS a
    crete AS b
    a.year = b.year

If you really need to have a loop and do advanced processing which cannot be accomplished with simple SQL you can have a look at PL/pgSQL

  • 1
    Ah, that is a great solution! I hadn't realised explicit loops are less common in SQL type scripts. Thanks a lot!
    – Erik
    Jun 28, 2013 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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