spring-data-jpapostgisjts

Spring JPA - how to insert an EWKT string into a PostGis database?

We may be moving to Postgis. Postgis supports EWKT. An example is a curved line:

CIRCULARSTRING(4 0, 4 4, 8 4)

The supported datatypes are in this overview.

How can I insert this arc into a Postgis database? I work with Spring JPA, JTS (locationtech) and Postgis.

Solution

  • Just create a simple method:

    public void insertBySql(final String insertSql) throws SQLException {
    try (PreparedStatement stmt = dataSource.getConnection().prepareStatement(insertSql)) {
        stmt.execute();
    }
}

    Call the method with a SQL statement with an arc. Try-1: use curveToLine:

    myRepo.insertBySql( """
  INSERT INTO objectwithgeometries (id,geometry,remarks) VALUES ( 3, ST_SetSRID( ST_CurveToLine('CIRCULARSTRING(29.8925 40.36667,29.628611 40.015000,29.27528 40.31667)'), 4326), 'remark-1');
                """);

    Or: try-2: without curveToLine:

    myRepo.insertBySql( """
  INSERT INTO objectwithgeometries (id,geometry,remarks) VALUES ( 3, ST_SetSRID( 'CIRCULARSTRING(29.8925 40.36667,29.628611 40.015000,29.27528 40.31667)', 4326), 'remark-2');
                """);

    With a similar call:

    enter image description here

    Or with ST_GeomFromEWKT (try-3 and try-4):

    objectWithGeometryService.insertBySql("""
  INSERT INTO objectwithgeometries (id,geometry,remarks) VALUES ( 4, ST_SetSRID( ST_GeomFromEWKT( ST_CurveToLine( 'CIRCULARSTRING(29.8925 41.36667,29.628611 41.015000,29.27528 41.31667)')), 4326), 'remark-3');
                """);

    The arc is visible e.g. via QGIS:

    enter image description here

    The result is a LineString which consists of many small lines.

    Another route is to create a dynamically created sql-statement with WkbWriter for the geometry column.