Re: How to create a TableField for postgis.Point ?

[Lukas Eder]

Hi Mike

There is a pending feature request to add support for GIS libraries in
MySQL and Postgres:
https://github.com/jOOQ/jOOQ/issues/982

I have not yet spent any time investigating a possible implementation.
One way for you to circumvent this is to use custom converter types.
Here's the manual's section about how to write your custom converter
and use it with the jOOQ API:
http://www.jooq.org/doc/2.5/manual/sql-execution/fetching/data-type-conversion/

And here's the section where you can wire your converter to generated classes:
http://www.jooq.org/doc/2.5/manual/code-generation/custom-data-types/

In case you find a solution along these lines, I'd be interested to
see how it works. If you're willing to contribute, this could make it
into the jOOQ core

Cheers
Lukas

2012/10/24 <mva...@googlemail.com>:
> Hello,
>
> I would like to use the generated Record-classes for easy reading and
> writing the entities from and to the database.
> But I got a postgis.Point column in a table.
> I tried to create an own GeometryTableField from CustomTableField but that
> failed at the point where the
> constructor of the Field needs a DataType. Making an own DataType failed
> because the
> DataType's constructor needs a SQLDataType<Point>. But I can't extend
> SQLDataType.
>
> Here's the code:
>
> public class GeometryDataField extends CustomField<Point>{
>
> private static final long serialVersionUID = 1L;
>
> protected GeometryDataField(String name, DataType<Point> type) {
> super(name, type);
> }
> @Override
> public void toSQL(RenderContext context) {
> }
> @Override
> public void bind(BindContext context) throws DataAccessException {
> }
> }
>
> and
>
> public class GeometryDataType extends AbstractDataType< Point> {
> private static final long serialVersionUID = 1L;
>
> protected GeometryDataType(SQLDialect dialect, SQLDataType<Point>
> sqldatatype,
> Class<? extends Point> type, String typeName) {
> super(dialect, sqldatatype, type, typeName);
> }
> }
>
> There is no "SQLDataType<Point>" and the constructor of SQLDataType is
> private, furthermore the class is final.
>
> Where can I find a description how to make TableFields for Postgis types or
> otherwise use the postgis.Point with Jooq ?
>
> Greetings
>
> Mike

[Lukas Eder]

The question is, how would you read an org.postgis.Point object
through JDBC? Would you use ResultSet.getObject() (i.e. does the
Postgres JDBC driver provide you with a Point), or would you use
ResultSet.getString() and deserialise the Point yourself. In the first
case, you'd have to convert Object to Point and vice versa. In the
latter case, you'd have to convert String to Point and vice versa. In
principle, code generation using converters should work. Could you
please post your generator configuration XML?

Could you also provide me with some DDL and further instructions, such
that I can experiment with PostGIS myself? It'll be easier to give you
directions that way...

Cheers
Lukas

2012/10/24 <mva...@googlemail.com>:
> Hello,
>

> thanks for your quick reply.
>
> I have already tried to make a Converter.
> But the classes from the generatorTool didn't seam to make use of it.
> This field doesn't work (in BuildingTable):
> public final org.jooq.TableField<BuildingtableRecord, org.postgis.Point>
> GEOMETRY = createField("geometry",
> org.jooq.util.postgres.PostgresDataType.getDefaultDataType("USER-DEFINED"),
> this);
>
> As far as I understand the Converter's purpose, it converts from a datatype
> it can actually read from the database. But that's the problem here.
> I would like to create the sql statements for the Postgis.Point myself. But
> I don't know which classes I must extend so that the whole thing works with
> the Records. Is that possible by overriding the CustomField class ?
> The only other solution I see is to abandon the created Records and do the
> reading and writing myself for my entities. But I would really like to use
> Record.storeUsing and so on.
>
> Greetings
>
> Mike

[Lukas Eder]

Hello and thanks for the feedback.

It looks as though something could be done within jOOQ's
PostgresFactory to simplify the interaction with PostGis... I hope I
will have time to play around with this at some stage. Optionally, you
could also try to generate the routines in the pg_catalog schema. You
might find those ST_xxx functions in there and use the generated code
with the jOOQ API

Cheers
Lukas

2012/11/13 <mva...@googlemail.com>:
> Hello,
>
> sorry for the late reply.
>
> Now I'm not using the special Records from tables.records anymore.
> I replace the geometry field in the table's class with
>
>> public final Field<?> POSITION = Factory.fieldByName("position");
>
>
> For reading I add all fields I want to a list of fields.
> This list is then used in
>>
>> create.select(listOfFields).from(...)
>
>
> The geometry field for postgres looks like this.
>>
>> Factory.field("ST_AsText(position)).as("position");
>
> This reads the geometry as a wkt string.
>
> After fetching the result records I get this field's content as a String.
>>
>> record.getValueAsString(table.POSITION);
>
>
> I think to use the special records one must create a special field class
> which uses the appropriate sql functions to read and write geometry as a wkt
> string.
> This way the user could decide for himself which java geometry class he
> wants to use. He just have to create the geometry object from the wkt
> string.
>
> For writing the geometry I use
>>
>>
>> map.put(table.POSITION,Factory.field("ST_GeomFromText("+wktGeometryString+")"));
>
>
> PostGIS can create geometry columns in the database's table. This way it is
> possible to do an area search within the database to select only rows which
> are located inside a given polygon, for example.
> I mean the geometry is stored in an internal format within the database
> which neither has anything to do with org.postgis.Point itself nor is stored
> as a string.
> As seen above it would be necessary to use the sql functions to read and
> write the geometry.
>
> Greetings from Dresden
>
>
>