PostgreSQL: Exclude geometries on the next query if it intersects on the previous one

Question

I have this query to get the number of geoms that intersect in another geom:

SELECT  count(evidensapp_polystructures.brgy_locat) AS high,
        evidensapp_polystructures.brgy_locat AS barangay,
        evidensapp_polystructures.municipali AS municipality
FROM evidensapp_floodhazard
INNER JOIN evidensapp_polystructures
      ON st_intersects(evidensapp_floodhazard.geom, evidensapp_polystructures.geom) 
      AND evidensapp_floodhazard.hazard= 'High'
GROUP BY evidensapp_polystructures.brgy_locat, evidensapp_polystructures.municipali;

As you noticed, its hazard is equal to High. I wanted to get also the number of geoms that intersect with the hazard value: Medium and Low. But, if a certain geometry intersects already in High,exclude in it Medium query and same goes with Low exclude those geometry that intersects in High and Medium.

I have this idea, maybe use CASE or I need to get the id of the geometry then try NOT IN in query but don't know how to do it. Probably because I'm new in PostgreSQL or any database work.

Here's a sample result of the query above:

The intended result should be like this:

Table details:

CREATE TABLE evidensapp_floodhazard (
  id serial NOT NULL,
  hazard character varying(6) NOT NULL,
  date_field character varying(60),
  geom geometry(MultiPolygon,32651),
  CONSTRAINT evidensapp_floodhazard_pkey PRIMARY KEY (id)
);

CREATE INDEX evidensapp_floodhazard_geom_id
  ON evidensapp_floodhazard USING gist (geom);

ALTER TABLE evidensapp_floodhazard CLUSTER ON evidensapp_floodhazard_geom_id;

CREATE TABLE evidensapp_polystructures (
  id serial NOT NULL,
  bldg_name character varying(100) NOT NULL,
  bldg_type character varying(50) NOT NULL,
  brgy_locat character varying(50) NOT NULL,
  municipali character varying(50) NOT NULL,
  province character varying(50) NOT NULL,
  geom geometry(MultiPolygon,32651),
  CONSTRAINT evidensapp_polystructures_pkey PRIMARY KEY (id)
);

CREATE INDEX evidensapp_polystructures_geom_id
  ON evidensapp_polystructures USING gist (geom);

ALTER TABLE evidensapp_polystructures CLUSTER ON evidensapp_polystructures_geom_id;

Patrick Patrick · Accepted Answer · 2015-08-03T05:34:03

Since there is little you can do with the strings "High", "Medium" and "Low" in terms of comparison, you have to use sub-queries. A solution with some CTE's would probably be the cleanest:

WITH hi AS (
  SELECT ps.id, ps.brgy_locat, ps.municipali
  FROM evidensapp_polystructures ps
  JOIN evidensapp_floodhazard fh ON fh.hazard = 'High'
                                 AND ST_Intersects(fh.geom, ps.geom)
), med AS (
  SELECT ps.id, ps.brgy_locat, ps.municipali
  FROM evidensapp_polystructures ps
  JOIN evidensapp_floodhazard fh ON fh.hazard = 'Medium'
                                 AND ST_Intersects(fh.geom, ps.geom)
  EXCEPT SELECT * FROM hi
), low AS (
  SELECT ps.id, ps.brgy_locat, ps.municipali
  FROM evidensapp_polystructures ps
  JOIN evidensapp_floodhazard fh ON fh.hazard = 'Low'
                                 AND ST_Intersects(fh.geom, ps.geom)
  EXCEPT SELECT * FROM hi
  EXCEPT SELECT * FROM med
)
SELECT brgy_locat AS barangay, municipali AS municipality, high, medium, low
FROM (SELECT brgy_locat, municipali, count(*) AS high
      FROM hi
      GROUP BY 1, 2) cnt_hi
FULL JOIN (SELECT brgy_locat, municipali, count(*) AS medium
      FROM med
      GROUP BY 1, 2) cnt_med USING (brgy_locat, municipali)
FULL JOIN (SELECT brgy_locat, municipali, count(*) AS low
      FROM low
      GROUP BY 1, 2) cnt_low USING (brgy_locat, municipali);

In the three CTE's you first identify rows that fall in the "high" hazard category, then those in the "medium" hazard category but EXCEPT those that are already in the "high" category, then those in the "low" hazard category except those listed as "high" or "medium". In the main query you then join the 3 CTE's with the count per barangay and municipality calculated in a sub-query from each of the CTE's. A FULL JOIN is used such that barangays and municipalities that do not have structures in the "high" hazard class also show up in the results.

PostgreSQL: Exclude geometries on the next query if it intersects on the previous one

1 Answers