3

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?

  • Welcome to GIS.SE. Does table multipolygon contain only the one row? – Martin F May 9 '14 at 18:12
  • @martinf Both table don't contain a single row. The point table is around 50 rows, the multypoligon is around 30000. I used also WHERE clause but it didn't work. I tried using the St_Intersect function and it works. I can't figure out why contains doesn't work. Suggestions are very welcome. – user29780 May 9 '14 at 18:15
  • @martinf Did it. – user29780 May 9 '14 at 18:28
  • The question is still unclear to me. Could you rewrite (rather than just append to) it to include all the relevant info and the exact queries you've tried. – Martin F May 9 '14 at 19:37
  • @martinf Ok. I rewrote the question. – user29780 May 9 '14 at 21:02
2

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.

  • Hi Stoner, thank you so much for your answer. I never made spatial query before, so I want ask you: can you write the query? – user29780 May 10 '14 at 5:35
  • here's a fiddle of it sqlfiddle.com/#!15/beb82/7 I'll update the answer too – MichaelStoner May 10 '14 at 9:19
  • I saw ur update, but right now I can't test it. I would like to ask you, how can I get only point which are over the are and the other that are outside? I mean your solution works when both polygon and multi polygon test_value are equal. My test_values are points and area right or true and false parameters? – user29780 May 10 '14 at 11:12
  • The 1st section selects the points that intersect one or more multipolygons. We get a list of ids that intersect. We then repeat the query but using it to all ids not in that list. This is done by the "where not in (LIST OF INTERSECTING POINT IDs)" – MichaelStoner May 10 '14 at 12:05
  • What are id and test_value inside the first select? Are them referred to point table? Previously I was using spatial query functions, but now I just check if a geom type is equal to a multipolygon type right? – user29780 May 10 '14 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.