PostgreSQL: query does not work using count

Question

WITH hi AS ( SELECT ps.id, ps.brgy_locat, ps.municipali, ps.bldg_name, fh.gridcode, ps.bldg_type FROM evidensapp_polystructures ps JOIN evidensapp_floodhazard fh ON fh.gridcode=3 AND ST_Intersects(fh.geom, ps.geom) ), med AS ( SELECT ps.id, ps.brgy_locat, ps.municipali ,ps.bldg_name, fh.gridcode, ps.bldg_type FROM evidensapp_polystructures ps JOIN evidensapp_floodhazard fh ON fh.gridcode=2 AND ST_Intersects(fh.geom, ps.geom) EXCEPT SELECT * FROM hi ), low AS ( SELECT ps.id, ps.brgy_locat, ps.municipali,ps.bldg_name, fh.gridcode, ps.bldg_type FROM evidensapp_polystructures ps JOIN evidensapp_floodhazard fh ON fh.gridcode=1 AND ST_Intersects(fh.geom, ps.geom) EXCEPT SELECT * FROM hi EXCEPT SELECT * FROM med ) SELECT brgy_locat, municipali, bldg_name, bldg_type, gridcode, count( bldg_name) FROM (SELECT brgy_locat, municipali, bldg_name, gridcode, bldg_type FROM hi GROUP BY 1, 2, 3, 4, 5) cnt_hi FULL JOIN (SELECT brgy_locat, municipali,bldg_name, gridcode, bldg_type FROM med GROUP BY 1, 2, 3, 4, 5) cnt_med USING (brgy_locat, municipali, bldg_name,gridcode,bldg_type) FULL JOIN (SELECT brgy_locat, municipali,bldg_name,gridcode, bldg_type FROM low GROUP BY 1, 2, 3, 4, 5) cnt_low USING (brgy_locat, municipali, bldg_name, gridcode, bldg_type) 

The above query returns an error:

ERROR: column "cnt_hi.brgy_locat" should appear in the GROUP BY clause or be used in the aggregate function ********** Error **********

ERROR: column "cnt_hi.brgy_locat" should appear in the GROUP BY clause or be used in the state of the SQL aggregate: 42803

But if I omit count(bldg_name) , it works. But I need to calculate based on bldg_name .

EDIT: I wanted to get the number of buildings that intersect with the hazard value (gridcode): High (3), Medium (2) and Low (1). But if some geometry intersects already in High, exclude the middle query in it, and the same as Low, exclude the geometry that intersects in High and Medium.

PostgreSQL: 9.4, PostGIS: 2.1.7

Table Details:

 CREATE TABLE evidensapp_floodhazard ( id integer NOT NULL DEFAULT nextval('evidensapp_floodhazard_id_seq'::regclass), gridcode integer 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; 

The supposed conclusion is similar to this, but with the correct calculation:

EDIT 2: In any case, I try to explain what the intended conclusion means:

• count bldg_name , not id , in which network code does it cross into floodhazard with the condition, as indicated above, on EDIT 1 .
• then group it with what brgy_locat , brgy_municipali and what gridcode and bldg_type it belongs to.

Please take a look at the image above.

Answer 1

You probably want this:

 WITH hi AS ( SELECT ps.brgy_locat, ps.municipali, ps.bldg_name, ps.bldg_type, fh.gridcode , count(*) OVER(PARTITION BY ps.bldg_name, ps.bldg_type) AS building_count FROM evidensapp_polystructures ps JOIN evidensapp_floodhazard fh ON fh.gridcode = 3 AND ST_Intersects(fh.geom, ps.geom) ) , med AS ( SELECT ps.brgy_locat, ps.municipali, ps.bldg_name, ps.bldg_type, fh.gridcode , count(*) OVER(PARTITION BY ps.bldg_name, ps.bldg_type) AS building_count FROM evidensapp_polystructures ps JOIN evidensapp_floodhazard fh ON fh.gridcode = 2 AND ST_Intersects(fh.geom, ps.geom) LEFT JOIN hi USING (bldg_name, bldg_type) WHERE hi.bldg_name IS NULL ) TABLE hi UNION ALL TABLE med UNION ALL SELECT ps.brgy_locat, ps.municipali, ps.bldg_name, ps.bldg_type, fh.gridcode , count(*) OVER(PARTITION BY ps.bldg_name, ps.bldg_type) AS building_count FROM evidensapp_polystructures ps JOIN evidensapp_floodhazard fh ON fh.gridcode = 1 AND ST_Intersects(fh.geom, ps.geom) LEFT JOIN hi USING (bldg_name, bldg_type) LEFT JOIN med USING (bldg_name, bldg_type) WHERE hi.bldg_name IS NULL AND med.bldg_name IS NULL; 

Based on your comments on the question and the chat, now it counts as (bldg_name, bldg_type) - excluding buildings that already intersect at a higher level - again based on (bldg_name, bldg_type) .

All other columns are distinguished ( id , geom ) or functionally dependent noise for count ( brgy_locat , municipali , ...). If not , add more columns to the PARTITION BY to disambiguate the buildings. And add the same columns to the USING position of the JOIN clause.

If a building intersects with multiple lines in evidensapp_floodhazard with the same gridcode , it is considered that many times . See Alternative Punch.

Since you really do not want to group the lines, but simply count on sections, the key function uses count() as a window function , and not as a cumulative function, for example, in your original. The main explanation:

• The best way to get a result counter before applying LIMIT

count(*) does a better job here:

Using LEFT JOIN / IS NULL instead of EXCEPT . Details:

• Select rows not in another table.

And I did not see the FULL JOIN target in the outer request. Use UNION ALL instead.

Alternative request

This counts the creation once , no matter how many times it intersects with evidensapp_floodhazard at the same grid level level

In addition, this option (unlike the first!) (bldg_name, bldg_type) that all rows for the same one (bldg_name, bldg_type) coincide at the same level of the grid level, which may or may not be:

 SELECT brgy_locat, municipali, bldg_name, bldg_type, 3 AS gridcode , count(*) OVER(PARTITION BY bldg_name, bldg_type) AS building_count FROM evidensapp_polystructures ps WHERE EXISTS ( SELECT 1 FROM evidensapp_floodhazard fh WHERE fh.gridcode = 3 AND ST_Intersects(fh.geom, ps.geom) ) UNION ALL SELECT brgy_locat, municipali, bldg_name, bldg_type, 2 AS gridcode , count(*) OVER(PARTITION BY bldg_name, bldg_type) AS building_count FROM evidensapp_polystructures ps WHERE EXISTS ( SELECT 1 FROM evidensapp_floodhazard fh WHERE fh.gridcode = 2 AND ST_Intersects(fh.geom, ps.geom) ) AND NOT EXISTS ( SELECT 1 FROM evidensapp_floodhazard fh WHERE fh.gridcode > 2 -- exclude matches on **all** higher gridcodes AND ST_Intersects(fh.geom, ps.geom) ) UNION ALL SELECT brgy_locat, municipali, bldg_name, bldg_type, 1 AS gridcode , count(*) OVER(PARTITION BY bldg_name, bldg_type) AS building_count FROM evidensapp_polystructures ps WHERE EXISTS ( SELECT 1 FROM evidensapp_floodhazard fh WHERE fh.gridcode = 1 AND ST_Intersects(fh.geom, ps.geom) ) AND NOT EXISTS ( SELECT 1 FROM evidensapp_floodhazard fh WHERE fh.gridcode > 1 AND ST_Intersects(fh.geom, ps.geom) ); 

Also demonstrates the option without CTE, which may or may not work better, depending on the distribution of data.

Index

Adding gridcode to the index can improve performance. (Not tested using PostGis):

To do this, you need to install the additional module btree_gist . Details:

• Equivalent exception constraint consisting of integer and range

 CREATE INDEX evidensapp_floodhazard_geom_id ON evidensapp_floodhazard USING gist (gridcode, geom); 

Answer 2

The error requests the inclusion of select list columns in the GROUP BY ; you can do as below

 SELECT brgy_locat, municipali, bldg_name, bldg_type, gridcode, building_count FROM (SELECT brgy_locat, municipali, bldg_name, gridcode, bldg_type, count( bldg_name) as building_count FROM hi GROUP BY 1, 2, 3, 4, 5) cnt_hi FULL JOIN (SELECT brgy_locat, municipali,bldg_name, gridcode, bldg_type FROM med GROUP BY 1, 2, 3, 4, 5) cnt_med USING (brgy_locat, municipali, bldg_name,gridcode,bldg_type) FULL JOIN (SELECT brgy_locat, municipali,bldg_name,gridcode, bldg_type FROM low GROUP BY 1, 2, 3, 4, 5) cnt_low USING (brgy_locat, municipali, bldg_name, gridcode, bldg_type); 

Answer 3

I don’t know if this will work for you as I don’t have enough knowledge about postgresql. Also not sure if this will give you what you want. But give it a try. You just need to include build_count in your usage suggestion.

 SELECT brgy_locat, municipali, bldg_name, bldg_type, gridcode, building_count FROM (SELECT brgy_locat, municipali, bldg_name, gridcode, bldg_type, count( bldg_name) as building_count FROM hi GROUP BY 1, 2, 3, 4, 5) cnt_hi FULL JOIN (SELECT brgy_locat, municipali,bldg_name, gridcode, bldg_type, count(bldg_name) as building_count FROM med GROUP BY 1, 2, 3, 4, 5) cnt_med USING (brgy_locat, municipali, bldg_name,gridcode,bldg_type, building_count) FULL JOIN (SELECT brgy_locat, municipali,bldg_name,gridcode, bldg_type, count(bldg_name) as building_count FROM low GROUP BY 1, 2, 3, 4, 5) cnt_low USING (brgy_locat, municipali, bldg_name, gridcode, bldg_type, building_count); 

I am not after a reputation. I just updated Rahul's answer. Hope it helps. Hooray!:)