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
HO HO HO, if you've been nice this year, email Santa!
Visit asksanta.ca to learn more!