1

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?

3

I have a feeling you may be over complicating the sql a bit. The following sql should sort out your problem and give you the answer you are looking for.

UPDATE schema1.table1
SET column1 = column2
FROM schema2.table2
WHERE ST_Intersects(table1.geom, table2.geom);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.