ryanrborn

ryanrborn

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.

Showing Posts 1 to 4

zachdaniel

zachdaniel

Creator of Ash

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.

ryanrborn

ryanrborn OP

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?

zachdaniel

zachdaniel

Creator of Ash

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

ryanrborn

ryanrborn OP

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]}
)
— All posts loaded —

Where Next? Top

Trending in Questions Top

Hellowman
Hello everyone! I am new to coding with Elixir/Erlang. And i am looking for some utilities for fuzzing testing like AFL++. Does anyone ...
New
yarrichar
I’m very new to Elixir, so sorry if this is a silly question: I am wondering what happens to the mailbox of a process when the machine i...
New
pragdave
How do I tell Hologram not to escape HTML when substituting into templates? {Textile.render(@product.long_description)} escapes the HTM...
New
mandar1045
I load tested a plain Phoenix chat app backed by Postgres with 100 million messages in it, everything on default settings, to see what br...
New
Fl4m3Ph03n1x
Background When trying to execute mix release on a Windows 11 machine for a Phoenix project I get the following error: * assembling mark...
New
rlipscombe
I’d like to create a file with 600 permissions (rw-------), but there’s a race condition if I create it and then change the permissions. ...
New
saleyn
Is there a way to import record definitions in the iex shell for pretty printing (similar to the rr command in the erl shell)? This woul...
New

Other Trending Topics Top

GenericJam
Edit: 2026 May 15 - This post is archived. Mob is alive!! Main docs: mob v0.7.11 — Documentation A bit of explanation for the slightly c...
261 13183 112
New
mudasobwa
I fully migrated to my own harness from Anthropic/Gemini and I think it’s time to share it. Welcome DSH, the DeepSeek Harness, fully writ...
New
OndrejValenta
(I just needed to vent somewhere and LinkedIn is full of hope, or hype, I’m not sure which exactly) AI dream has many faces, but general...
New
kip
Localize is the next generation localisation library for Elixir. Think of it as ex_cldr version 3.0. The first version will be released ...
New
jimsynz
Beam Bots (or just BB for short) is a framework for building fault-tolerant robotics applications in Elixir using familiar OTP patterns. ...
New
mcass19
ExRatatui lets you cook up rich terminal UIs in Elixir, powered by Rust’s ratatui via Rustler NIFs. Build interactive terminal applicatio...
New

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews