Presto: ST_Collect (or Similar?)

[voycey]

Is there anything that takes the place of ST_Collect? It would help us in moving over items from PostGIS verbatim.

For example I am trying to create a convex hull over a few billion points that are identified by a cluster_id:

cid, lat, lng

A simple PostGIS query would be something like:

SELECT c.cid, ST_ConvexHull(ST_Collect(c.cid)) as geom
FROM c_clustered As c
GROUP BY c.cid;

However any aggregations I try and use in Presto means I have to aggregate each.

I can see some functions and issues relating to:

convex_hull_agg(Geometry) → Geometry

Returns the minimum convex geometry that encloses all input geometries. This function doesn’t support geometry collections.

geometry_union_agg(Geometry) → Geometry

Returns a geometry that represents the point set union of all input geometries.

But lacking any documentation for how to actually use them or how to solve the aggregation problem - can you provide any pointers as to how I can work around the ST_Collect thing?

Thanks :)

[mbasmanova]

@voycey Dan, I think you can use convex_hull_agg aggregate function like so:

SELECT c.cid, convex_hull_agg(ST_Point(lng, lat)) as geom
FROM c_clustered As c
GROUP BY c.cid;

[voycey]

I think I tried this but it didn't like the fact that convex_hull_agg wasnt
aggregated or in the group, I'll try again tomorrow and let you know!

On Mon, 10 Sep. 2018, 19:27 Maria Basmanova, [e-mail]
wrote:

@voycey https://github.com/voycey Dan, I think you can use
convex_hull_agg aggregate function like so:

SELECT c.cid, convex_hull_agg(ST_Point(lng, lat)) as geom
FROM c_clustered As c
GROUP BY c.cid;

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/prestodb/presto/issues/11451#issuecomment-419848202,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ABBAigj_vhvC_KPqwjGvWfNcBBg9tfx9ks5uZjBpgaJpZM4WhAOd
.

[voycey]

Never mind I was on 0.206 :( will upgrade and test!

Thanks Maria!

On Mon, 10 Sep. 2018, 20:02 Dan Voyce, [e-mail] wrote:

I think I tried this but it didn't like the fact that convex_hull_agg
wasnt aggregated or in the group, I'll try again tomorrow and let you know!

On Mon, 10 Sep. 2018, 19:27 Maria Basmanova, [e-mail]
wrote:

@voycey https://github.com/voycey Dan, I think you can use
convex_hull_agg aggregate function like so:

SELECT c.cid, convex_hull_agg(ST_Point(lng, lat)) as geom
FROM c_clustered As c
GROUP BY c.cid;

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/prestodb/presto/issues/11451#issuecomment-419848202,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ABBAigj_vhvC_KPqwjGvWfNcBBg9tfx9ks5uZjBpgaJpZM4WhAOd
.

[mbasmanova]

convex_hull_agg function was added in 0.209: https://prestodb.io/docs/current/release/release-0.209.html

[mbasmanova]

@voycey Dan, I'm closing this issue assuming that convex_hull_agg function is sufficient for your use case. Feel free to reopen if there is anything else that remains unresolved.

[voycey]

Hi Maria,

There are some problems installing 0.210 on Google Dataproc but it does look like this will solve it - am investigating and will raise a ticket if I can't figure it out :) Thanks!

[voycey]

Hi Maria,

This is sorted now - works a treat!

[mbasmanova]

@voycey Thanks for the update, Dan. Happy to hear Presto is working for you :-)