[postgis-users] Query Help

[Obe, Regina]

IF you use a subselect, you really don't need the from polygons piece
you have there.  I think you can simply write

UPDATE lines 
	SET pname = (select a.name 
				FROM polygons a
   				WHERE a.the_geom &&
expand(lines.the_geom, 0.1)
   				ORDER BY distance(a.the_geom,
lines.the_geom) asc 
				LIMIT 1)
WHERE lines.pname = ''

Hope that helps,
Regina
 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Stephen Woodbridge
Sent: Friday, August 24, 2007 10:55 AM
To: PostGIS Users Discussion
Subject: [postgis-users] Query Help

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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.