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

[Jorge Arévalo]

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/568d9f66/attachment.html>