Hi Brigit,
Thanks! I'm not sure I would have thought of using DISTINCT ON, but that
seems to work. It seems Postgresql picks the LAST item when using
DISTINCT ON instead if the FIRST row that matches, so I had to change
DESC to ASC. I'm not sure hold stable that will be because it assumes
some side effect of how DISTINCT ON works, which probably is not defined
in the SQL standard (but I have not looked).
But for now it looks like it will work and should be faster than using
the stored procedure. And it gives me a new way to think about this
class of tagging problems.
Thanks again, this helps a lot!
-Steve
On 4/26/2011 4:51 AM, Birgit Laggner wrote:
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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
