Is there a way to map unknown geom/shapefile/multipolygon to a known multipolygon table?

Question

I have two tables, Table_A:

ID	known_multipolygon
A	Philadelphia
B	Baltimore
C	Charlotte

And Table_B:

ID	unknown_multipolygon
1	unknown_polygon_A
2	unknown_polygon_B
3	unknown_polygon_C

Table_A is essentially a reference table, each row is a known multipolygon of an area. Table_B, however, is an unknown combination of multipolygons that could be either a single location, e.g., unknown_polygon_A could be Charlotte, or a combination of multipolygons, e.g., unknown_polygon_A could be Philadelphia + Baltimore. The problem is, I do not know what known_multipolygon, or combination of known_polygon, make up the Table_B.unknown_multipolygon. Is there a way to determine the polygons that make up Table_B.unknown_multipolygon?

Answer 1

You can use st_contains to join the two tables:

SELECT b.ID as unknown_id,
       a.ID as known_id
FROM Table_B b
 JOIN Table_A a
   ON st_contains(b.geometry, a.geometry);