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
Here shows how to make these queries: https://gist.github.com/jpetazzo/5177554 It shows how to do it downloading the OSM database in PostGis format first. For example, you can make a query like:
SELECT name,way FROM osm_points WHERE place IN ('town', 'city');
(The way column should be the GPS coordinates of the center of the city.)
And you will get the points already as spatial objects, with latitude and longitude.
If you select a polygon or a line in OSM database, you can get the coordinates (X and Y) with ST_Centroid() function.
Maybe it suits your needs.