H2 201 will require JTS 1.17 for passed Geometry objects

[katzyn]

The next version of H2 will be able to accept JTS Polygon objects only with jts-core 1.17 in the classpath due to incompatible change in JTS.

[nicolas-f]

It will be necessary to have an explicit error message if this is not the case, is this technically possible?

[katzyn]

AFAIK, development versions of H2GIS use own snapshot builds of H2 and they throw java.lang.NoSuchMethodError: 'org.locationtech.jts.geom.CoordinateSequence org.locationtech.jts.geom.CoordinateSequenceFactory.create(int, int, int)' with JTS 1.15 and nobody cares. Current H2 throws java.lang.NoSuchMethodError: 'org.locationtech.jts.geom.LinearRing org.locationtech.jts.geom.Polygon.getExteriorRing()' with JTS 1.16. Situation is the same.

H2 can check JTSVersion.CURRENT_VERSION.get***(), but there is no guarantee that the next release of JTS will be compatible with JTS 1.17. Should we restrict newer versions too? No, because hypothetically they can be compatible, who knows? From my point of view there is no simple reliable solution.

In H2's OSGi bundle description JTS 1.17.0+ is optionally required.

[nicolas-f]

Thanks. Maybe we could check for JTS version on call to H2GIS init db function and log a warning if < 1.17

[ebocher]

@SPalominos we must build a new H2 snapshot (2.0.201-SNAPHOT) in order to use the last JTS version 1.17.1 that contains many important changes. By the way, H2GIS will be compatible with the last Geotools version that uses > 1.17

[ebocher]

https://github.com/ebocher/H2GIS/tree/update_jtsversion

[ebocher]

I'm merging H2GIS with the last H2 master. I have some trouble with a function that return a ValueArray.

public class FindGeometryMetadata extends DeterministicScalarFunction{

    public FindGeometryMetadata() {
        addProperty(PROP_REMARKS, "Extract geometry metadata from its create table signature."
                + "eg : GEOMETRY; GEOMETRY(POINT); GEOMETRY(POINT Z); GEOMETRY(POINTZ, 4326)...");
    }

    @Override
    public String getJavaStaticMethod() {
        return "extractMetadata";
    }

    /**
     * Extract the geometry metadata from its OGC geometry data type
     *
     * Examples:
     *
     * POINT
     * POINT Z
     * POINT ZM
     *
     * @param geometryTableSignature
     * @return an array of values with the following values order
     * values[0] =   GEOMETRY_TYPE CODE
     * values[1] = COORD_DIMENSION 
     * values[2] = SRID 
     * @throws SQLException
     */
    public static ValueArray extractMetadata(String geometryTableSignature) throws SQLException {
        GeometryMetaData geomMeta = GeometryMetaData.createMetadataFromGeometryType(geometryTableSignature);
        Value[] values = new Value[3];
        values[0] = ValueVarchar.get(String.valueOf(geomMeta.getGeometryTypeCode()));
        values[1] = ValueVarchar.get(String.valueOf(geomMeta.getDimension()));
        values[2] = ValueVarchar.get(geomMeta.getSfs_geometryType());
        return ValueArray.get(values, null);
    }

}

When I try to use it as

SELECT   FindGeometryMetadata(GEOMETRY_TYPE)[1]  as geometry_type  FROM INFORMATION_SCHEMA.COLUMNS  WHERE DATA_TYPE = 'GEOMETRY';

It returns

org.h2.jdbc.JdbcSQLDataException: Valeur invalide "JAVA_OBJECT" pour le paramètre "Array"
Invalid value "JAVA_OBJECT" for parameter "Array"; SQL statement:

@katzyn
Any idea.
Am I doing something wrong

ARRAY_GET(FindGeometryMetadata(GEOMETRY_TYPE),1) returns the same error

[katzyn]

I think your function needs to return String[] instead.

[ebocher]

Excellent !
So simple.
Thanks

[ebocher]

With the previous H2 syntax we were able to write

CREATE TABLE TABLE_PROPERTIES_READ (THE_GEOM GEOMETRY(POLYGON,0),LEQ_PROFILE ARRAY)

We use the ARRAY data type to store json array.
The ARRAY data type has been removed from the master but there is now a JSON type.
Do we need to store a JSON ARRAY in a JSON data type in H2 or store the array as a VARCHAR ?

I'm in favor of the first option but comments are welcome.

@nicolas-f @SPalominos

[katzyn]

The ARRAY data type has been removed from the master

It wasn't removed, it was aligned with the SQL Standard to revolve long-standing issues with this data type. Now you need to specify data type of elements (CHARACTER VARYING(100) ARRAY, INTEGER ARRAY[20], JSON ARRAY[3], etc.)

[katzyn]

but there is now a JSON type

JSON data type was introduced in 1.4.200. In 2.0 it got an optional maximum length parameter in bytes and its length was also limited to 1,048,576 bytes.

[ebocher]

COLUMN_NAME from INFORMATION_SCHEMA.INDEXES has be moved to another schema table
So this query

select * from INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = 'SHPTABLE' and COLUMN_NAME='THE_GEOM'

must be updated

[ebocher]

A print on st.executeQuery("select * from INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = 'SHPTABLE'");

returns

Column : INDEX_CATALOG -  Value : DBH2SHPTEST
Column : INDEX_SCHEMA -  Value : PUBLIC
Column : INDEX_NAME -  Value : SHPTABLE_ROWID_
Column : TABLE_CATALOG -  Value : DBH2SHPTEST
Column : TABLE_SCHEMA -  Value : PUBLIC
Column : TABLE_NAME -  Value : SHPTABLE
Column : INDEX_TYPE_NAME -  Value : INDEX
Column : IS_GENERATED -  Value : FALSE
Column : REMARKS -  Value : null
Column : INDEX_CLASS -  Value : org.h2gis.functions.io.file_table.H2TableIndex

@katzyn can you point me where is stored the COLUMN_NAME.
thanks

[katzyn]

See INFORMATION_SCHEMA.INDEX_COLUMNS.

[ebocher]

Thanks

[ebocher]

Currently H2GIS has a workaround to return a SRID when the INFORMATION_SCHEMA.COLUMNS return a SRID equals to 0.
See https://github.com/orbisgis/h2gis/blob/master/h2gis-functions/src/main/java/org/h2gis/functions/spatial/metadata/FindGeometryMetadata.java#L75
This is useful in this case

 st.execute("DROP TABLE IF EXISTS input_table,grid;"
                + "CREATE TABLE input_table(the_geom Geometry(POLYGON, 4326));"
                + "INSERT INTO input_table VALUES"
                + "(ST_GeomFromText('POLYGON((0 0, 2 0, 2 2, 0 0))', 4326));");
st.execute("CREATE TABLE grid AS SELECT * FROM st_makegrid('input_table', 1, 1);");

The srid column of the geometry_columns view is populated with the value 4326.

Do we need to keep this behaviour ?

Note : In postgis, user must update manually the srid with UpdateGeometrySRID function

@nicolas-f @SPalominos

[katzyn]

Please note that in H2, unlike in PostGIS, SRID=0 doesn't have any special meaning.

CREATE TABLE TEST(A GEOMETRY, B GEOMETRY(GEOMETRY, 0));
SELECT COLUMN_NAME, GEOMETRY_SRID FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'GEOMETRY';
> COLUMN_NAME  	GEOMETRY_SRID  
> A             null
> B             0

When GEOMETRY_SRID is 0, only geometry values with default (0) SRID may be inserted in that column.
When GEOMETRY_SRID is NULL, geometry values with any SRID may be inserted.

In PostGIS, GEOMETRY(GEOMETRY, 0) accepts geometry objects with any SRID if I remember it well.

[ebocher]

ST_IsValidDetail returns an array of Object[] (see https://github.com/orbisgis/h2gis/blob/master/h2gis-functions/src/main/java/org/h2gis/functions/spatial/properties/ST_IsValidDetail.java#L76)
seems that this kind of return is not supported anymore
It returns

Data conversion error converting "GEOMETRY, BOOLEAN"; SQL statement:

@katzyn
Is there a way to return a mixed array with boolean , varchar and geometry values.
PostGIS has the same functionality https://postgis.net/docs/ST_IsValidDetail.html

[ebocher]

Please note that in H2, unlike in PostGIS, SRID=0 doesn't have any special meaning.

CREATE TABLE TEST(A GEOMETRY, B GEOMETRY(GEOMETRY, 0));
SELECT COLUMN_NAME, GEOMETRY_SRID FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'GEOMETRY';
> COLUMN_NAME  	GEOMETRY_SRID  
> A             null
> B             0

When GEOMETRY_SRID is 0, only geometry values with default (0) SRID may be inserted in that column.
When GEOMETRY_SRID is NULL, geometry values with any SRID may be inserted.

In PostGIS, GEOMETRY(GEOMETRY, 0) accepts geometry objects with any SRID if I remember it well.

I have cast the GEOMETRY_SRID as int so it returns 0 in the geometry columns view.
https://github.com/ebocher/H2GIS/blob/update_jtsversion/h2gis-functions/src/main/java/org/h2gis/functions/factory/H2GISFunctions.java#L367

[katzyn]

Is there a way to return a mixed array with boolean , varchar and geometry values.

No, we intentionally don't support that any more.

Actually a row value data type should be used for that purpose, but our API for user-defined functions doesn't support this data type yet.

[ebocher]

Thank you. I will use a string array while waiting for this feature to be implemented in H2.

h2database/h2database#2909

[katzyn]

I have cast the GEOMETRY_SRID as int so it returns 0 in the geometry columns view.

I don't understand what do you mean. Cast from INTEGER to INTEGER (INT is a short name of INTEGER) does nothing. It doesn't convert NULL to 0. ResultSet, however, returns 0 from getInt() method, that's why applications should call wasNull() to distinguish 0 from NULL when they need, or they can use getObject() instead of these two methods.

BTW, you shouldn't use PostgreSQL-specific syntax in H2-specific code. Cast to INTEGER, when you need it, should be written as CAST(value AS INTEGER). H2 currently allows value::INTEGER too, but this syntax is not officially supported (and newer was) and may be restricted in the future to PostgreSQL compatibility mode.

[ebocher]

I have cast the GEOMETRY_SRID as int so it returns 0 in the geometry columns view.

I don't understand what do you mean. Cast from INTEGER to INTEGER (INT is a short name of INTEGER) does nothing. It doesn't convert NULL to 0. ResultSet, however, returns 0 from getInt() method, that's why applications should call wasNull() to distinguish 0 from NULL when they need, or they can use getObject() instead of these two methods.

You are right. It's time to take a break ;(

[ebocher]

About geojson or json file drivers, I propose to import array as varchar or clob, especially for this kind of array :

"testArray":[49, 40.0, {"member1":1, "member2":{"member21":21,"member22":22}}, "string", [13, "string", {"member3":3, "member4":4}]],
                ``` 
Users will then have to use json functions to manipulate this string.

@SPalominos , @nicolas-f 

[katzyn]

Everything longer than 1,048,576 bytes or characters need to use BLOB or CLOB data type in H2 2.0.

[ebocher]

Thanks.

getObject() on Value has been remove so this kind of set

preparedStatement.setObject(columnId + 1, shpDriver.getField(rowId, columnId).getObject());

from https://github.com/orbisgis/h2gis/blob/master/h2gis-functions/src/main/java/org/h2gis/functions/io/shp/SHPDriverFunction.java#L325

doesn't work any more.

Is there another way to do that ?

[katzyn]

What is the data type of shpDriver.getField(rowId, columnId)?

[katzyn]

You can use JdbcUtils.set(PreparedStatement, int, Value, JdbcConnection) method, but it isn't a part of API and may be modified in any version of H2.

[ebocher]

Yes but my code works also with a postgis jdbc connection so I cannot set a JdbcConnection

[ebocher]

This file driver is able to import data in H2 and POSTGIS

[katzyn]

The first two arguments of this method belong to the target database and this database isn't required to be the H2 Database.

The last two arguments belong to the source database and the source database must be H2.

If you read this value from PostgreSQL, why it has the org.h2.value.Value data type?

[katzyn]

Actually when target database is H2, the connection from the source database may be null. When target database is not H2, the connection from the source database is also only used for ARRAY and JAVA_OBJECT data types.

[katzyn]

Note that this method passes GEOMETRY values as byte[] containing big-endian EWKB to other DBMS.

[ebocher]

I read the file and I insert the values in a PostgreSQL table.
Not sure to understand everything but the file driver is not linked with a H2 database. It uses H2 value to return value.
Maybe I have to change so that it will return Object[] instead of value and them cast the object to H2 value

[katzyn]

If you don't construct values of ARRAY and JAVA_OBJECT data types, you can use JdbcUtils.set(targetPreparedStatement, targetParameterIndex, yourValue, null).

[ebocher]

Thank you. Sorry my internet connection has some pbs.
I will test it

[ebocher]

JdbcUtils.set(targetPreparedStatement, targetParameterIndex, yourValue, null). rocks !

[ebocher]

DROP TABLE IF EXISTS json_table;
CREATE TABLE json_table (test json);

INSERT INTO json_table values('[49, 40.0, {"member1":1, "member2":{"member21":21,"member22":22}}, "string", [13, "string", {"member3":3, "member4":4}]]');

SELECT CAST (test AS Varchar)  FROM json_table;

returns the Varchar representation

 ResultSet res = stat.executeQuery("SELECT * FROM json_table;");
 res.next();
res.getString(1)

return a bytes representation.

does it make sense to return the string representation of the JSON type ?

[katzyn]

H2 converts boolean values to JSON scalar boolean value, numeric values to JSON scalar number, and character string values to JSON scalar string value.

You need to use the JSON literal instead of character string literal:
https://h2database.com/html/grammar.html#json

If you want to pass a java.lang.String value to a PreparedStatement, you need to use the FORMAT JSON clause:
https://h2database.com/html/datatypes.html#json_type

[ebocher]

I use
cast(? as json) to insert literal string

[ebocher]

Test with ? FORMAT json returns a bytes representation.
See : https://github.com/ebocher/H2GIS/blob/update_jtsversion/h2gis-functions/src/main/java/org/h2gis/functions/io/geojson/GeoJsonReaderDriver.java#L254

[katzyn]

H2 converts a character string value to a JSON String value exactly as required by the SQL Standard, for example, '[]' is converted to JSON '"[]"' by explicit or implicit cast to JSON. I guess it isn't your intention. If ? is a character string with JSON text, the only way to let H2 know that it is not a JSON string, but a JSON text is to use the standard FORMAT JSON clause. There are no other ways.

I don't know what you mean by “bytes representation” here.

try (Connection c = DriverManager.getConnection("jdbc:h2:mem:")) {
    Statement s = c.createStatement();
    s.execute("CREATE TABLE json_table (test json)");
    s.execute("INSERT INTO json_table values(JSON '[49, 40.0, {"
            + "\"member1\":1, \"member2\":{\"member21\":21,\"member22\":22}},"
            + " \"string\", [13, \"string\", {\"member3\":3, \"member4\":4}]]')");
    PreparedStatement ps = c.prepareStatement("INSERT INTO json_table VALUES ? FORMAT JSON");
    ps.setString(1, "[49, 40.0, {" + "\"member1\":1, \"member2\":{\"member21\":21,\"member22\":22}},"
            + " \"string\", [13, \"string\", {\"member3\":3, \"member4\":4}]]");
    ps.executeUpdate();
    try (ResultSet rs = s.executeQuery("TABLE json_table")) {
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
    }
}

produces

[49,40.0,{"member1":1,"member2":{"member21":21,"member22":22}},"string",[13,"string",{"member3":3,"member4":4}]]
[49,40.0,{"member1":1,"member2":{"member21":21,"member22":22}},"string",[13,"string",{"member3":3,"member4":4}]]

just like expected.

[ebocher]

I use the approach with
PreparedStatement ps = c.prepareStatement("INSERT INTO json_table VALUES ? FORMAT JSON");
ps.setObject(1,"[49, 40.0, {" + ""member1":1, "member2":{"member21":21,"member22":22}},"
+ " "string", [13, "string", {"member3":3, "member4":4}]]"); )

and rs.getString(1)

returns

WzQ5LDQwLjAseyJtZW1iZXIxIjoxLCJtZW1iZXIyIjp7Im1lbWJlcjIxIjoyMSwibWVtYmVyMjIiOjIyfX0sInN0cmluZyIsWz....

[katzyn]

I can't reproduce it with current H2. Actually you should use setString(), but setObject() works too.

[katzyn]

Your string is a Base64-encoded JSON. H2 does not work with Base64 at all. Something else performs Base64 encoding in your environment.

[ebocher]

Maybe due to java 11.

[ebocher]

@SPalominos

When I try to run H2 version available in nexus.orbisgis.org, it returns

org.h2.jdbc.JdbcSQLNonTransientException: Erreur générale: "java.lang.NoSuchMethodError: java.nio.ByteBuffer.position(I)Ljava/nio/ByteBuffer;"
General error: "java.lang.NoSuchMethodError: java.nio.ByteBuffer.position(I)Ljava/nio/ByteBuffer;" [50000-201]

I'm not able to open any databases with a JAVA 8 version

[katzyn]

You need to compile H2 with Java 8. Compilation on newer versions creates incompatible jar even if you use Java 8 target of Javac.

[katzyn]

But your problem with Base64 isn't related with H2 on Java 11, CI tests of H2 use that Java version too.

[ebocher]

We are on the way to finish the H2 and JTS update.

@SPalominos
Ready to test on your machine and fix the last issues.

There is one important issue that I'm not able to fix. It's about spatial index on linked table.
The test https://github.com/ebocher/H2GIS/blob/update_jtsversion/h2gis-functions/src/test/java/org/h2gis/functions/io/shp/SHPEngineTest.java#L261 doesn't work because we are not able to define when H2 use a spatial index on a linked table.
It seems that some metadata are missing on class H2TableIndex used in H2GIS.
Indeed INFORMATION_SCHEMA.INDEXES.INDEX_TYPE_NAME return the value INDEX. See : https://github.com/ebocher/H2GIS/blob/update_jtsversion/h2gis-utilities/src/main/java/org/h2gis/utilities/GeometryTableUtilities.java#L1356

[katzyn]

Indeed INFORMATION_SCHEMA.INDEXES.INDEX_TYPE_NAME return the value INDEX.

H2 returns SPATIAL INDEX for own spatial indexes only. H2 doesn't know types of indexes in linked tables, because H2 uses java.sql.DatabaseMetaData for them where information about index type isn't available.

[ebocher]

Yes but it was working in the last version. see https://github.com/orbisgis/h2gis/blob/master/h2gis-functions/src/test/java/org/h2gis/functions/io/shp/SHPEngineTest.java#L298

[ebocher]

Maybe I missed something.

[ebocher]

Migration completed. We are now in-line with the last H2 master.
We use also JTS 1.18
H2GIS master will be replaced by this new version very soon.
@nicolas-f , @SPalominos

[ebocher]

This is done here #1160
We are now cleaning the pom.
A big thanks to H2 database community @grandinj and especially @katzyn
We are on the way to publish a new H2GIS version and work now on raster support as in PostGIS