[postgis-users] Query Help

[Stephen Woodbridge]

Hi,

I have tables "polygons" (100s rows) and "lines" (~2mil rows) and a few 
(~150 rows) of the "lines" fall outside any polygon. I would like to 
update the lines.pname and assign the polygons.name where the 
polygons.name is the closest polygon to the line and pname=''. But I 
can't quite figure out the query so I only assign the closest one:

update lines set pname=a.name
   from polygons a
  where lines.pname=''
    and ...

I think I need to make "a.name" into a subselect so it selects the 
closest polygon for each row, something like:

(select a.name
   where a.the_geom && expand(lines.the_geom, 0.1)
   order by distance(a.the_geom, lines.the_geom) asc
   limit 1)

But I'm not sure how to set up the subselect, that references columns 
from the update.

Any suggestions on this one would be appreciated.

-Steve