I am new to postgresql and I'm working with two tables, both having a geometry for postGIS. What I am trying to do is get the five nearest people from one list for every person in the second. I was trying to do this in a for loop - all I'm trying to do for now is display the 5 people for each person in the one list.
This code works as a command line argument to psql, and correctly finds five people for one query:
select lastname, city from eng_alumni order by ST_distance(point_geom, (select the_geom from points where text = 'Smith')) asc limit 5;
I tried to follow a for loop using the documentation, but it is not working. This seems like something that a for loop would work well for, at least in other languages that I have learned. Here is the code that I tried (running from a command line)
create table temp;
FOR temp IN SELECT text, the_geom FROM points ORDER BY text LOOP select lastname, latitude, longitude from eng_alumni order by ST_distance(point_geom, (select the_geom from temp)) asc limit 5;END LOOP;
Any help would be greatly appreciated.
Thanks,
Bryan Manuel
_________________________________________________________________
Use fowl language with Chicktionary. Click here to start playing!
http://puzzles.sympatico.msn.ca/chicktionary/index.html?icid=htmlsig