Check if polygon falls inside other polygon with 2 layers

Question

I have two polygons layers with SRID 4326, indexed in a PostGIS database. Layer 1 is a country layer. I want to check whether the polygons (> 60k instances) from layer 2, fall inside the boundaries of 1 country only (1 polygon). So, once a polygon overlaps a country boundary (= falls inside multiple countries), I need to detect them. I used the ST_CONTAINS function of PostGIS:

SELECT
  a.identifier, a.name, ST_Contains(c.geom, a.pol) 
FROM
  layer_polygons a, layer_countries c 
WHERE 
  ST_Contains(c.geom, a.pol) = False

This gives wrong 'False' values (too many False's). Any idea what can be the problem and also how I can do this the best/most efficient (current query is very slow)?

Answer 1

You can look for polygons partially contained by multiple country polygons, as in Zoltan's answer, or you can look for polygons that are fully contained by no country polygon.

SELECT *
FROM layer_polygons a
WHERE NOT EXISTS (
  SELECT 1
  FROM layer_countries c 
  WHERE ST_Contains(c.geom, a.pol)
);

Answer 2

To find polygons overlapped by more polygons from another layer you can use the following query.

SELECT a.identifier, a.name, count(*)
FROM layer_polygon a INNER JOIN layer_ countries c ON ST_Overlaps(a.pol, c.geom)
GROUP BY a.identifier, a.name HAVING count(*) > 1;

Create spatial index on both layers to speed up the query.