How to search for all points inside all polygons in Postgres

Question

I want to search for all points of a specific user inside all polygons and display the polygons

these are my tables

users 

 id   points 
 1     1
 1     2
 1     3
 1     4
 2     3
 3     1

 poly

  polygon-points    poly-name
  (1,2,4,5)          store
  (1,3)              shoop

I wrote this code

BEGIN
FOR poly-name IN SELECT poly-name FROM poly

LOOP
    FOR points IN SELECT * FROM users
    LOOP
    points@poly-name
    END LOOP;

END LOOP;
RETURN;
END

Are you using PostGIS? Or built-in PostgreSQL shape data types? You've failed to provide table definitinos, so it's hard to know. — Craig Ringer, Jul 09 '12 at 01:33
do you mean the types of the polygon-points and the points ? the type of polygon-points is polygon and points is point — Lily, Jul 09 '12 at 16:43
I found a function that I cloud use which is boolean ST_Crosses(geometry g1, geometry g2); but the problem is I want to use this function between a point and a polygon so how i can cast the point and the polygon to geometry since this what i read when i googled the problem — Lily, Jul 09 '12 at 18:47
Is the column polygon-points defined as a 'geometry' or 'geography' data type, or are you using a varchar field and storing it as an array? You can use st_geomfromtext to create a polygon in a geometry field. — Twelfth, Jul 10 '12 at 21:04

score 1 · Accepted Answer · edited May 23 '17 at 12:20

It appears that you are using PostgreSQL's built-in point and polygon types, but you want to use a PostGIS function ST_Crosses that takes the PostGIS geometry type as arguments.

If so, start here: SQL query for point-in-polygon using PostgreSQL

Note that PostGIS is an add-on to PostgreSQL. You may not have it installed, and may need to install it to get the ST_Crosses function (and a lot more).

How to search for all points inside all polygons in Postgres

1 Answers1