[GIS] Problem PostGIS Spatial Query ST_Contains ST_Disjoint ST_Intersects

postgisqueryspatial-databasespatial-query

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:
enter image description here

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.

Writing also:

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)

or

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?

Best Answer

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.

Related Question