Geo.Postgis with CockroachDB - (Postgrex.Error) ERROR 42883 (undefined_function) st_contains()

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: https://postgis.net/docs/manual-3.4/ST_Contains.html.

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!

Hi pdgonzalez872

Thanks for your response.

I saw that CoackroachDB has the functions I’m using, including running the query directly in the console and it works normally, my problem is just using the fragment in Ecto, as I need to pass the parameters to generate the geopolygon.

Isn’t there any other way to generate this fragment? I could manually sanitize the query values ​​and not allow injections

Hello

I made some progress with the error and discovered that:

When I do:

fragment("ST_Contains(ST_Buffer(ST_SetSRID(ST_MakePoint(?, ?), 4326), 1), u0.geo_point)", ^lng, ^lat)

It’s works!!! BUT the distance variable is not in use, I just changed this interpolation to a integer 1, then when I use the ^distance this appears:

(Postgrex.Error) ERROR 42883 (undefined_function) st_contains(): unknown signature: st_buffer(geometry, geometry) (desired <geometry>)

In specific that point:

st_buffer(geometry, geometry) (desired <geometry>)

The interpolation is “casting” the distance var as a geometry type, not an float type.

It’s something, but I need to resolve this now…

Alright, seems like your approach of moving to cockroach db was to change the DATABASE_URL (or equivalent) to the new db and go from there? I’m surprised things mostly worked. Neat!

The error you posted first tells us the functions are different. While there may be one that has the same name, it seems to want different arguments/be called differently than the one from postgis. From the link I mentioned above it seems like they go in depth as to why they chose a certain approach.

I think this problem is better framed as: “I need to translate this a postgis function cockroachdb’s version”. This is how I see this at least. There is likely some minor refactoring you will need to do to get this working as you’d expect, I hope you have a good test case for it. I’m sure cockroachdb folks would potentially be able to help with this, maybe try there?

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!

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!