MySQL POLYGON ←→ Jts Polygon with JOOQ (as WKT)

Question

I'm trying to get a seamlessly conversion between MySQL POLYGON type, and Jts Polygon while using jOOQ (3.9.x). Ideally, I want to just parse the WKT (well known text) from the DB into the Jts type. However, the generated queries are not working, I see that they get simple quote marks around the function that converts to text, rendering it just text. Here's the converter and the binding that I'm using. Am I doing this right? How should I approach the conversion via ST_AsWKT and ST_GeomFromText?

public class PolygonConverter implements Converter<Object, Polygon> {


    /**
     * Convert WK string into Polygon
     */

    @Override
    public Polygon from(Object databaseObject) {
        if (databaseObject == null) {
            return null;
        }
        String wkString = databaseObject.toString();
        WKTReader reader = JtsSpatialContext.GEO.getWktShapeParser();
        try {
            Polygon poly = (Polygon)reader.parse(wkString);
            return poly;
        } catch (java.text.ParseException e) {
            throw new IllegalArgumentException(e);
        }
    }

    @Override
    public Object to(Polygon userObject) {
        if (userObject == null) {
            return null;
        }
        return userObject.toString();
    }

    @Override
    public Class<Object> fromType() {
        return Object.class;
    }

    @Override
    public Class<Polygon> toType() {
        return Polygon.class;
    }
}

The Binding:

public class MySQLPolygonBinding implements Binding<Object, Polygon> {
    @Override
    public Converter<Object, Polygon> converter() {
        return new PolygonConverter();
    }

    @Override
    public void sql(BindingSQLContext<Polygon> ctx) throws SQLException {
        ctx.render().visit(DSL.sql("ST_AsWKT(?)"));
    }

    @Override
    public void register(BindingRegisterContext<Polygon> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }

    @Override
    public void set(BindingSetStatementContext<Polygon> ctx) throws SQLException {
        String resultStr = null;
        Object obj = ctx.convert(converter()).value();
        if (obj != null) {
            resultStr = String.format("ST_GeomFromText('%s')", obj,toString() );
        }
        ctx.statement().setObject(ctx.index(), resultStr);
    }

    @Override
    public void set(BindingSetSQLOutputContext<Polygon> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }

    @Override
    public void get(BindingGetResultSetContext<Polygon> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.resultSet().getString(ctx.index()));
    }

    @Override
    public void get(BindingGetStatementContext<Polygon> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.statement().getString(ctx.index()));
    }

    @Override
    public void get(BindingGetSQLInputContext<Polygon> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }
}

Answer 1

Note that starting from jOOQ 3.16 (see #982), jOOQ supports various popular GIS implementations out of the box.

From what I understand, you'd like two things to happen automatically:

String bind values should be wrapped with the ST_GeomFromText() function

This is the "easy" part and you almost got it right:

@Override
public void sql(BindingSQLContext<Polygon> ctx) throws SQLException {
    ctx.render()
       .sql("ST_GeomFromText(")
       // This will use your converter to convert from a Polygon to "Object"
       // prior to binding the variable
       .visit(DSL.val(ctx.convert(converter()).value()))
       .sql(")");
}


@Override
public void set(BindingSetStatementContext<Polygon> ctx) throws SQLException {
    // No wrapping of the bind variable in functions can be done here!
    ctx.statement().setString(ctx.index(), ctx.convert(converter()).value());
}

Polygon type columns should be formatted with the ST_AsWKT() function

You (probably) don't have to do anything specific here inside of the binding, as your binding cannot influence any expression where no bind variable is involved (e.g. an ordinary column expression).

Instead, you might need to adapt your converter (the one inside the binding) to be able to read any type that comes back from the JDBC driver and convert that into a Polygon