H2GISDialect.postCreateTable ALTER TABLE GEOMETRY(POINT) error

[ccarlow]

The following error is thrown when calling DataStore.createSchema(featureType):

java.io.IOException: Error occurred creating table
    at org.geotools.jdbc.JDBCDataStore.createSchema(JDBCDataStore.java:790)
    at geotoolsfx.MapController.testDb(MapController.java:126)
    at geotoolsfx.MapController.start(MapController.java:177)
    at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$161(LauncherImpl.java:863)
    at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$174(PlatformImpl.java:326)
    at com.sun.javafx.application.PlatformImpl.lambda$null$172(PlatformImpl.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.application.PlatformImpl.lambda$runLater$173(PlatformImpl.java:294)
    at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
    at com.sun.glass.ui.gtk.GtkApplication._runLoop(Native Method)
    at com.sun.glass.ui.gtk.GtkApplication.lambda$null$48(GtkApplication.java:139)
    at java.lang.Thread.run(Thread.java:748)
Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "ALTER TABLE ""PUBLIC"".""blah2"" ALTER COLUMN ""the_geom"" GEOMETRY(POINT[*]); ALTER TABLE ""PUBLIC"".""blah2"" ADD CHECK ST_SRID( ""the_geom"")= 4326;"; expected "long"; SQL statement:
ALTER TABLE "PUBLIC"."blah2" ALTER COLUMN "the_geom" GEOMETRY(POINT); ALTER TABLE "PUBLIC"."blah2" ADD CHECK ST_SRID( "the_geom")= 4326; [42001-197]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
    at org.h2.message.DbException.getSyntaxError(DbException.java:217)
    at org.h2.command.Parser.readLong(Parser.java:3425)
    at org.h2.command.Parser.parseColumnWithType(Parser.java:4446)
    at org.h2.command.Parser.parseColumnForTable(Parser.java:4215)
    at org.h2.command.Parser.parseAlterTableAlterColumnType(Parser.java:6296)
    at org.h2.command.Parser.parseAlterTable(Parser.java:6265)
    at org.h2.command.Parser.parseAlter(Parser.java:5373)
    at org.h2.command.Parser.parsePrepared(Parser.java:362)
    at org.h2.command.Parser.parse(Parser.java:335)
    at org.h2.command.Parser.parse(Parser.java:307)
    at org.h2.command.Parser.prepareCommand(Parser.java:278)
    at org.h2.engine.Session.prepareLocal(Session.java:611)
    at org.h2.server.TcpServerThread.process(TcpServerThread.java:271)
    at org.h2.server.TcpServerThread.run(TcpServerThread.java:165)
    at java.lang.Thread.run(Thread.java:748)

    at org.h2.engine.SessionRemote.done(SessionRemote.java:623)
    at org.h2.command.CommandRemote.prepare(CommandRemote.java:85)
    at org.h2.command.CommandRemote.<init>(CommandRemote.java:51)
    at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:493)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1247)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:217)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.h2gis.geotools.H2GISDialect.postCreateTable(H2GISDialect.java:517)
    at org.geotools.jdbc.JDBCDataStore.createSchema(JDBCDataStore.java:787)
    ... 11 more

It seems to be fixed by changing the ALTER TABLE statement from + "GEOMETRY(" + geomType + "); " to + geomType + "; "

[ccarlow]

Created pull request #16

[ccarlow]

So the patch fails at least for H2GISTest.updateGeometry_Columns() which creates the table using GEOMETRY(POLYGON) so the syntax seems correct after all but it gets created via dialect.postCreateTable() so the ALTER COLUMN statement only fails when called using dataStore.createSchema().

[ccarlow]

Sample code for reference:

    String typeName = "blah";
    Map<String, Object> params = new HashMap<>();
    H2GISDataStoreFactory factory = new H2GISDataStoreFactory();
    params.put(JDBCDataStoreFactory.NAMESPACE.key, "http://www.h2gis.org/test");
    params.put(JDBCDataStoreFactory.DATABASE.key, "mem:" + typeName);
    params.put(JDBCDataStoreFactory.DBTYPE.key, "h2gis");
    params.put(JDBCDataStoreFactory.HOST.key, "localhost");
    JDBCDataStore dataStore = null;
    try {
        dataStore = factory.createDataStore( params );
        System.out.println(dataStore);
    } catch (IOException e1) {
        e1.printStackTrace();
    }

    try {
        Iterator availableStores =  DataStoreFinder.getAvailableDataStores();
        System.out.println("List available Stores: ");
        while (availableStores.hasNext()) {
            System.out.println(availableStores.next().toString());
        }

        SimpleFeatureTypeBuilder typeBuilder = new SimpleFeatureTypeBuilder();

        //set global state
        typeBuilder.setName(typeName);
        typeBuilder.setNamespaceURI( "http://www.geotools.org/" );
        typeBuilder.setSRS( "EPSG:4326" );

        //add attributes
        typeBuilder.add( "the_geom", Point.class );
        typeBuilder.add( "intProperty", Integer.class );
        typeBuilder.add( "stringProperty", String.class );
        typeBuilder.add( "pointProperty", Point.class );

        SimpleFeatureType featureType = typeBuilder.buildFeatureType();

        dataStore.createSchema(featureType);
    } catch (Exception e) {
        e.printStackTrace();
    }

[ccarlow]

I'm not entirely sure how H2GISTest.updateGeometry_Columns() is even able to use the GEOMETRY(POLYGON) syntax. Maybe factory.createSQLDialect(ds) enables something?

When I try to run the command manually it gives the original error. The example code given on the main page uses syntax "THE_GEOM MULTIPOLYGON" not "THE_GEOM GEOMETRY(MULTIPOLYGON)".

I'm not sure if the ALTER STATEMENT is even necessary in H2GISDialoect.postCreateTable(). When removed entirely it fixes both DataStore.createSchema() and H2GISTest.updateGeometry_Columns() failures.

[ccarlow]

Updated pull request with ALTER STATEMENT removed and no tests failed this time. Just need an expert to review since I'm not aware of how the change could break something else.