Spring JPA - how to read CIRCULARSTRING from PostGis database?

Question

In a PostGis database is a CircularString (EWKT) Geometry. It was inserted via this SQL:

INSERT INTO objectwithgeometries (id,geometry,remarks) VALUES ( 41, ST_SetSRID( ST_GeomFromEWKT( 'CIRCULARSTRING(29.8925 41.36667,29.628611 41.015000,29.27528 41.31667)'), 4326), 'remark-3');

The result is:

Try 1: When trying to read the object with      postgisRepo.findAll();

... the error is:

org.geolatte.geom.codec.WkbDecodeException: Unsupported WKB type code: 8

When looking in the WktDialect class, I see only the standard 7 WKB types. Not the CurcularString.

Try 2: reading with JDBC:

List<Map<String, Object>> objects = jdbcTemplate.queryForList( String.format( "select id,geometry,remarks from objectwithgeometries where id = %d", id));
objects.forEach( r -> {
        WKBReader wkbReader = new WKBReader();
        PGobject geometryObject = (PGobject) r.get( "geometry");
        byte[] geom = WKBReader.hexToBytes( geometryObject.getValue() );
        try {
            log.info( "Geometry: {}", wkbReader.read(geom));
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
    });

This will also throw the exception: org.locationtech.jts.io.ParseException: Unknown WKB type 8

There is no difference for Spring Boot 2 or 3 (with Hibernate 5 or 6).

Try-3: using PGobject, PGGeometry, etc. These objects are available via the maven dependency:

    <dependency>
        <groupId>net.postgis</groupId>
        <artifactId>postgis-jdbc</artifactId>
        <version>2023.1.0</version>
    </dependency>

The problem with this is that it gives an error (Unknown Geometry Type: 8) during the jdbcTemplate.queryForList() ;-(.

Try-4: standalone example via a different approach. The effect is the same: Unknown Geometry Type: 8.

Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost:5433/postgis";
conn = DriverManager.getConnection(url, "xyz", "abc");
((org.postgresql.PGConnection)conn).addDataType("geometry", 
     (Class<? extends PGobject>) Class.forName("net.postgis.jdbc.PGgeometry"));
Statement s = conn.createStatement();
ResultSet r = s.executeQuery("select id,geometry,remarks from objectwithgeometries where id = 43");
while( r.next() ) {
    PGgeometry geom = (PGgeometry)r.getObject(2);
    int id = r.getInt(1);
    System.out.println("Row " + id + ":");
    System.out.println(geom.toString());
}
s.close();
conn.close();

How to read this (EWKT) object in Spring JPA?

I use the org.hibernate.spatial.dialect.postgis.PostgisDialect for accessing the geometry stuff.

Further, notice that performing the above 'insert' statement via Spring JPA will often lead to a connection time-out.

Answer 1

Finally I found a solution. Feel free to have a more elegant solution.

You can find some info via this GeoTools informative page.

Below you find a standalone and a Spring JPA version. Item 3 is a linearized version as a (standard) JTS geometry.

1. Standalone version:

import org.geotools.geometry.jts.CircularString;
import org.geotools.geometry.jts.CurvedGeometryFactory;
import org.geotools.geometry.jts.JTSFactoryFinder;
import org.geotools.geometry.jts.WKTReader2;
import org.locationtech.jts.geom.GeometryFactory;
import org.postgresql.util.PGobject;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class PostgisReaderStandaloneAsText {
    public static void main(String[] args) {
        java.sql.Connection conn;

        try {
            Class.forName("org.postgresql.Driver");
            String url = "jdbc:postgresql://localhost:5433/postgis";
            conn = DriverManager.getConnection(url, "postgis", "postgis");
            Statement s = conn.createStatement();
            ResultSet r = s.executeQuery("select id,ST_AsText(geometry),remarks from objectwithgeometries where id = 43");
            while( r.next() ) {
                String geom = (String) r.getObject(2); 
                int id = r.getInt(1);
                System.out.println("Row " + id + " = " + geom.toString());

                GeometryFactory geometryFactory = JTSFactoryFinder.getGeometryFactory();
                CurvedGeometryFactory curvedfactory = new CurvedGeometryFactory(Double.MAX_VALUE);

                WKTReader2 reader = new WKTReader2(curvedfactory);
                CircularString arc = (CircularString) reader.read(geom);
                System.out.println( "Arc = " + arc);
            }
            s.close();
            conn.close();
        }
        catch( Exception e ) {
            e.printStackTrace();
        }
    }
}

The result is:

Row 43 = CIRCULARSTRING(29.8925 41.36667,29.628611 41.015,29.27528 41.31667)
Arc = CIRCULARSTRING (29.8925 41.36667, 29.628611 41.015, 29.27528 41.31667)

Because JTS cannot work with curves, you can make a LineString with densified (intermediate0 points.

2. Spring JPA version:

public List<Map<String, Object>> readDataGeometryAsText( int id) {
    return jdbcTemplate.queryForList( String.format( "select id,ST_AsText(geometry),remarks from objectwithgeometries where id = %d", id));
}

The calling/processing method:

List<Map<String, Object>> objects = queryListByJdbcTemplate.readDataGeometryAsText( id);
objects.forEach( r -> {
    try {
        String geometryString = (String) r.get("st_astext");
        CircularString arc = (CircularString) reader2.read( geometryString);
        log.info( "Geometry: {}", arc);
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
});

The next issue is to convert any Surface with LineStrings and Curves into the database and out again.

3. Make a standard JTS object

Now it is simple to create a standard JTS object:

LineString linearizedCurve = arc.linearize();

The output is:

Linearized: LINESTRING (29.8925 41.36667, 29.89304671109555 41.36271422419931, 29.89570081654394 41.32222040018712, 29.89304671109555 41.28172657617493, 29.88512980723046 41.2419256127186, 29.87208556536878 ... 41.2419256127186, 29.277884450534472 41.28172657617493, 29.27528 41.31667)