Fetching centroid of geometry objects

but expressions can’t be used in select statements.

Not quite true. Calculations can use expressions.

calculate :centroid, :point, expr(fragment("ST_centroid(ST_collect(?))", coordinates))

Then you can load that calculation.

True, calculations can use expressions. Though, ST_centroid is an aggregate function like count or average, so postgres returns an error saying that all the other columns need to appear in the GROUP BY clause.
As I understand calculations, they’re meant to operate individually on each record rather than collectively on a group of records.

There might be a solution using Ash aggregates, though I don’t understand them as well as I should. Don’t they require to be used with a relationship? Would I need to create another resource that somehow relates to the MapPin without a foreign key or something?

Ahh, got it. So you ought to be able to accomplish this with a custom aggregate.

So with your custom aggregate defined:

defmodule StCentroid do
  def dynamic(opts, binding)
    Ecto.Query.dynamic(
      [],
      fragment("ST_centroid(?, ?)", field(as(^binding), ^opts[:field]))
    ) 
  end
end

YourResource
|> Ash.aggregate({:centroid, :custom, implementation: StCentroid})

This is not a commonly used feature, so let me know if you hit any rough edges and we’ll get it worked out

This was it! Thank you Zach!

I will note for posterity that while calling the aggregate the field needs to be passed into the custom module and the type needs to be declared as an option to Ash.aggregate. Like this:

Ash.aggregate(:centroid, :custom, 
  type: AshGeo.Geometry,
  implementation: {StCentroid, [field: :coordinates]}
)