[postgis-users] Problem updating a column when two polygons match

[nicklas.aven at jordogskog.no]

Hi 
 
first, I think you get also the polygons partly within when you are using st_dwithin like that. St_within only gets the polygons comlpetly within.
 
second, In your query you don't tell the database how to link the table T1 outside the subquery with the tables inside the subquery. T1 inside and outside the subquery is handled independent from eachother. I'm supriseed it works at all.
This should will work instead:
update T1 set new_column = T2.fourth_column from T2 where st_dwithin(T1.polygon, T2.multipolygon, 0.0);
 
/Nicklas

2009-03-27 Jorge Arévalo wrote:

Hello everybody,
>
>I have 2 tables:
>
>T1: 8 columns. 1 geometry column of type polygon. UTM coords. Thousands
>of rows
>T2: 4 columns. 1 geometry column of type multipolygon. UTM coords. 29
>rows. Each multipolygon has thousands of polygons.
>
>I need to add a new column to T1. The new column's value will be taken
>from one of the T2's fields (the 4th one). For each row of T1, I have to
>find the multipolygon of T2 that contains the polygon of T1. When
> matchs, I have to update the new T1's column with the value of 4th
>column of the T2 matching row.
>
>I'm trying something like this:
>
>UPDATE T1 SET new_column = (SELECT T2.fourth_column from T1 JOIN T2
> ON ST_DWithin(T1.polygon, T2.multipolygon, 0.0))
>
>Yes, I know that I can use ST_Within(T1.polygon, T2.multipolygon)), but
>I would need PostGIS with GEO support, and I can't compile it again.
>
>Anyway, my big problem is that the query takes really LONG time. More
> than 1 hour. And I have to translate this test to a bigger enviroment
>(bigger tables). I' ve created index on the geometry columns in both
>tables, but doesn't seem to be enough...
>
>Any clues?
>
>Thanks in advance
> Jorge 
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090327/b2bed4ae/attachment.html>