Effectively forcing the planner to do the thing you want might help. In this case, sub-setting the polygon table prior to executing the spatial join with the points table. You might be able to outwit the planner using "WITH" syntax:
WITH polys AS (
SELECT * FROM area
WHERE area.id in IN(28,29,30,31,32,33,25,26,27,18,19,20,21,12,13,14,15,16,17,34,35,1,2,3,4,5,6,22,23,24,7,8,9,10,11)
)
SELECT *
FROM polys JOIN data ON ST_Intersects(data.point, polys.polygon)
WHERE datasetid IN(3)
AND (readingdatetime BETWEEN '1948-01-01' AND '2012-11-19');
The trouble with trying to play these games is that you are coding into your statement the assumption "my polygon list will always be more selective than my other query portions". Which might not be true for all parameterizations of your query, or for all applications of a particular query over a heterogeneously distributed dataset.
But it might work.
UPDATE: This goes even further down the dangerous road of assuming you know the selectivity of your clauses beforehand, this time we also take the attribute selection on the point table out and do it separately before the spatial join:
WITH polys AS (
SELECT * FROM area
WHERE area.id in IN(28,29,30,31,32,33,25,26,27,18,19,20,21,12,13,14,15,16,17,34,35,1,2,3,4,5,6,22,23,24,7,8,9,10,11)
),
WITH points AS (
SELECT * FROM data
WHERE datasetid IN(3)
AND (readingdatetime BETWEEN '1948-01-01' AND '2012-11-19')
)
SELECT *
FROM polys JOIN points ON ST_Intersects(points, polys.polygon);
You have a set of polygon, they cover an area of the map.
You have a set of points, some are inside the polygons and some are outside.
The st_intersect will compare all points against all polygons and only return points that are "on top" one or more polygons.
The st_disjoint will return a point if a point is not "on top" of a polygon. So for a set of points against a set of polygons, so unless the polygons overlap, each point is going to be "outside" of one or more polygons.
You can have a nested query of the intersect, and another inverted nested query of the intersect to get your t/f result
Adding a solution
There's a fiddle that goes with this here, since it was a PostgreSQL fiddle rather than PostGIS I've change the fiddle test to be a simple integer equality test.
So the first half of the sql is your intersect test:
SELECT distinct points.id
FROM points, multipolygon
WHERE st_intersects(points.geom, multipolygon.geom)
I add a TRUE column to match your sample, by adding a wrapping SQL select with a hardcoded value of TRUE:
SELECT
id,
TRUE AS intersects_at_least_one_polygon
FROM
(
SELECT distinct points.id
FROM points, multipolygon
WHERE st_intersects(points.geom, multipolygon.geom)
) intersecting ;
To find the points that are NOT intersecting the polygons, well that is the total list of point id, minus the above points. So I select all the points, the outer select below, and then say "WHERE id NOT IN" and just repeat your SQL, plus I add a hardcode a FALSE:
SELECT
points.id,
FALSE AS intersects_at_least_one_polygon
FROM
points WHERE id NOT IN
(
SELECT distinct points.id
FROM points, multipolygon
WHERE st_intersects(points.geom, multipolygon.geom)
)
order by id
;
SELECT
id,
TRUE AS intersects_at_least_one_polygon
FROM
(
SELECT distinct points.id
FROM points, multipolygon
WHERE st_intersects(points.geom, multipolygon.geom)
) intersecting
UNION
SELECT
points.id,
FALSE AS intersects_at_least_one_polygon
FROM
points WHERE id NOT IN
(
SELECT distinct points.id
FROM points, multipolygon
WHERE st_intersects(points.geom, multipolygon.geom)
)
order by id
;
I'm certain that there are more elegant, efficient, solutions to this, but hope you're happy with a simple working version.
Best Answer
Ironically, the fastest way to find the set of things not within other things is to do a full join that finds the contained things, but using a LEFT JOIN, so the un-matched things are hanging about to be found, thus:
The un-matched rows in a left join are returned as NULL so doing an IS NULL test on a column that you know is declared NOT NULL finds you all the un-matched rows.