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

[Jorge Arévalo]

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 <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
> >
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090329/b6226b09/attachment.html>