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.

enter image description here

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 (kind=2). The query results in to 0 rows.

select * from ( 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;

The 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.

2 Answers 2


Have you tried not doing the subquery?

 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)
  AND (st_contains(sp.polygon_geom,p.point_geom) and sp.kind = 2)

Take a look at your query further and you will see that you are restricting your results to only return subquery matches, thus ruling out any kind=2 polygons. your final query should be comparing the subquery against the original unfiltered table.

This does the trick:

    polygons sp,(
     SELECT p.id,p.geom p_geom,sp.geom sp_geom,sp.kind sp_kind
    FROM polygons sp, points p
    WHERE (st_disjoint(sp.geom,p.geom) and sp.kind = 1)
    ) as subquery
where st_contains(sp.geom,p_geom) and sp.kind = 2;
  • Yes, this was the first query came to my mind..but didn't worked for me Aug 26, 2016 at 5:03
  • @ShahzadBacha Please update the question with how it did not work. Was there an error message? Was the result not what you expected? Did you try to break down the query into smaller statement before combining them? EG: try 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) then try 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 = 2)
    – kttii
    Aug 26, 2016 at 13:17
  • After i asked this question here on GIS StackExcahnge, a friend of mine told me to post this question on stackoverflow.com, as members of this group are mostly not programmers . So here is the link stackoverflow.com/questions/39120369/… where you can follow my updated question . Sorry for inconvenience Aug 26, 2016 at 13:32
  • @ShahzadBacha updated answer
    – kttii
    Aug 26, 2016 at 14:13

This is a quick solution if all of the points are contained within the larger polygon and the smaller polygon (nothing outside).

  1. Manually select the points in the smaller polygon with Select Features by Area.
  2. Open Attribute Table for the points layer.
  3. Invert Selection (this button in QGIS). enter image description here
  • Thank you but i am not using QGIS Aug 26, 2016 at 5:01
  • The point about selection inversion still holds for most GIS programs, although I understand if this is not possible with a large data set.
    – arabiccode
    Aug 30, 2016 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.