Show points that are within several overlapped/intersected polygons using ST_Within in PostGIS

Question

I have a separate table for points and polygons. The polygons are overlapped or intersect with each other. The points are within several polygons at the same time. I need to develop an SQL query which should show the points that are in the polygons. If I use an SQL query like this:

SELECT point_table.geom, polygon_table.name FROM point_table
INNER JOIN polygon_table on ST_Within(point_table.geom, polygon_table.geom);

It does not iterate through all polygons, instead it evaluates the point geometry with the innermost polygon and does not evalute the point geometry with other overlapped or intersected polygons. What I need is to evaulate all points with all polygons. Becuase points can be within several polygons at the same time.

For instance, if 10 points are within 3 overlapped polygons, it should return 30 records and each record/row should have the corresponding polygon_table.name in it. And in this situation, the above query is returning 10 records with polygon_table.name from the innermost polygon, i.e once it finds the match, it is not evaluating the points against other polygons.

Any help on this matter appreciated.

Thanks.

Hmm, that should work, as far as I can tell. Could you post a couple of overlapping polygons and some points? — mlinth
Please post your query and table definitions (ddl) and sample data, as formatted text (or better yet a fiddle). Include actual and expected output The demonstration by @JimJones contradicts your claim, there must a disconnect somewhere. — Belayer

Jim Jones Jim Jones · Accepted Answer · 2021-03-03T16:58:22

Your query looks just fine. The error is most probably somewhere else. To make my point clearer, I will add an example showing two polygons and four points, where three points are within the inner polygon and one point is within the outer one:

WITH pol(id,geom) AS (
  VALUES (1,'SRID=4326;POLYGON((-4.53 54.21000000000001,-4.51 54.21000000000001,-4.51 54.19,-4.53 54.19,-4.53 54.21000000000001))'::GEOMETRY),
         (2,'SRID=4326;POLYGON((-4.537478046647734 54.213934294785226,-4.502287464372343 54.213934294785226,-4.502287464372343 54.18506473763151,-4.537478046647734 54.18506473763151,-4.537478046647734 54.213934294785226))'::GEOMETRY)
), poi(id,geom) AS (
  VALUES (1,'SRID=4326;POINT(-4.524700698880288 54.20613765421879)'::GEOMETRY),
         (2,'SRID=4326;POINT(-4.517588984076674 54.20219317274976)'::GEOMETRY),
         (3,'SRID=4326;POINT(-4.524762007263298 54.19627574559112)'::GEOMETRY),
         (4,'SRID=4326;POINT(-4.507098941702792 54.211414844419664)'::GEOMETRY))
SELECT poi.id AS point_id, pol.id AS polygon_id FROM poi
JOIN pol ON ST_Within(poi.geom,pol.geom);

 point_id | polygon_id 
----------+------------
        1 |          1
        1 |          2
        2 |          1
        2 |          2
        3 |          1
        3 |          2
        4 |          2

In the resultset you can see that three points are joined with all polygons and one point is joined with a single one, as you can also see in the image above.

Try it yourself: db<>fiddle

If it still does not work, provide some sample data and we can test it.

Show points that are within several overlapped/intersected polygons using ST_Within in PostGIS

1 Answers