postgresql - 从返回多列的函数中选择

Question

我有以下查询，它对位置进行聚类，并返回一个聚类 ID，以及一个包含该聚类中原始点的结构:

SELECT
    cluster_id,
    ST_AsGeoJSON(ST_Collect(origin)) AS points
FROM (
    SELECT
        origin,
        ST_ClusterDBSCAN(origin, eps := 0.01, minPoints := 5) OVER(ORDER BY id) AS cluster_id
    FROM
        ride
    WHERE
        region_id = 1 AND
        created_at > now() - interval '1 week'
) a
WHERE cluster_id IS NOT NULL
GROUP BY cluster_id

示例输出:

3   {"type":"MultiPoint","coordinates":[ ... ]}
4   {"type":"MultiPoint","coordinates":[ ... ]}

我不想返回原始点，而是想返回集群的边界圆(中心和半径)，这可以使用 ST_MinimumBoundingRadius 完成:

SELECT
    ST_AsGeoJSON(center),
    radius
FROM
    ST_MinimumBoundingRadius(
        ST_GeomFromGeoJSON('{"type":"MultiPoint","coordinates":[ ... ]}')
    )

示例输出:

{"type":"Point","coordinates":[ ... ]}  0.002677744742706528

但是，由于 ST_MinimumBoundingRadius 返回两列，下面会引发错误:

SELECT
    cluster_id,
    ST_MinimumBoundingRadius(ST_Collect(origin))
FROM (
    ...
) a
WHERE cluster_id IS NOT NULL
GROUP BY cluster_id;

我找到了 this提到使用 LATERAL 但我无法获得有效查询的问题。

正确的做法是什么？

Answer 1

尝试另一个子选择:

SELECT q.cluster_id,
       (q.mbr).radius,
       (q.mbr).center
FROM (
   SELECT cluster_id,
          ST_MinimumBoundingRadius(
             ST_Collect(origin)
          ) AS mbr
   FROM (
      ...
   ) AS a
) AS q;