I have two tables in postgis with polygon data. Both are in wgs84. I want to update a column (column1) in the one table (table1) with information from a column (column2) in the other table (table2), where polygons in table1 intersects polygons in table2.
Here is the SQL:
UPDATE schema1.table1 SET column1 = column2 FROM schema2.table2 WHERE column2 IN (SELECT column2 FROM schema1.table1,schema2.table2 WHERE ST_Intersects(table1.geom,table2.geom));
The query runs, but it doesn't return the correct information. All the information in column1 is the same even though there are different entries in column2. I have also checked that the polygons in table1 intersects different polygons in table2, so the results in column1 after running the query is incorrect. I have tried this with intersects, overlap, within and contains with the same result.
What am I missing?