stefanoa0
Geo.Postgis with CockroachDB - (Postgrex.Error) ERROR 42883 (undefined_function) st_contains()
Hello everyone, I hope you are well.
I’m migrating from PostgreSQL to CockroachDB and everything was going well until I came across the following error using the Geo.Postgis lib:
** (Postgrex.Error) ERROR 42883 (undefined_function) st_contains(): unknown signature: st_buffer(geometry, geometry) (desired <geometry>)
Here is the specific where snippet:
|> where(
[u],
st_contains(st_buffer(st_set_srid(st_make_point(^lng, ^lat), 4326), ^distance), u.geo_point)
)
I tried changing to a fragment function with the string:
fragment("ST_Contains(ST_Buffer(ST_SetSRID(ST_MakePoint($2, $3), 4326), $4), u0.geo_point)", lng, lat, distance)
But the same error happened
Has anyone had a similar error?
Thanks for your time!
Marked As Solved
stefanoa0
Hello
Thanks for this informations, but the CockroachDB is compatible with PostgresSQL, as described:
[> CockroachDB supports the PostgreSQL wire protocol and the majority of PostgreSQL syntax. This means that existing applications built on PostgreSQL can often be migrated to CockroachDB without changing application code.]
So, the migrations was easy and all the queries are running very well, but that query who uses the Postgis functions needed to changed, I had to cast the distance to float and It’s works!
|> where(
[u],
st_contains(st_buffer(st_set_srid(st_make_point(^lng, ^lat), 4326), ^fragment("Cast(? as FLOAT)", distance), u.geo_point)
)
So, thanks for your help and time!
Also Liked
pdgonzalez872
hi @stefanoa0 , welcome to ElixirForum!
So, ST_Contains is not native to Postgres, it comes from the postgis extension. You can use the function when you install an extension. Here is the documentation for the function: ST_Contains.
The lib you are using is a wrapper around the postgis extension, that’s why your fragment didn’t work. Postgres just doesn’t know a function with that name.
Here is a link that will help you migrate that specific functionality over: How we built scalable spatial data and spatial indexing in CockroachDB
I love Postgres, including the postgis library. Sad to see you migrate away from Postgres, but good luck nonetheless!
pdgonzalez872
Great!
Thanks for this informations, but the CockroachDB is compatible with PostgresSQL, as described:
Well, we saw that in this case it wasn’t a 1 to 1 since you had to refactor that function call. BUT, on CockRoachDB’s defense, postgis is not part of core, it’s an extension to Postgres even though it is the de-facto way of dealing with spacial data in Postgres. So, their claim could still very well be valid.
So, thanks for your help and time!
You are welcome!
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Supporting innovation across the BEAM ecosystem.
Categories:
Sub Categories:
Forums
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.
Practical resources that improve the lives of professional developers.
Enabling companies to succeed by building software people love.
The deployment platform built for Elixir: PaaS ease, VPS control.
Courses that'll move you from confusion to "Aha, now I get it!"