SELECT from function which returns multiple columns

Question

I have the following query, which clusters locations, and returns a cluster ID, along with a structure containing the raw points within that cluster:

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

Example output:

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

Instead of returning the raw points, I want to returning the bounding circle for the cluster (center and radius), which can be done using ST_MinimumBoundingRadius:

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

Example output:

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

However, as ST_MinimumBoundingRadius returns two columns, the below throws an error:

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

I found this question which mentions using LATERAL but I wasn't able to get a working query.

What's the correct way to do this?

Laurenz Albe · Accepted Answer · 2019-02-25 16:48:27Z

2

Try another subselect:

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;

answered Feb 25, 2019 at 16:48

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Benedict Lewis · Accepted Answer · 2019-02-25 15:52:45Z

0

Really simple in the end, no idea how I didn't guess this.

SELECT
    cluster_id,
    (ST_MinimumBoundingRadius(ST_Collect(origin))).*
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

answered Feb 25, 2019 at 15:52

Benedict Lewis

2,8137 gold badges41 silver badges81 bronze badges

4 Comments

Laurenz Albe Over a year ago

This will end up calling the function twice. if you want to avoid that, wrap the query with another outer query that selects the center and radius properties.

Benedict Lewis Over a year ago

As in (SELECT center, radius FROM ST_MinimumBoundingRadius(bounds)).*? This throws the same error I had before - ERROR: subquery must return only one column

Laurenz Albe Over a year ago

I mean SELECT x.center, x.radius FROM (SELECT st_minimumboundingradius(...) AS x FROM ...) AS q

Benedict Lewis Over a year ago

Could you post your suggestion as an answer? I've tried your suggestion but get ERROR: missing FROM-clause entry for table "x"

Collectives™ on Stack Overflow

SELECT from function which returns multiple columns

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related