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?
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;