Fetching centroid of geometry objects

Ash Forum Ash Questions / Help
, , ,
1

I have a resource (MyApp.MapPin) that has an attribute :coordinates of type :geometry. I’m trying to implement a clustering feature that will cluster pins that are close together for easier viewing. These MapPins also have filter policies associated with them. It makes the most sense to use functionality from PostGIS for this, so the query I’d like to create is this:

SELECT COUNT(*), ST_centroid(ST_collect(mp.coordinates))
FROM map_pins mp
WHERE ST_makeenvelope(?, ?, ?, ?, 4326) ~ mp.coordinates
-- Additional filters from applied policies

Is there a way to create an Ash query that could return the result I’m looking for? Or perhaps a way to get the filter commands from policies to inject into a raw query? Or even a better way that I haven’t thought of?

I know AshGeo has the st_centroid function exposed for Ash expressions, but expressions can’t be used in select statements.

The best I’ve come up with so far is a separate query that fetches the ids of the map_pins and then doing a Repo.query with an in clause on those ids.

2

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.

3

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?

4

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

https://hexdocs.pm/ash_postgres/AshPostgres.CustomAggregate.html

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 :slight_smile:

1 Like
5

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]}
)
1 Like