# Select point features using spatial query

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

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:

 select
*
from
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 – Shahzad Bacha Aug 26 '16 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 '16 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 – Shahzad Bacha Aug 26 '16 at 13:32
• @ShahzadBacha updated answer – kttii Aug 26 '16 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).
• Thank you but i am not using QGIS – Shahzad Bacha Aug 26 '16 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 '16 at 16:23