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

[Birgit Laggner]

Hi Steve,

I thought about your problem and I would solve it with distinct on  - 
like this:

update line set poly_att=sel.poly_att from (select distinct on 
(a.line_id) a.line_id, b.poly_att from line a inner join polygons b on 
a.the_geom && b.the_geom where st_intersects(a.the_geom,b.the_geom) 
order by a.line_id, st_length(st_intersection(a.the_geom, b.the_geom)) 
desc) sel where line.line_id=sel.line_id;

Hope that helps,

Birgit.


Am 25.04.2011 16:24, schrieb 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>