I have two postgres tables, one polygon features and second point features, having same projection. What i want to do is to select only the points which contains in large polygon and does not contain in the small polygons, as highlighted in the picture below , using spatial query.
I am trying to do with the below query is to,
select all the points which does not intersect/contain in the small polygon (
kind=1) and then return those points from the selected features which contains in the large polygon (
The query results in to
select p.geom as point_geom,sp.geom as polygon_geom
from polygons sp, points p
where st_disjoint(sp.polygon_geom,p.point_geom) and sp.kind = 1
) as subquery
where st_contains(subquery.scale_geom,subquery.pois_geom) and subquery.kind = 2;
kind column in the above query is used to differentiate between these polygons, where
kind = 1 represents the small polygon and
kind = 2 the large polygon. Also both polygons overlaps which means the points contain in small polygon also contains in large polygon.