Select Polygon Intersections Between Two Tables With No Common Key

Question

I am new to geodatabase queries and cartodb. I am trying to write a SELECT query that will do the following.

I have two tables.

Table 1 contains congressional district polygons and info about representatives.

Table 2 contains service territory polygons and info about providers.

There is no common key between the tables.

For each polygon in Table 1, I need to find all of the intersecting polygons from Table 2, and return all of the calculated polygon intersections.

Help?

Answer 1

From what you described, you don't need any extra common keys, as the geometries will take that role. So just create your query and join the tables spatially. Eg. something like this with corrected names and fields you want to select:

SELECT congdist.name, congdist.representative, territories.info
FROM congdist, territories
WHERE ST_Intersects(congdist.geom, territories.geom);