Hi Nicklas, You're right. I'm sorry. I used pseudo-code to describe and simplify my query, but I did it bad.
Your query seems to work fine. Many thanks! Best regards, Jorge 2009/3/27 <[email protected]> > 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 > > > > _______________________________________________ > 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
