How to check a set of points are inside a polygon or not in postgis?

Question

I have a set of geometry values (point values). I have another distinct geometry value (polygon). I want to find out which points are inside (contains) the polygon. Say, my polygon is B. And the set of points are: {A1, A2, A3...}. I want to check whether B contains A1 then A2 then A3 and so on. I can do it individually by ST_Contains. Such as:

select * from table1 where st_contains(B,A1)

But I don't want to check individually. I want to check for the whole set at a time, i.e {A1, A2, A3...} and want to find out the points (i.e A3 and/or A2) which are contained in B means which has given true value for ST_Contains. How to do it?

Answer 1

You can find the points in one table that are within the polygons of another table with this statement:

SELECT *
FROM table1, table2
WHERE ST_Contains(table1.geom, table2.geom)

Answer 2

You could use the following simple query to get all polygons with their associated points inside. Or reduce the points only to one polygon using a WHERE clause.

 SELECT pol.gid, pol.name, pol.area, poi.gid, poi.name
   FROM polygons pol, points poi
  WHERE st_intersects(pol.the_geom, poi.the_geom) AND pol.name ilike 'Time Square';