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