上面的查询返回错误：
错误：列cnt_hi.brgy_locat必须出现在GROUP BY子句中或用于集合函数 **********错误** ********
错误：列cnt_hi.brgy_locat必须出现在GROUP BY子句中或用于聚合函数SQL州：42803
但是，如果我省略 count（bldg_name），它会起作用。但我需要根据 bldg_name 。
编辑：我想获得与危险值（网格代码）相交的建筑物数量：高（3），中（2）和低（1）。但是，如果某个几何图形已经在High中相交，请排除其中的Medium查询，并且与Low一起排除那些在High和Medium中相交的几何。
PostgreSQL ：9.4，PostGIS：2.1.7
表格详情：
预期的输出是这样的，但有正确的计数： 编辑2： 尽我所能解释预期的输出是什么，无论如何：
- 统计 bldg_name 不是 id code>，其中 floodhazard 与编辑1 中提到的条件相交的网格代码。
- 然后将它分组为 brgy_locat ， brgy_municipali 和 gridcode 和 bldg_type 它属于。
/ b> 请看看在上面的图片。
解决方案您可能想要这个：
WITH WITH AS（ SELECT ps.brgy_locat，ps.municipali，ps.bldg_na me，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 和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使用（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 ，计数（*）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用法bldg_name，bldg_type） LEFT JOIN med USING（bldg_name，bldg_type） WHERE hi.bldg_name IS NULL AND med.bldg_name IS NULL; 根据您对问题和聊天的评论，此计数按照 （bldg_name，bldg_type） 现在 - 不包括已经在更高层上相交的建筑物 - 再次基于（bldg_name，bldg_type）
所有其他列都是不同的（ id ， geom ）或计数的功能相关噪声（ brgy_locat ， municipali ，...）。 如果不是，则添加更多列 PARTITION BY 子句以消除建筑物的歧义。
如果一栋建筑物与 evidensapp_floodhazard 与 相同 gridcode ，它会被多次计数 。由于你实际上并不想聚合行，而只是依赖分区，所以关键特性是使用 count（） code> as 窗口功能 ，而不是像原来那样的聚合函数。基本解释：
- 在应用LIMIT之前获取结果数量的最佳方法
count（*）在这里做的更好：
code> LEFT JOIN / IS NULL 而不是 EXCEPT 。详细信息：
- 选择不存在于其他表中的行
我在外部查询中未能看到 FULL JOIN 的用途。使用 UNION ALL 来代替。
备选查询 无论它在同一个网格代码级别与 evidensapp_floodhazard 相交多少次， 一次 另外，这个变体（不同于第一个！）假定同一个（bldg_name，bldg_type）的所有行匹配相同gridcode级别，可能是也可能不是这种情况：
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 evidens app_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 - 排除** all **更高的网格代码上的匹配和ST_Intersects（fh.geom，ps.geom）） UNION ALL SELECT brgy_locat， （，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））; 同时演示一个没有CTE的变体，根据数据分布，这可能会或可能不会更好。 / p> 索引
将 gridcode 添加到索引可能可以提高性能。 （未使用PostGis进行测试）：
您需要首先安装附加模块 btree_gist 。详细信息：
-
整数和范围/ 20908766＃20908766>相当于由整数和范围组成的排除约束
CREATE INDEX evidensapp_floodhazard_geom_id ON evidensapp_floodhazard USING gist（gridcode，geom）;
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 query above returns an error:
ERROR: column "cnt_hi.brgy_locat" must appear in the GROUP BY clause
or be used in an aggregate function
********** Error **********
ERROR: column "cnt_hi.brgy_locat" must appear in the GROUP BY clause
or be used in an aggregate function SQL state: 42803
But if I omit the count(bldg_name) it works. But I need to count 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 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.
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;
Intended output is like this but with correct count:
EDIT 2:
As much as I try my best to explain what the intended output is, anyway:
- count the bldg_name not the id in which what gridcode it intersects in floodhazard with the condition as mentioned above on the EDIT 1.
- then group it to what brgy_locat,brgy_municipali and what gridcode and bldg_type it belong.
Kindly take a look at the image above.
解决方案 You probably want this instead:
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 to the question and the chat, this counts per (bldg_name, bldg_type) now - excluding buildings that already intersect on a higher level - again based on (bldg_name, bldg_type).
All other columns are either distinct (id, geom) or functionally dependent noise for the count (brgy_locat, municipali, ...). If not, add more columns the PARTITION BY clause to disambiguate buildings. And add the same columns to the USING clause of the JOIN condition.
If a building intersects with multiple rows in evidensapp_floodhazard with the same gridcode it is counted that many times. See alternative blow.
Since you do not actually want to aggregate rows but just count on partitions, the key feature is using count() as window function, not as aggregate function like in your original. Basic explanation:
- Best way to get result count before LIMIT was applied
count(*) does a better job here:
Using LEFT JOIN / IS NULL instead of EXCEPT. Details:
- Select rows which are not present in other table
And I failed to see the purpose of FULL JOIN in the outer query. Using UNION ALL instead.
Aternative query
This counts building once, no matter how many times it intersects with evidensapp_floodhazard on the same gridcode level
Also, this variant (unlike the first!) assumes that all rows for the same (bldg_name, bldg_type) match on the same gridcode level, which may or may not be the case:
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 demonstrating a variant without CTEs, which may or may not perform better, depending on data distribution.
Index
Adding gridcode to the index might improve performance. (Not tested with PostGis):
You need to install the additional module btree_gist for this first. Details:
- Equivalent to exclusion constraint composed of integer and range
CREATE INDEX evidensapp_floodhazard_geom_id
ON evidensapp_floodhazard USING gist (gridcode, geom);
更多推荐
PostgreSQL：查询不能使用count
发布评论