sql - 使用 PostGIS 计算邻域多边形包含的点数(来自两个数据集)

Question

我有三个空间数据集:

1. 邻域多边形
2. 派出所的地点
3. 犯罪地点

我正在使用 PostGIS。

我目前的代码是:

SELECT 
nc.nbh_names,
count(ps), 
count(ci)
FROM public."Neighborhood_Clusters" AS nc
LEFT JOIN public."Police_Stations" AS ps
ON ST_Contains(nc.geom, ps.geom)
JOIN public."Crime_Incidents_in_2020" AS ci
ON ST_Contains(nc.geom, ci.geom)
GROUP BY nc.nbh_names;

我得到的输出:

我希望我的输出是一个如下所示的 View :

第 1 列:街区名称 |第 2 栏:派出所数量 |第 3 列:犯罪数量

每个街区的警察局不应超过一两个。

无论出于何种原因，如果附近有警察局，它就会计算并返回犯罪数量。

有人可以帮助更正代码以便我得到想要的结果吗？

Answer 1

涉及多个表的计数的空间连接可能会变得非常棘手。您可以做的是对每个表使用子查询，然后使用 ST_Contains 引用外部查询，例如

SELECT 
   nc.nbh_names,
  (SELECT count(ps.geom) FROM public.Police_Stations ps 
   WHERE ST_Contains(nc.geom, ps.geom)) AS pol,
  (SELECT count(ci.geom) FROM public.Crime_Incidents_in_2020 ci
   WHERE ST_Contains(nc.geom, ci.geom)) AS cri
FROM public.Neighborhood_Clusters nc;

检查这个 db<>fiddle .

进一步阅读:

• Subqueries in PostgreSQL
• Don't use double quotes in PostgreSQL