[postgis-users] Need help with a query to assign attributes to lines the intersect multiple polygons

[Stephen Woodbridge]

Hi all,

I have a table a line and a table of polygons.
I would like to come up with a query that allow me to link the lines to 
the polygon where most of the length of the line falls into that polygon.

The problem is that the lines may overlap or touch more than one 
polygon. So I can do this in a stored procedure where I iterate through 
the lines and intersect them with the overlapping polygons and sort them 
based on the length of the intersection fragments and limit 1 on the 
results.

It just seems like this should be able to be done using join and 
sub-query of some kind, but I can't quite figure it out. Ultimately, I 
want to do an UPDATE line set attr1=b.someattr ...

Thoughts,
   -Steve