如何在一个查询中包含三个几何 - PostGIS

Question

我是SQL新手，在创建包含三个几何形状的查询时遇到困难。 我有一个点图层(建筑物(，我想从中计算所有点建筑物，它们与某个面(市政单位(中的线(道路(的最大距离为 50 米，使用一些额外的标准对三个表中的两个。

下面是表结构：

Table 1: building (id_building, address_name,color_tagged,point)
Table 2: roads (id_road, line) 
Table 3: munic_units (id_munic, munic_name, polygon)

我尝试了下面的代码，并进行了很多更改。但它给了我错误。 我很高兴听到任何建议。谢谢。

SELECT address_name, count(*) AS Frequency,munic_name,color_tagged
FROM building,roads,munic_units 
WHERE ST_CONTAINS((SELECT polygon FROM munic_units WHERE munic_name=''),(ST_DWithin((SELECT point FROM building WHERE color_tagged=''),line,50))) 
GROUP BY address_name,munic_name,color_tagged 
ORDER BY Frequency DESC,address_name DESC;

首先，我尝试了一个具有两种几何形状的更简单版本：

SELECT address_name, count(*) AS Frequency,color_tagged
FROM building,roads,loc_munic_units
WHERE ST_Dwithin(point,roads_geom,50) AND color_tagged='YELLOW'
GROUP BY address_name,color_tagged
ORDER BY Frequency DESC,address_name DESC;

并返回...在此处输入图像描述..此阶段的预期结果是找到距离道路 50 米的所有建筑物，这些建筑物color_tagged为"黄色"。最终所需的结果是仅在特定区域(一个多边形(中运行先前的搜索。表格的结构如下所示。

Answer 1

你已经接近解决方案了。第一个查询失败，因为您没有正确联接表。第二个查询可能会返回高估的计数，因为您与 city 表进行了交叉联接。

使用第二个查询，您可以添加缺少的连接条件(并正确编写连接(

SELECT address_name, count(*) AS Frequency,color_tagged
FROM building b  --selects from buildings
JOIN roads r ON ST_Dwithin(b.point,r.roads_geom,50) -- when building is within 50 of a road
JOIN loc_munic_units m ON ST_WITHIN(r.roads_geom, m.polygon) -- and when the road is within a municipality polygon
WHERE b.color_tagged='YELLOW' AND m.munic_name = 'abc' -- restrain to a specific municipality (or >1) and color
GROUP BY address_name,color_tagged
ORDER BY Frequency DESC,address_name DESC;