Help dealing with postgis geometry column

[minva]

Hi,

I've got a table that has a single, generic geometry column. When it comes to insert, update and retrieve, I'd like the value type to be a GeoJSON type, a simple POJO.

This is how I'd like my SQL insert/update statement to look, but I can't figure out how to get my converter/binder to do this:

`insert into places (id, geom) values("foo", st_geomFromGeoJson(?))`

Where the value for the geom is a GeoJSON string.

For queries/reads, this is what the SQL looks like:

`select id, st_asGeoJson(geom) from places`

I've looked for examples of how to do this but not finding anything yet. I've setup my generator project with a ForcedType and have a converter/binder but I've had no luck in getting this to work so far. Any tips or examples would be extremely appreciated!

Thanks!

[Lukas Eder]

Thanks for your message.

We don't have a feature yet that allows you to transform projections and bind values using SQL functions. There's a feature request for this:

https://github.com/jOOQ/jOOQ/issues/8842

There's a client side computed column feature that allows for doing this in one direction only, i.e. you'd need 2 computed columns for reading and writing, respectively, and then you could hide the original column:

https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-forced-types/codegen-database-forced-types-computed

A binding (or converter) must be entirely implemented in the client application. That's certainly an option as well, but then, you'd have to work with the org.jooq.Geometry type containing a WKT or WKB.

I hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/03ef0177-033d-4d28-86cc-f0c96c942d7bn%40googlegroups.com.