CODEWITHSUNDEEP
postgresqlpostgis
Lily
Lily

Reputation: 33

How to search for all points inside all polygons in Postgres

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

Upvotes: 1

Views: 2297

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324465

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

Upvotes: 1

Related Questions