Howto insert geometry in h2 using sql

Question

Since a couple of versions, h2 does have support for spatial geometries.

It's not a problem to select and insert geometries in java. But how can insert them in pure sql? Documentation shows it uses WKT. But when I try to insert in WKT I got an error.

That's an example insert:

insert into feature (id, name, description, geom) values
(1, 'example name', 'example description', 'SRID=4326;POINT(7 52)');

Thanks for any hints!

Answer 1

As far as I see, the suffix SRID=4326 is not WKT (Well-Known Text), but EWKT.

The H2 database currently does not support EWKT (Extended Well-Known Text). You would have to use 'POINT(7 52)'. A complete example:

create table feature(id int, name varchar(255), 
description varchar(255), geom geometry);
insert into feature (id, name, description, geom) values
(1, 'example name', 'example description', 'POINT(7 52)');

Answer 2

Spatial functions in H2 database is available in the H2GIS library. This library is a spatial extension of H2 database. It provides all OGC's simple feature for SQL standards. Using this library you can also change the coordinate reference system of your data.

If you don't want to use H2GIS you can define SRID by using alias method of H2:

create alias ST_GeomFromText AS $$
com.vividsolutions.jts.geom.Geometry fromText(String wkt, int srid) throws SQLException {
    if(wkt == null) {
        return null;
    }
    try {
        com.vividsolutions.jts.io.WKTReader wktReaderSRID = new com.vividsolutions.jts.io.WKTReader(new com.vividsolutions.jts.geom.GeometryFactory(new com.vividsolutions.jts.geom.PrecisionModel(),srid));
        com.vividsolutions.jts.geom.Geometry geometry = wktReaderSRID.read(wkt);
        return geometry;
    } catch (com.vividsolutions.jts.io.ParseException ex) {
        throw new SQLException(ex);
    }
}$$

Then call it:

insert into feature (id, name, description, geom) values
(1, 'example name', 'example description', ST_GeomFromText('POINT(7 52)', 4326));

You have to place the jts jar file in the h2 classpath in order to use Geometry type.