When i execute the line sql code :
CREATE TABLE contouring_noise_map_l93 AS SELECT st_transform(ST_setsrid("DATABASE"."PUBLIC"."contouring_noise_map".the_geom, 27572), 310024140) AS the_geom, idiso FROM "DATABASE"."PUBLIC"."contouring_noise_map";

( the SRID 310024140 = "IGNF:Lamb93")

I have this error message:
WARN Thread-81 - Ellipsoid cannot be defined
ERROR Thread-81 - Cannot create the CRS; SQL statement:
create table contouring_noise_map_l93 as select st_transform(ST_setsrid("DATABASE"."PUBLIC"."contouring_noise_map".the_geom, 27572), 310024140) as the_geom, idiso from "DATABASE"."PUBLIC"."contouring_noise_map" [0-175]
org.h2.jdbc.JdbcSQLException: Cannot create the CRS; SQL statement:
create table contouring_noise_map_l93 as select st_transform(ST_setsrid("DATABASE"."PUBLIC"."contouring_noise_map".the_geom, 27572), 310024140) as the_geom, idiso from "DATABASE"."PUBLIC"."contouring_noise_map" [0-175]
at org.h2.message.DbException.addSQL(DbException.java:128)
at org.h2.command.Command.executeUpdate(Command.java:265)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:181)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:156)
at org.h2gis.utilities.wrapper.StatementWrapper.execute(StatementWrapper.java:93)
at org.orbisgis.view.sqlconsole.actions.ExecuteScriptProcess.parseAndExecuteScript(ExecuteScriptProcess.java:118)
at org.orbisgis.view.sqlconsole.actions.ExecuteScriptProcess.run(ExecuteScriptProcess.java:136)
at org.orbisgis.view.background.Job.run(Job.java:79)
at org.orbisgis.view.background.RunnableBackgroundJob.run(RunnableBackgroundJob.java:71)
at java.lang.Thread.run(Thread.java:744)
Caused by: java.sql.SQLException: Cannot create the CRS
at org.h2gis.h2spatial.internal.function.spatial.crs.ST_Transform.ST_Transform(ST_Transform.java:120)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.h2.engine.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:467)
at org.h2.expression.JavaFunction.getValue(JavaFunction.java:39)
at org.h2.expression.Alias.getValue(Alias.java:37)
at org.h2.command.dml.Select.queryFlat(Select.java:533)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:632)
at org.h2.command.dml.Query.query(Query.java:314)
at org.h2.command.dml.Insert.insertRows(Insert.java:167)
at org.h2.command.dml.Insert.update(Insert.java:114)
at org.h2.command.ddl.CreateTable.update(CreateTable.java:183)
at org.h2.command.CommandContainer.update(CommandContainer.java:79)
at org.h2.command.Command.executeUpdate(Command.java:253)
... 8 more
Caused by: org.cts.crs.CRSException: No datum definition. Cannot create the CoordinateReferenceSystem
at org.cts.CRSHelper.createCoordinateReferenceSystem(CRSHelper.java:104)
at org.cts.CRSFactory.getCRS(CRSFactory.java:101)
at org.h2gis.h2spatial.internal.function.spatial.crs.ST_Transform.ST_Transform(ST_Transform.java:96)
... 23 more
Overall execution time: 0,073 seconds

The following functions from the GDMS operators package must be added to H2GIS:

• ST_GeomUnion
• ST_GeomUnionArg
• ST_RingBuffer

If you decide to work on this, please specify in the comments which functions you plan to implement.

Implement the ST_Is3D method as ISO SQL MM proposes.

Purpose: Test if a Geometry has a z-coordinate.

Definition:

CREATE METHOD ST_Is3D()
RETURNS INTEGER
FOR ST_Geometry
RETURN SELF.ST_PrivateIs3D

Description

1. The method ST_Is3D has no input parameters.
2. The null-call method ST_Is3D returns the value of the ST_PrivateIs3D
   attribute.

Should update the javadoc about ST_Transform relative to the term CRS,SRS, codeEPSG, SRID
see PR #154

When compiling H2GIS, I get 86 different warnings from org.cts.CRSHelper that look like this:

104 [main] WARN org.cts.CRSHelper - A component has already been register for key EPSG:9602.

It would be nice to fix this so that it doesn't pollute the output.
Full stacktrace

@jparty

Could you delete the function ST_M? (H2GIS/h2spatial/src/main/java/org/h2gis/h2spatial/internal/function/spatial/properties/ST_M.java)
Because this function is not supported by JTS and does not run in H2GIS.

Currently the SimpleResulSet class is not able to understand the Geometry so the following code doesn't work to create a geometry table

SimpleResultSet srs = new SimpleResultSet(this);
        srs.addColumn("the_geom", Types.JAVA_OBJECT, 0, 0);
        srs.addColumn("id", Types.INTEGER, 10, 0);

Please see this example:
https://github.com/ebocher/H2GIS/tree/feature/creategrid/h2spatial-ext/src/main/java/org/h2gis/h2spatialext/function/spatial/create

@agouge

The following functions from the GDMS simplify package must be added to H2GIS:

• ST_PrecisionReducer
• ST_Simplify
• ST_SimplifyPreserveTopology

If you decide to work on this, please specify in the comments which functions you plan to implement.

The following functions from the GDMS raster package must be added to H2GIS:

• ST_Count
• ST_CropRaster -> ST_Crop
• ST_D8Accumulation -> ST_D8FlowAccumulation
• ST_D8AllOutlets
• ST_D8ConstrainedAccumulation
• ST_D8Direction -> ST_D8FlowDirection
• ST_D8DistanceToTheOutlet
• ST_D8RiverDistance
• ST_D8Slope
• ST_D8StrahlerStreamOrder
• ST_D8Watershed
• ST_FillSinks
• ST_Interpolate
• ST_LSFactor
• ST_PixelValue
• ST_RasterAlgebra
• ST_RasterToPoints
• ST_RasterToPolygons
• ST_RasterToXYZ
• ST_RasterizeLine
• ST_Resample
• ST_Shadow
• ST_StreamPowerIndex
• ST_VectorizeLine
• ST_WetnessIndex

If you decide to work on this, please specify in the comments which functions you plan to implement.

• ST_Accum

When you start working on the documentation of one of these functions,
please say so in the comments.

ST_PointN is 1-N based in OGC. It will be the same with H2 Spatial. GDMS Sql uses 0-N-1 based ST_PointN.

The following functions from the GDMS convert package must be added to H2GIS:

• ST_Force_2D
• ST_Force_3D
• ST_Holes
• ST_PointsToLine
• ST_ToMultiLine
• ST_ToMultiPoint
• ST_ToMultiSegments

Note: ST_InteriorPoint correponds to the SFS function ST_PointOnSurface which was already implemented in h2spatial.

If you decide to work on this, please specify in the comments which functions you plan to implement.

I think it's more aesthetic if we put the text description with justified alignment rather than left alignment in the H2Gis site (on description or note in examples).
Can you change the CSS to this alignment?
What do you think?

• ST_Polygonize
• ST_PrecisionReducer
• ST_Simplify
• ST_SimplifyPreserveTopology
• ST_Snap
• ST_Split

When you start working on the documentation of one of these functions,
please say so in the comments.

I work to create the documentation of ST_CoorDim.

• ST_SetSRID
• ST_Transform

When you start working on the documentation of one of these functions,
please say so in the comments.

In the previous GDMS function, it was possible to set as parameter a SQL command like this

SELECT * FROM ST_EXPLODE(select * from toto limit 10);

It seems that this approach is not supported by the new ST_Explode that received only a table name. Is it possible to fix ST_Explode to use the same approach than GDMS function.

Postgresql is able to do it :
http://www.postgresql.org/docs/7.3/static/xfunc-tablefunctions.html

• ST_ConstrainedDelaunay
• ST_Delaunay

When you start working on the documentation of one of these functions,
please say so in the comments.

The signature

public static ResultSet explode(Connection connection, String tableName, String fieldName) throws SQLException {

is not covered by a unit test.

This will affect #120.

• ST_BoundingCircle
• ST_Expand
• ST_Extrude
• ST_MakeEnvelope
• ST_MinimumRectangle
• ST_OctogonalEnvelope

When you start working on the documentation of one of these functions,
please say so in the comments.

As Postgis proposes and H2 for the CSV file format two functions tables must be transposed from gdms to H2 to read and write shapefile.
The syntax could be

SELECT * FROM SHPRead('/tmp/myshp');

SELECT * FROM SHPRead('/tmp/myshp', 'charset=UTF-8');

CALL SHPWRITE('/tmp/myshp', 'SELECT * FROM TEST', 'charset=UTF-8'');

The following functions from the indicated GDMS packages must be added to H2GIS:

export:

• ST_PLYExporter

other:

• ST_MainDirections
• ST_MeanSpacing

polygonize:

• ST_Polygonize

qa:

• ST_InternalGapFinder

trigo:

• ST_Azimut

If you decide to work on this, please specify in the comments which functions you plan to implement.

The following functions from the GDMS distance package must be added to H2GIS:

• ST_FurthestPoint
• ST_LocateAlong
• ST_NearestPoints
• ST_ProjectTo

If you decide to work on this, please specify in the comments which functions you plan to implement.

• ST_Translate

When you start working on the documentation of one of these functions,
please say so in the comments

This makes no sense. It should return the same thing as ST_Length if the geometry is 2D.

I should fix this in #29.

• ST_CompactnessRatio
• ST_Explode
• ST_Extent

These have been claimed by @mlecoeuvre.

• ST_ClosestCoordinate
• ST_ClosestPoint
• ST_FurthestCoordinate
• ST_LocateAlong

When you start working on the documentation of one of these functions,
please say so in the comments.

The following functions from the indicated GDMS packages must be added to H2GIS:

tin/analysis:

• ST_TriangleContouring

tin/create:

• ST_TIN

If you decide to work on this, please specify in the comments which functions you plan to implement.

Write a driver for ogc geopackage format

The following functions from the GDMS edit package must be added to H2GIS:

• ST_3DReverse
• ST_AddZ
• ST_AddZFromRaster
• ST_LinearInterpolation
• ST_Normalize
• ST_RemoveHoles
• ST_Reverse
• ST_SetZToExtremities
• ST_Snap
• ST_Split
• ST_SplitLine

If you decide to work on this, please specify in the comments which functions you plan to implement.

I'm working on ST_MakeLine and ST_MakePoint website documentation

@mlecoeuvre

@gpetit and @mlecoeuvre have been adding and will continue to add examples in
their documentation on h2gis.org.

In this ticket, we will keep track of the PR numbers which contain new unit
tests to be added.

At the time of writing, new unit tests are contained in PRs:

• #119 ST_Difference
• #120 ST_Explode
• #121 ST_SymDifference
• #124 ST_ConvexHull
• #125 ST_Intersection
• #138 ST_CompactnessRatio
• #140 ST_Contains
• #145 ST_Crosses
• #147 ST_Equals
• #148 ST_Touches
• #163 St_GeomFromText
• #165 ST_LineFromWKB
• #167 ST_MPointFromText, ST_MPolyFromText, ST_PointFromText, ST_PolyFromText
• #168 ST_PolyFromWKB
• #171 ST_Overlaps
• #173 ST_Intersects
• #174 ST_Within
• #176 ST_Disjoint
• #178 ST_EnvelopesIntersect
• #180 ST_Boundary
• #182 ST_Centroid
• #183 ST_Dimension
• #184 ST_Distance
• #185 ST_EndPoint
• #187 ST_Envelope
• #188 ST_ExteriorRing
• #192 ST_GeometryN, ST_PointN, ST_InteriorRingN
• #194 ST_GeometryType
• #195 ST_IsRing, ST_IsSimple, ST_IsEmpty, ST_Closed
• #196 ST_Lenght, ST_X, ST_Y, ST_Z
• #199 ST_NumGeometries, ST_NumInteriorRing, ST_NumInteriorRings, ST_NumPoints
• #207 ST_PointOnSurface
• ST_AsKml

Optional

• #130 ST_Union

If you add unit tests in the documentation, please update this list and say so
in the comments.

• ST_Area
• ST_Boundary
• ST_Centroid
• ST_Dimension
• ST_Distance
• ST_EndPoint
• ST_Envelope
• ST_ExteriorRing
• ST_GeometryN
• ST_GeometryType
• ST_InteriorRingN
• ST_IsClosed
• ST_IsEmpty
• ST_IsRing
• ST_IsSimple
• ST_Length
• ST_M
• ST_NumGeometries
• ST_NumInteriorRing
• ST_NumInteriorRings
• ST_NumPoints
• ST_PointN
• ST_PointOnSurface
• ST_SRID
• ST_StartPoint
• ST_X
• ST_Y
• ST_Z

When you start working on the documentation of one of these functions,
please say so in the comments.

Add support to use RTree.
RTree must be available in H2 grammar.

The following h2spatial/geometry-conversion functions need
to be documented:

• ST_AsBinary
• ST_AsText
• ST_AsWKT
• ST_GeomFromText
• ST_LineFromText
• ST_LineFromWKB
• ST_MLineFromText
• ST_MPointFromText
• ST_MPolyFromText
• ST_PointFromText
• ST_PolyFromText
• ST_PolyFromWKB

When you start working on the documentation of one of these functions,
please say so in the comments.

Extend the H2 data type to support geometry as PostGreSQL offers.

Have a look : http://www.postgresql.org/docs/6.3/static/c0806.htm

• ST_Holes
• ST_ToMultiLine
• ST_ToMultiPoint
• ST_ToMultiSegments

When you start working on the documentation of one of these functions,
please say so in the comments.

• ST_AddPoint
• ST_AddZ
• ST_Densify
• ST_Interpolate3DLine
• ST_MultiplyZ
• ST_Normalize
• ST_RemoveHoles
• ST_RemovePoint
• ST_RemoveRepeatedPoints
• ST_Reverse
• ST_Reverse3DLine
• ST_UpdateZ
• ST_ZUpdateExtremities

When you start working on the documentation of one of these functions,
please say so in the comments.

The following functions had been mistakenly listed as SFSQL functions:

• ST_Dimension
• ST_EndPoint
• ST_Envelope
• ST_EnvelopesIntersect
• ST_NumGeometries
• ST_NumInteriorRings

We may need to reorganize the code to fix this. See #178.

Currently ST_Buffer does not have a shape parameter like it did in GDMS.

We should add a string parameter with 3 possibilities: but, square or round (default).

We should add a unit test for ST_Buffer.

As I changed the example provided in the ST_IsValid page (http://www.h2gis.org/docs/dev/ST_IsValid/), we should adapt the unit test.

Instead of

SELECT ST_IsValid('POLYGON ((0 0, 10 0, 10 5, 10 -5, 0 0))'::Geometry);

we have to put

SELECT ST_IsValid('POLYGON ((0 0, 10 0, 10 5, 6 -2, 0 0))'::Geometry);

This issue is linked with #85

The default SRID value must be set to 0.

There are none. We'll be able to use @mlecoeuvre's examples once I merge my agouge/m-110 branch.

• ST_Buffer
• ST_ConvexHull
• ST_Difference
• ST_Intersection
• ST_SymDifference
• ST_Union

When you start working on the documentation of one of these functions,
please say so in the comments.

The following functions from the GDMS create package must be added to H2GIS:

• ST_AddVertex
• ST_BoundingCircle
• ST_CreateGrid
• ST_CreatePointsGrid
• ST_CreateWebGrid
• ST_Densify
• ST_Expand
• ST_Extrude
• ST_MakeEllipse
• ST_MakeEnvelope
• ST_MakeLine
• ST_MakePoint
• ST_MinimumDiameter
• ST_MinimumRectangle
• ST_OctogonalEnvelope
• ST_RandomGeometry
• ST_RemoveDuplicateCoordinate

If you decide to work on this, please specify in the comments which functions you plan to implement.

I'm working with "orbisgis-dist-4.1.0-SNAPSHOT/orbisgis.sh"

I import a shape file weighing 78 MB into an empty H2 database. Now I can see that the corresponding "database.h2.db" file weighs 144 MB. So there is a big difference.

Is there a way to optimize this point? ... knowing that in this context, some workspaces could weigh many GB.

@ebocher @nicolas-f @agouge

• ST_Contains
• ST_Crosses
• ST_Disjoint
• ST_EnvelopesIntersect
• ST_Equals
• ST_Intersects
• ST_Overlaps
• ST_Relate
• ST_Touches
• ST_Within

When you start working on the documentation of one of these functions,
please say so in the comments.

SQL in the OGC Conformance is not valid:

For Conformance 1&2, insert query is invalid for geometry_columns

INSERT INTO geometry_columns VALUES (
'bridges', 'position_gid',
'bridge_geom',1, 1, 2, 0);

There are fewer columns in comparison with the create table

-- Geometry Columns
CREATE TABLE geometry_columns ( f_table_schema CHARACTER VARYING,
f_table_name CHARACTER VARYING,
f_geometry_column CHARACTER VARYING,
g_table_schema CHARACTER VARYING,
g_table_name CHARACTER VARYING,
storage_type INTEGER,
geometry_type INTEGER,
coord_dimension INTEGER,
max_ppr INTEGER,
srid INTEGER REFERENCES spatial_ref_sys,
CONSTRAINT gc_pk PRIMARY KEY (f_table_schema, f_table_name,
f_geometry_column));

In C2:

A ) is missing in all sql queries on Hex conversion

-- Bridges
INSERT INTO bridge_geom VALUES(101, 44.0, 31.0, 44.0, 31.0,
HEXTOVARBINARY('010100000000000000000046400000000000003f40');

Insert statement refers to a non existing foreign key, this leads to NULL key value (in h2)

INSERT INTO geometry_columns VALUES (
'forests', 'boundary_gid',
'forest_geom',1, 11, 2, 0);

SRID is not given and it can be null then h2 sets it to null.

We currently can't compile this project using Java 7. This is because the following classes have new methods:

• Connection#getNetworkTimeout()
• CommonDataSource#getParentLogger()
• Statement#isCloseOnCompletion()
• [ResultSet#<T>getObject(String,Class<T>)](http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getObject%28java.lang.String, java.lang.Class%29)

The SHPRead is not able to parse this kind of path: /home/toto/Téléchargements/Data/ZONE_VEGETATION.SHP

ERROR [AWT-EventQueue-0] (Catalog.java:230) - Cannot load dropped data source
org.h2.jdbc.JdbcSQLException: File not found: "/home/toto/T\u00e9l\u00e9chargements/Data/ZONE_VEGETATION.SHP"; SQL statement:
CREATE TABLE `ZONE_VEGETATION` COMMENT STRINGDECODE('/home/ebocher/T\u00e9l\u00e9chargements/Data/ZONE_VEGETATION.SHP') ENGINE "org.h2gis.drivers.shp.SHPEngine" WITH "/home/ebocher/T\u00e9l\u00e9chargements/Data/ZONE_VEGETATION.SHP" [90124-174]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
    at org.h2.message.DbException.get(DbException.java:172)
    at org.h2.message.DbException.get(DbException.java:149)
    at org.h2gis.drivers.shp.SHPEngine.createTable(SHPEngine.java:54)
    at org.h2gis.drivers.shp.SHPEngine.createTable(SHPEngine.java:42)
    at org.h2.schema.Schema.createTable(Schema.java:584)
    at org.h2.command.ddl.CreateTable.update(CreateTable.java:135)
    at org.h2.command.CommandContainer.update(CommandContainer.java:79)
    at org.h2.command.Command.executeUpdate(Command.java:253)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:181)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:156)
    at org.h2gis.drivers.DriverManager.openFile(DriverManager.java:92)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.h2.engine.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:417)
    at org.h2.expression.JavaFunction.getValue(JavaFunction.java:39)
    at org.h2.command.dml.Call.query(Call.java:66)
    at org.h2.command.CommandContainer.query(CommandContainer.java:91)
    at org.h2.command.Command.executeQuery(Command.java:196)
    at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement