Intersecting variable number of points with multiple polygons

Question

This might be a pretty simple question, but I just can't think of a proper query to solve it.

I would like to intersect a number of points (can range from one to pretty much anything depending on the user's input) with a number of single polygons (the polygons are all in one table).

To be specific, I want to set the value of a field (let's call it "check") in the polygon table to 1 whenever it intersects with a point from the point table.

Here's a screenshot to clarify what I want to do enter image description here

The field "check" of that one cell that intersects with the points should be set to 1.

My query so far looks like this:

select * from raster2km     
where ( ST_Intersects( (select geom from points), (select the_geom from raster2km) ) = true)

But naturally it doesn't work since it returns more than one geometry.

Any suggestions? I'd be really grateful.

EDIT: I tried to run the query Jakub Kania suggested, however i just keep on getting error messages saying "invalid join selectivity". I tried to run it with a significantly smaller table containing "only" 5000 polygon" but the error message stays the same.

EDIT: I can now confirm that the query presented by Jakub Kania works on a table with only a few polygons (I tested it with 10 polygons). The problem is that it is crucial for me to maintain that high amount of polygons.

Maybe I use the wrong approach, but I just can't think of another way to do it.

LAST EDIT: I came up with a workaround that works perfectly: I get the coordinates of the points, use the google api to get the name of the town they are located in and use that name to do a query with postgresql that is much, much faster than intersecting.

To conclude, Jakub Kania's query works just fine, but it's not suitable for very large datasets. Using reverse geocoding is a possible alternative.

Jakub Kania Jakub Kania · Accepted Answer · 2014-11-07T13:55:06

ST_Intersects() is a function so you can just use it in ON clause of a JOIN.

SELECT r.* 
FROM raster2km AS r
JOIN points AS p
  ON  ST_Intersects(p.geom,r.geom)

Intersecting variable number of points with multiple polygons

1 Answers