ryanrborn
Fetching centroid of geometry objects
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.
Marked As Solved
zachdaniel
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 
Also Liked
ryanrborn
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]}
)
Popular in Questions
Other popular topics
Latest Ash Threads
Latest on Elixir Forum
Sponsor Spotlight
Producing high quality Elixir screencasts since 2017.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #elixirconf-us
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #security
- #hex
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"