Data conversion error MULTIPOLYGON

[gpetit]

Hi guys,

Using NoiseModelling 4.0.2 and so H2GIS 2.2.0-SNAPSHOT , I'm facing a problem.

When I want to make a difference (ST_Difference) or intersection (ST_Intersection), I have the following error

AVERTISSEMENT: Failed to execute:
    CREATE TABLE LANDCOVER_03_DIFF_D4 AS SELECT ST_Difference(b.the_geom, a.the_geom) as the_geom from RAIL_BUFF_D4 a, PBLM_Z1 b where a.the_geom && b.the_geom and st_intersects(a.the_geom, b.the_geom) because: Erreur lors de la conversion de données "MULTIPOLYGON (((913904.6529011861 6531310.962266126, 913948.9208011833 6531279.342266128, 914856.1789011916 6530907.866566111, 914973.

...

941866.0047012524 6587340.734568493, 941937.3629012528 6587258.4366684845, 942056.8503354524 6587317.529194794)))"; SQL statement:
    CREATE TABLE LANDCOVER_03_DIFF_D4 AS SELECT ST_Difference(b.the_geom, a.the_geom) as the_geom from RAIL_BUFF_D4 a, PBLM_Z1 b where a.the_geom && b.the_geom and st_intersects(a.the_geom, b.the_ [22018-214]
03 009 14:09:31 ERROR  [wps.executor] - Process execution failed
org.geotools.process.ProcessException: javax.script.ScriptException: org.h2.jdbc.JdbcSQLDataException: Erreur lors de la conversion de données "MULTIPOLYGON (((913904.6529011861 6531310.962266126, 913948.9208011833 6531279.342266128, 914856.1789011916 6530907.866

...

941866.0047012524 6587340.734568493, 941937.3629012528 6587258.4366684845, 942056.8503354524 6587317.529194794)))"
Data conversion error converting "MULTIPOLYGON (((913904.6529011861 6531310.962266126, 913948.9208011833 6531279.342266128, 914856.1789011916 6530907.866566111, 914973.809701186 6530885.341466109, 91511

...

52.259368497, 941748.0311012517 6587426.930368498, 941866.0047012524 6587340.734568493, 941937.3629012528 6587258.4366684845, 942056.8503354524 6587317.529194794)))"; SQL statement:
    CREATE TABLE LANDCOVER_03_DIFF_D4 AS SELECT ST_Difference(b.the_geom, a.the_geom) as the_geom from RAIL_BUFF_D4 a, PBLM_Z1 b where a.the_geom && b.the_geom and st_intersects(a.the_geom, b.the_ [22018-210]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:525)
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:496)
	at org.h2.message.DbException.get(DbException.java:227)
	at org.h2.message.DbException.get(DbException.java:203)
	at org.h2.value.ValueGeometry.getFromGeometry(ValueGeometry.java:85)
	at org.h2.value.ValueToObjectConverter.objectToValue(ValueToObjectConverter.java:144)
	at org.h2.schema.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:349)
	at org.h2.expression.function.JavaFunction.getValue(JavaFunction.java:40)
	at org.h2.expression.Alias.getValue(Alias.java:37)
	at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1833)
	at org.h2.result.LazyResult.hasNext(LazyResult.java:78)
	at org.h2.result.FetchedResult.next(FetchedResult.java:34)
	at org.h2.command.query.Select.queryFlat(Select.java:728)
	at org.h2.command.query.Select.queryWithoutCache(Select.java:833)
	at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197)
	at org.h2.command.query.Query.query(Query.java:494)
	at org.h2.command.dml.Insert.insertRows(Insert.java:197)
	at org.h2.command.dml.Insert.update(Insert.java:135)
	at org.h2.command.dml.DataChangeStatement.update(DataChangeStatement.java:61)
	at org.h2.command.ddl.CreateTable.update(CreateTable.java:172)
	at org.h2.command.CommandContainer.update(CommandContainer.java:174)
	at org.h2.command.Command.executeUpdate(Command.java:252)
	at org.h2.server.TcpServerThread.process(TcpServerThread.java:408)
	at org.h2.server.TcpServerThread.run(TcpServerThread.java:191)
	at java.base/java.lang.Thread.run(Unknown Source)
	at org.geoserver.script.wps.ScriptProcess.execute(ScriptProcess.java:116)
	at org.geoserver.wps.executor.ProcessStartupFilter$ProcessStartupWrapper.execute(ProcessStartupFilter.java:50)
	at org.geoserver.wps.executor.DefaultProcessManager$ProcessCallable.call(DefaultProcessManager.java:222)
	at org.geoserver.wps.executor.DefaultProcessManager$ProcessCallable.call(DefaultProcessManager.java:191)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: javax.script.ScriptException: org.h2.jdbc.JdbcSQLDataException: Erreur lors de la conversion de données "MULTIPOLYGON (((913904.6529011861 6531310.962266126, 913948.9208011833 6531279.342266128, 914856.1789011916 6530907.866566111, 

I tried to reduce the size of the input tables to zoom on the problem. Attached in the data_pblm.zip are the twos files:

• RAIL_BUFF_D4.shp : a unique big and complex geometry
• PBLM_Z1.shp : a unique small and simple geometry

I switched to DBeaver / H2GIS to resolve the pblm, but it's always the same. ST_Difference or ST_intersection returns the same error

DROP TABLE IF EXISTS RAIL_BUFF_D4, PBLM_Z1;
CALL SHPREAD ('/home/gpetit/RAIL_BUFF_D4.shp', 'RAIL_BUFF_D4');
CALL SHPREAD ('/home/gpetit/PBLM_Z1.shp', 'PBLM_Z1');

DROP TABLE IF EXISTS pblm_intersection;
CREATE TABLE pblm_intersection AS SELECT st_intersection(a.THE_GEOM, b.THE_GEOM) AS THE_GEOM 
   FROM RAIL_BUFF_D4 a, PBLM_Z1 b 
   WHERE a.THE_GEOM && b.the_geom AND st_intersects(a.THE_GEOM , b.THE_GEOM) ;

This query was initially executed on PostGIS and it was working.

Any idea ?

[nicolas-f]

One of the two PRJ file was not recognized

The error was:

Values of types "GEOMETRY(MULTIPOLYGON, 0)" and "GEOMETRY(MULTIPOLYGON Z, 2154)" are not comparable; SQL statement:
CREATE TABLE pblm_intersection AS SELECT st_intersection(a.THE_GEOM, b.THE_GEOM) AS THE_GEOM
   FROM RAIL_BUFF_D4 a, PBLM_Z1 b
   WHERE a.THE_GEOM && b.the_geom AND st_intersects(a.THE_GEOM , b.THE_GEOM) [90110-214] 90110/90110 [(Aide)](https://h2database.com/javadoc/org/h2/api/ErrorCode.html#c90110)
org.h2.jdbc.JdbcSQLSyntaxErrorException: Values of types "GEOMETRY(MULTIPOLYGON, 0)" and "GEOMETRY(MULTIPOLYGON Z, 2154)" are not comparable; SQL statement:
CREATE TABLE pblm_intersection AS SELECT st_intersection(a.THE_GEOM, b.THE_GEOM) AS THE_GEOM
   FROM RAIL_BUFF_D4 a, PBLM_Z1 b

[nicolas-f]

I don't know if it is related but
H2GIS 2.2 use CTS 1.7 but NoiseModelling 4.0.2 use CTS 1.6

[gpetit]

Hum, it's on the export, but in my db, both are in 2154

Don't mind with the table name which is different

[ebocher]

The PRJ file RAIL_BUFF_D4 doesn't contain any EPSG code so H2GIS is not able to find a SRID.

[gpetit]

Thanks Erwan. I reupladed the files here : data_pblm.zip

The PRJ are the same. But the problem remains in H2GIS db.
@nicolas-f just told me that the problem is resolved if I use H2GIS clien (https://github.com/orbisgis/h2gis/releases/download/v2.2.0/h2gis-2.2.0-bin.zip) instead of the NM db. So I will try

[ebocher]

ok super ;-)

[gpetit]

All right, the problem seems to be resolved.
The issue was not coming from SRID but from H2GIS version.
In NM v4.0.2 we are using H2GIS v2.1.120 while the H2GIS web client is based on v2.1.214

So with @nicolas-f we updated (manually) the NM version to v2.1.214 and it works ! We will have to update the release ...