[postgis-users] Query Help

[Stephen Woodbridge]

Cool, that did the trick.

Thanks,
   -Steve

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