CODEWITHSUNDEEP
sqlpostgresqlpostgis
Jesper
Jesper

Reputation: 105

For a list of points, is the point inside any polygon or not

I have a table with 200000 points and another table with about 50000 polygons in PostGIS. The polygons can overlap with each other.

How can I query PostGIS so that I get a flag for each point indicating if it intersects any polygon or not? The point might touch tree overlaying polygons but I only want true or false if touches any polygon in the table.

Updated with sample data:

CREATE TABLE poi (id integer,geom GEOMETRY);
INSERT INTO poi VALUES (1,'POINT(28 27)');
INSERT INTO poi VALUES (2,'POINT(12.1321018521888 30.2084895425822)');
INSERT INTO poi VALUES (3,'POINT(24.1330003363259 37.3570074902601)');
INSERT INTO poi VALUES (4,'POINT(41.9053232823 31.3888090347548)');
INSERT INTO poi VALUES (5,'POINT(37.1416078534822 18.033534226006)');
INSERT INTO poi VALUES (6,'POINT(34.3933104907027 18.7290161619684)');

CREATE TABLE poly (geom GEOMETRY);
INSERT INTO poly VALUES ('POLYGON((30 10,40 40,20 40,10 20,30 10))');
INSERT INTO poly VALUES ('POLYGON((35 15,38 30,20 35,35 15))');

enter image description here

The result I want:

1   true
2   false
3   true
4   false
5   false
6   true

Upvotes: 3

Views: 1960

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

Just use the function ST_Contains with both geometries. Depending on your use case, check ST_Overlaps, ST_Touches or ST_Intersects

Select all points contained in the polygons

SELECT ST_AsText(poly.geom), ST_AsText(poi.geom)
FROM poi,poly
WHERE ST_Contains(poly.geom,poi.geom);

                st_astext                 |                st_astext                 
------------------------------------------+------------------------------------------
 POLYGON((30 10,40 40,20 40,10 20,30 10)) | POINT(28 27)
 POLYGON((30 10,40 40,20 40,10 20,30 10)) | POINT(24.1330003363259 37.3570074902601)
 POLYGON((35 15,38 30,20 35,35 15))       | POINT(28 27)
 POLYGON((35 15,38 30,20 35,35 15))       | POINT(34.3933104907027 18.7290161619684)
(4 Zeilen)

Retrieve a boolean value stating if the points are contained in any polygon in the foreign table

SELECT 
  id, 
  EXISTS (SELECT * FROM poly WHERE ST_Contains(poly.geom,p.geom)) 
FROM poi p;

 id | exists 
----+--------
  1 | t
  2 | f
  3 | t
  4 | f
  5 | f
  6 | t
(6 Zeilen)

Further reading:

Upvotes: 3

Related Questions