How to read CIRCULARSTRING from PostGis database?

[johan974]

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');

Try 1: When reading with Spring JPA repo.findAll() (with a connection to Postgis including Postgis dialect) I get this error:

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

Try 2: Then I tried with:

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);
        }
    });

I got this error:

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

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?

[phillipross]

I don't think CIRCULARSTRING is supported by the basic geometry parter, but you may have better luck with the JTS parser. Did you try that?

[johan974]

It could be done via

ResultSet r = s.executeQuery("select id,ST_AsText(geometry),remarks from objectwithgeometries where id = 43");

And then use osgeo geotools WktReader2().

So,

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();
        }
    }
}

[phillipross]

Thanks for the update on this. Based on the names of classes in the code you've provided, it appears geotools may be relying on JTS to read the geometry. This is actually what the postgis-jdbc-jts module does. I apologize if I wasn't clear in my previous message, but when I mentioned the JTS parser, I was referring to postgis-jdbc-jts module. This module works similarly to the default (older) module, but offloads the geometry parsing to the JTS library.

I'm thinking at some point the older module needs to be retired and the jts module replace it as the default.

Good to see you were able to get it working in some way or the other 😅