I'm working on a project with two different tables inside a database. One contains points in a column geometry(Point,4326) type and the second one has a column geometry(MultiPolygon,4326). Now I would like to do a query on pgAdmin III using st_contains and st_disjoint to understand if a point is inside or outside the entire polygon. Both tables have more than one row. The multipolygon table has inside values to represent the area, instead the point table has just points that are on the map.
Between tables there is no relation because the multipolygon table is created from a shapefile, instead the polygon table is created from collected data.
I tried to load both tables on QGIS and I can see that some points are over the area represented by multipolygon table.
I wrote this query:
SELECT distinct points.id, st_disjoint (points.geom, multipolygon.geom) FROM points, multipolygon ORDER BY points.id;
The result is this:
As you can see, the id 1,2,3,4 are ok and also checking on QGIS is fine. The number 5 and 6 are cointained inside the multypoligon, so the result would be false, but I get a false and a true result together.
SELECT distinct points.id FROM points, multipolygon WHERE st_disjoint (points.geom, multipolygon.geom) ORDER BY points.id;
the result is wrong with all points inside the result. It's like the result I get with
WHERE st_intersect(points.geom, multipolygon.geom)='f'
If I write:
SELECT distinct points.id FROM points, multipolygon WHERE st_intersects(points.geom, multipolygon.geom) ORDER BY points.id
The result is correct, without any repetition and it looks like I did something like the st_contains.
To do the st_disjoint, instead, I tried to do
WHERE not st_intersects(points.geom, multipolygon.geom)
WHERE st_intersects(points.geom, multipolygon.geom)='f'
but the result is the entire list of points. So it's not working to emulate st_disjoint.
Why the st_intersects sometimes works, when st_contains and st_disjoint don't work?