Implement ST_Multi

[nicolas-f]

As now H2 will check but not convert between geometry types:

https://github.com/h2database/h2database/blob/version-2.1.210/h2/src/main/org/h2/value/Value.java#L2347

            if (type != 0 && result.getTypeAndDimensionSystem() != type || srid != null && result.getSRID() != srid) {
                StringBuilder builder = ExtTypeInfoGeometry
                        .toSQL(new StringBuilder(), result.getTypeAndDimensionSystem(), result.getSRID())
                        .append(" -> ");
                extTypeInfo.getSQL(builder, TRACE_SQL_FLAGS);
                throw DbException.get(ErrorCode.DATA_CONVERSION_ERROR_1, builder.toString());
            }

Conversion to multi geometry type must now be done by the user. So maybe implementing ST_Multi method is a quick solution..

I got an issue when merging tables with UNION ALL (left multipolygon and right polygon) . Without any method to converto to multipolygon the right table..

[ebocher]

@nicolas-f Do you have a test case ?

[nicolas-f]

drop table if exists T1, T2, T3;
create table T1(the_geom Geometry(MULTIPOLYGON, 2154));
create table T2(the_geom Geometry(POLYGON, 2154));
insert into T1 VALUES (ST_GeomFromText('MULTIPOLYGON(((1 1, 7 1, 7 2, 3 2, 3 6, 1 6, 1 1)),  ((7 2, 8 2, 8 8, 3 8, 3 6, 7 6, 7 2)))', 2154));
insert into T2 VALUES (ST_GeomFromText('POLYGON((100 250, 100 350, 200 350, 200 250, 100 250))', 2154));
create table T3 as SELECT THE_GEOM FROM T1 UNION ALL SELECT THE_GEOM FROM T2;

[ebocher]

Thanks tested on PostGIS it works. This issue must be fixed on H2 side.

We must check with @katzyn

[katzyn]

What exactly do you need? To have GEOMETRY(SRID) as result of data type combination between GEOMETRY(SUBTYPE1, SRID) and GEOMETRY(SUBTYPE2, SRID)?

[nicolas-f]

I don't know in which extent PostGIS will convert geometry types. But I think the minimal conversion manageable is POLYGON->MULTIPOLYGON LINESTRING->MULTILINESTRING POINT->MULTIPOINTS

maybe also

POLYGON->GEOMETRYCOLLECTION LINESTRING->GEOMETRYCOLLECTION POINT->GEOMETRYCOLLECTION

[nicolas-f]

Still if SRID is not equal we will keep the exception

[ebocher]

PostGIS set the GEOMETRY as parent data type.

[nicolas-f]

I don't know where h2 try to guess the final column types when not specified

[ebocher]

So I mean :

• mixed dimension return a Geometry datatype
• if srid is not equal returns an exception

[ebocher]

Here there is a fix https://github.com/h2database/h2database/pull/3422 from @katzyn thanks to him H2GIS will be updated with the next H2 release.

[ebocher]

@nicolas-f
If you need this fix quickly we can use a compiled version of H2 from the OrbisGIS repo

[ebocher]

@nicolas-f Ready to implement it https://postgis.net/docs/ST_Multi.html ?

[ebocher]

I'm on it