postgresql - 在 FROM 中使用 JOIN 语句的别名

Question

我正在尝试构建一个在 FROM 语句中使用 JOIN 语句别名的 PostGIS 查询，但这是不可能的。还有其他方法吗？

SELECT DISTINCT 
ST_Buffer(
    ST_Centroid(geom),
    ST_Area(geom))
FROM building AS b, ST_Transform(sg.geometry, 31467) AS geom 
LEFT JOIN surface_geometry AS sg ON b.id = sg.cityobject_id WHERE ST_Area(geom) < 100

Answer 1

您可以引入额外的子查询级别。

所以目前你有:

SELECT DISTINCT ST_Buffer( ST_Centroid(geom), ST_Area(geom) )
FROM building AS b,
     ST_Transform(sg.geometry, 31467) AS geom 
     LEFT JOIN surface_geometry AS sg ON b.id = sg.cityobject_id
WHERE ST_Area(geom) < 100

(我更改了缩进以使其更有意义，尽管我仍然不明白:您似乎没有在任何地方使用 b 的值)。你需要这样的东西:

SELECT DISTINCT ST_Buffer( ST_Centroid(geom), ST_Area(geom) )
FROM (
    SELECT ST_Transform(sg.geometry, 31467) AS geom 
    FROM building AS b
         LEFT JOIN surface_geometry AS sg ON b.id = sg.cityobject_id
) x
WHERE ST_Area(geom) < 100