How to insert / select a Point from postgres Geometry type in Spring Boot 3

Question

I have a Spring Boot 3.0.5 application with a connection to postgres v15 database. I have an entity with a field of type org.locationtech.jts.jeom.Point (also tried vividsolutions but didn't work). And in the database I can see that the type of the column is 'Geometry'.

This is my code:

import org.locationtech.jts.jeom.Point;

public class MyEntity {
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "address", columnDefinition = "geometry(Point,4326)")
    @Convert(converter = PointConverter.class)
    private Point address;

    // rest of class definition / getters / setters

}

public class PointConverter implements AttributeConverter<Geometry, byte[]> {

    private static final WKBWriter WKB_WRITER = new WKBWriter();

    private static final WKBReader WKB_READER = new WKBReader();

    private static final ReentrantLock WRITE_LOCK = new ReentrantLock();

    private static final ReentrantLock READ_LOCK = new ReentrantLock();




    @Override
    public byte[] convertToDatabaseColumn(Geometry attribute) {
        WRITE_LOCK.lock();
        try {
            //PostGIS's storage format is WKB
            return WKB_WRITER.write(attribute);
        }finally {
            WRITE_LOCK.unlock();
        }
    }

    @Override
    public Geometry convertToEntityAttribute(byte[] dbData) {
        READ_LOCK.lock();
        try {
            return WKB_READER.read(dbData);
        } catch (ParseException e) {
            throw new RuntimeException(e);
        } finally {
            READ_LOCK.unlock();
        }
    }
}

gradle:

implementation 'org.locationtech.jts:jts-core:1.19.0'
implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
runtimeOnly 'org.postgresql:postgresql'

I found this solution here:

How do i insert a Point / MultiPolygon in postgres ( using postgis ) with Spring data JPA?

I've also tried the solution here and it didn't work:

Inconsistent Exception parsing WKB geometry using JTS

If I could (don't have 50 points) I would have commented on the posts to ask for help there.

Now, when I attempt to save an entity to the db by using the repository it works as expected.

BUT, when trying to pull an entity using the repository I'm getting the following error:

Caused by: org.locationtech.jts.io.ParseException: Unknown WKB type 592
    at org.locationtech.jts.io.WKBReader.readGeometry(WKBReader.java:282)
    at org.locationtech.jts.io.WKBReader.read(WKBReader.java:191)
    at org.locationtech.jts.io.WKBReader.read(WKBReader.java:159)
    at com.mypath.PointConverter.convertToEntityAttribute(PointConverter.java:87)

UPDATE:

I managed to pull a Point back into an entity with this converter:

@Converter(autoApply = true)
public class PointConverter implements AttributeConverter<Point, Object> {

    @Override
    public Object convertToDatabaseColumn(final Point point) {
        // Create a WKB writer
        final WKBWriter writer = new WKBWriter();

        // Convert the JTS Point to a WKB byte array
        final byte[] wkbBytes = writer.write(point);

        // Convert the WKB byte array to a hex string
        return WKBWriter.bytesToHex(wkbBytes);

    }


    @Override
    public Point convertToEntityAttribute(final Object dbData) {
        // Create a WKB reader
        final WKBReader reader = new WKBReader();

        // Read the WKB string into a JTS geometry object
        try {
            final Geometry geometry = reader.read(WKBReader.hexToBytes(dbData.toString()));
        // Get the centroid of the geometry
            final Point centroid = geometry.getCentroid();
            return centroid;
        } catch (final ParseException e) {
            throw new RuntimeException(e);
        }
    }
}

BUT!!! now I can't save a new entity.. getting the following error message:

Caused by: org.postgresql.util.PSQLException: Unsupported Types value: 596,497,711
    at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:737)
    at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:974)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setObject(HikariProxyPreparedStatement.java)
    at org.hibernate.type.descriptor.jdbc.ObjectJdbcType$1.doBind(ObjectJdbcType.java:58)
    at org.hibernate.type.descriptor.jdbc.BasicBinder.bind(BasicBinder.java:63)
    at org.hibernate.type.internal.ConvertedBasicTypeImpl.nullSafeSet(ConvertedBasicTypeImpl.java:271)
    at org.hibernate.type.internal.ConvertedBasicTypeImpl.nullSafeSet(ConvertedBasicTypeImpl.java:249)
    at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:3252)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3435)

Anyone ??

Answer 1

You can try:

1 - Create a class GeometryType:

import lombok.extern.slf4j.Slf4j;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.io.WKBReader;
import org.locationtech.jts.io.WKBWriter;

import java.io.*;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Objects;

@Slf4j
public class GeometryType implements UserType<Geometry> {

    @Override
    public int getSqlType() {
        return Types.BINARY;
    }

    @Override
    public Class<Geometry> returnedClass() {
        return Geometry.class;
    }

    @Override
    public boolean equals(Geometry o,Geometry o1) throws HibernateException {
        return Objects.equals(o, o1);
    }

    @Override
    public int hashCode(Geometry o) throws HibernateException {
        return Objects.hashCode(o);
    }

    @Override
    public Geometry nullSafeGet(ResultSet rs, int position, SharedSessionContractImplementor session, Object owner) throws SQLException   {
        if (rs.wasNull()) {
            return null;
        }
        try {
            String string = rs.getString(position);
            if (string == null) {
                return null;
            }
            final byte[] bytes = WKBReader.hexToBytes(string);
            return new WKBReader().read(bytes);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void nullSafeSet(PreparedStatement ps, Geometry geometry, int i, SharedSessionContractImplementor sc) throws HibernateException, SQLException {
        if (Objects.isNull(geometry)) {
            ps.setNull(i, Types.BINARY);
            return;
        }
        byte[] write = new WKBWriter().write(geometry);
        try (InputStream is = new ByteArrayInputStream(write)) {
            ps.setBinaryStream(i, is);
        } catch (IOException | NullPointerException e) {
            throw new SQLException(e);
        }
    }

    @Override
    public Geometry deepCopy(Geometry geometry) throws HibernateException {
        if (Objects.isNull(geometry)) {
            return null;
        }
        return geometry.copy();
    }

    @Override
    public boolean isMutable() {
        return false;
    }

    @Override
    public Serializable disassemble(Geometry o) throws HibernateException {
        return o;
    }

    @Override
    public Geometry assemble(Serializable serializable, Object o) throws HibernateException {
        return deepCopy((Geometry) serializable);
    }

    @Override
    public Geometry replace(Geometry original, Geometry o1, Object o2) throws HibernateException {
        return deepCopy(original);
    }

}

2 - Insert @Type(GeometryType.class) above your point attribute.

Ref: How do i insert a Point / MultiPolygon in postgres ( using postgis ) with Spring data JPA?