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

[Stephen Woodbridge]

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