PostGIS ST_Contains question

Question

Here's a quick summary about what I'm trying to do: Let's say I got two layers. One (A) with 10 polygons and one that is more granular with 100 polygons (B). Just to check if the behavior of PostGIS and ArcMap is the same, I would like to select all polygons in B that are under a polygon in A. Since the sum of all polygons of both layer got the same shape, all polygons of B should be selected. If I do that in ArcMap, everything works fine (100 of 100 polygons selected). If I use the following statement in PostGIS, some of the polygons are not in the resultset (about 10%):

SELECT layer_a.gid, layer_b.gid FROM layer_a, layer_b WHERE ST_Contains(layer_a.geom, layer_b.geom);

No I want to be more specific. Let's say I'd like to select all Polygons from B that are under a specific polygon from A with a polygon_id = 1. For this I use the following statement:

SELECT layer_a.gid, layer_b.gid FROM layer_a, layer_b WHERE ST_Contains(layer_a.geom, (SELECT layer_b.geom WHERE layer_by.polygon_id = '1');

But now, I get a empty resultset and I really don't know why. Could somebody explain me this behavior? Note: I just imported shape files without assigning a special SRID or something like this. I appreciate any help. :)

Answer 1

Looks like ST_Contains is the correct function. Two things come to mind that could cause the results you are getting. First, invalid geometries can silently cause all kinds of trouble. Run a query similar to the following on both of your tables:

SELECT gid AS invalid_gid
  FROM layer_a
  WHERE NOT St_IsValid(geom);

Second issue could be that the polygons do not actually have the topological properties that you think they do. Replace St_Contains with St_Intersects and that should help to inform you. Let me know what that yields.

Your second query appears to have typos, but should look more like this:

SELECT layer_a.gid, layer_b.gid
  FROM layer_a, layer_b
  WHERE layer_b.polygon_id = 1
    AND St_Contains(layer_a.geom, layer_b.geom)