New cartodb & PostgreSQL user here. I have two rows in cartodb table and each row has its own polygon. The two polygons have some overlapping area. How can I create a new polygon of just the overlapping area, preferably via cartodb SQL commands?

overlapping polygons


Use ST_Intersection

So something like:

SELECT ST_Intersection(p1.the_geom, p2.the_geom) As the_geom
   FROM p1 INNER JOIN p2 ON (ST_Intersects(p1.geom, p2.geom) );

If you know the ids or some other delimiting factor you could do this:

    SELECT ST_Intersection(p1.the_geom, p2.the_geom) As the_geom
   FROM p1 CROSS JOIN p2  WHERE p1.cartodb_id = 1 and p2.cartodb_id = 2;
  • The CROSS JOIN on rows in a single table did not work, but I was able to use the first method by moving the two rows to two separate tables and using: SELECT ST_Intersection(table1.the_geom, table2.the_geom) As the_geom FROM table1 INNER JOIN table2 ON ST_Intersects(table1.the_geom, table2.the_geom) The results came back in a different format that required running a find and replace and placing square brackets around each coordinate and commas in between each coordinate before being able to save as a polygon into cartoDB. THANK YOU. – Alex Sep 5 '15 at 3:13

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.