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

Question

I created a map using leaflet and i'm trying to send point / multipolygon drawns to my postgres database using spring data JPA. The server initially receives a string geoJSON ( converted to org.locationtech.jts.geom.MultiPolygon and org.locationtech.jts.geom.Point by the follow deserialize methods ). Obs: I took care that the geojson string is always valid

import com.fasterxml.jackson.core.JsonParser;
import com.fasterxml.jackson.databind.DeserializationContext;
import com.fasterxml.jackson.databind.JsonDeserializer;
import org.locationtech.jts.geom.MultiPolygon;
import java.io.IOException;
import org.locationtech.jts.io.ParseException;
import org.locationtech.jts.io.geojson.GeoJsonReader;

public class MultiPolygonDeserializer extends JsonDeserializer<MultiPolygon> {

    @Override
    public MultiPolygon deserialize(JsonParser parser, DeserializationContext context) throws IOException {
        String geoJson = parser.getValueAsString();
        GeoJsonReader reader = new GeoJsonReader();
        try {
            return (MultiPolygon) reader.read(geoJson);
        } catch (ParseException e) {
            throw new IOException(e);
        }
    }
}

import com.fasterxml.jackson.core.JsonParser;
import com.fasterxml.jackson.databind.DeserializationContext;
import com.fasterxml.jackson.databind.JsonDeserializer;
import org.locationtech.jts.geom.Point;
import java.io.IOException;
import org.locationtech.jts.io.ParseException;
import org.locationtech.jts.io.geojson.GeoJsonReader;

public class PointDeserializer extends JsonDeserializer<Point> {

    @Override
    public Point deserialize(JsonParser parser, DeserializationContext context) throws IOException {
        String geoJson = parser.getValueAsString();
        GeoJsonReader reader = new GeoJsonReader();
        try {
            return (Point) reader.read(geoJson);
        } catch (ParseException e) {
            throw new IOException(e);
        }
    }
}

They apparently work fine, returning, for example: MULTIPOLYGON (((-42.538033 -19.461896, -42.542667 -19.489894, -42.487564 -19.478404, -42.538033 -19.461896))) and POINT (-42.5270802 -19.4777807)

And this is my model:

import com.fasterxml.jackson.databind.annotation.JsonDeserialize;
import com.pedro.geoPartners.util.MultiPolygonDeserializer;
import com.pedro.geoPartners.util.PointDeserializer;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import java.util.UUID;
import org.locationtech.jts.geom.MultiPolygon;
import org.locationtech.jts.geom.Point;


/**
 *
 * @author pedro
 */
@Entity
public class Partner {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;

    private String tradingName;
    private String ownerName;
    private String document;

    @Column(columnDefinition = "geometry(Point,4326)")
    @JsonDeserialize(using = PointDeserializer.class)
    private Point address;

    @Column(columnDefinition = "geometry(MultiPolygon,4326)")
    @JsonDeserialize(using = MultiPolygonDeserializer.class)
    private MultiPolygon coverageArea;

    public UUID getId() {
        return id;
    }

    public void setId(UUID id) {
        this.id = id;
    }

    public String getTradingName() {
        return tradingName;
    }

    public void setTradingName(String tradingName) {
        this.tradingName = tradingName;
    }

    public String getOwnerName() {
        return ownerName;
    }

    public void setOwnerName(String ownerName) {
        this.ownerName = ownerName;
    }

    public String getDocument() {
        return document;
    }

    public void setDocument(String document) {
        this.document = document;
    }

    public Point getAddress() {
        return address;
    }

    public void setAddress(Point address) {
        this.address = address;
    }

    public MultiPolygon getCoverageArea() {
        return coverageArea;
    }

    public void setCoverageArea(MultiPolygon coverageArea) {
        this.coverageArea = coverageArea;
    }

However, when i try to save a Partner in db, i receive:

2023-03-04T17:51:39.152-03:00  WARN 116266 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: XX000
2023-03-04T17:51:39.152-03:00 ERROR 116266 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: Invalid endian flag value encountered.
2023-03-04T17:51:39.152-03:00  INFO 116266 --- [nio-8080-exec-1] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements
2023-03-04T17:51:39.157-03:00 ERROR 116266 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.orm.jpa.JpaSystemException: could not execute statement] with root cause

org.postgresql.util.PSQLException: ERROR: Invalid endian flag value encountered.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) ~[postgresql-42.5.4.jar:42.5.4]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) ~[postgresql-42.5.4.jar:42.5.4]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) ~[postgresql-42.5.4.jar:42.5.4]
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496) ~[postgresql-42.5.4.jar:42.5.4]...

• I tried numerous implementantios of "JPA Attribute Converters" and always same error. Moreover, i read somewhere ( not sure ) that MultiPolygon and Point locationtech can be directly inserted in @Column(columnDefinition = "geometry(MultiPolygon,4326)") and @Column(columnDefinition = "geometry(Point,4326)").

*Also read about hibernate @Type... but apparently doens't works ( deprecated?) in hibernate spatial version 5.4.32

*Only for have sure, i tried to set coverageArea and address null before try to save in db, and it saves correctly.

My application properties:

spring.jpa.database=POSTGRESQL
spring.datasource.url=jdbc:postgresql://localhost:5432/GeoPartners?useTimeZone=true&serverTimezone=UTC&autoReconnect=true&useSSL=false
spring.datasource.username=postgres
spring.datasource.password=:)
spring.datasource.testWhileIdle=true
spring.datasource.validationQuery=SELECT 1
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.ImprovedNamingStrategy
spring.sql.init.mode=ALWAYS
spring.jpa.defer-datasource-initialization=true
hibernate.dialect=org.hibernate.spatial.dialect.postgis.PostgisDialect

My pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.0.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.pedro</groupId>
    <artifactId>geoPartners</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>war</packaging>
    <name>geoPartners</name>
    <description>update of &quot;JavaWebProject&quot;, implementing new features and Front-end</description>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-spatial</artifactId>
            <version>5.4.32.Final</version>
        </dependency>
        <dependency>
            <groupId>org.locationtech.jts.io</groupId>
            <artifactId>jts-io-common</artifactId>
            <version>1.19.0</version>
        </dependency>
        <dependency>
            <groupId>org.postgis</groupId>
            <artifactId>postgis-jdbc</artifactId>
            <version>1.3.3</version>
        </dependency>
    </dependencies>

    <repositories>
        <repository>
            <id>osgeo-alt</id>
            <url>https://repo.osgeo.org/repository/release/</url>
        </repository>
        <repository>
            <id>geomajas</id>
            <name>Geomajas Maven Repository</name>
            <url>http://maven.geomajas.org/(http://maven.geomajas.org/)</url>
        </repository>
    </repositories>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

Its my first question here, sorry if i made a mistake or forget something. Thank You

Tried to save a "Partner" ( model ) in a postgres db with JpaRepository "save". Expected to save all attributes but always receive same error (org.postgresql.util.PSQLException: ERROR: Invalid endian flag value encountered.) when i try to save a MultiPolygon or/and Point.

Answer 1

JPA

1. You can try this AttributeConverter interface implementation. Geometry in postgis storage format is WKB.

2. add @Type(type = "org.example.GeometryType") in pojo field

3. There are also some methods that are not implemented

import lombok.extern.slf4j.Slf4j;
import org.geotools.geometry.jts.WKBReader;
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.ParseException;
import org.locationtech.jts.io.WKBWriter;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Objects;
import java.util.Optional;

/**
 * @author Arjen10
 * @date 2023/5/29 下午7:48
 */
@Slf4j
public class GeometryType implements UserType {

    private static final int[] TYPES = new int[] {Types.BINARY};

    @Override
    public int[] sqlTypes() {
        return TYPES;
    }

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

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

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

    @Override
    public Object nullSafeGet(ResultSet rs, String[] strings, SharedSessionContractImplementor sc, Object o) throws HibernateException, SQLException {
        if (rs.wasNull()) {
            return null;
        }
        String wkbString = rs.getString(strings[0]);
        byte[] bytes = WKBReader.hexToBytes(wkbString);
        try {
            return new WKBReader().read(bytes);
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
    }

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

    @Override
    public Object deepCopy(Object o) throws HibernateException {
        return null;
    }

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

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

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

    @Override
    public Object replace(Object o, Object o1, Object o2) throws HibernateException {
        return null;
    }

}

myBatis

import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.geotools.geometry.jts.WKBReader;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.io.ParseException;
import org.locationtech.jts.io.WKBWriter;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Optional;
import java.util.concurrent.locks.ReentrantLock;

/**
 * @author Arjen10
 * @date 2022/8/30 11:30
 */
@Slf4j
public class GeometryTypeHandler extends BaseTypeHandler<Geometry> {

    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 void setNonNullParameter(PreparedStatement ps, int i, Geometry geometry, JdbcType jdbcType)
            throws SQLException {
        byte[] write;
        WRITE_LOCK.lock();
        try {
            //PostGIS 矢量数据存储方式为WKB
            write = WKB_WRITER.write(geometry);
        } catch (Exception e) {
            throw new SQLException(e);
        } finally {
            WRITE_LOCK.unlock();
        }
        try (InputStream is = new ByteArrayInputStream(Optional.of(write).get())) {
            ps.setBinaryStream(i, is);
        } catch (IOException | NullPointerException e) {
            throw new SQLException(e);
        }

    }

    @Override
    public Geometry getNullableResult(ResultSet rs, String s) throws SQLException {
        return getGeometry(rs.getString(s));
    }

    @Override
    public Geometry getNullableResult(ResultSet rs, int i) throws SQLException {
        return getGeometry(rs.getString(i));
    }

    @Override
    public Geometry getNullableResult(CallableStatement cs, int i) throws SQLException {
        return getGeometry(cs.getString(i));
    }

    private static Geometry getGeometry(String wkbString) throws SQLException {
        if (StringUtils.isEmpty(wkbString)) {
            return null;
        }
        byte[] bytes = WKBReader.hexToBytes(wkbString);
        READ_LOCK.lock();
        try {
            return WKB_READER.read(bytes);
        } catch (ParseException e) {
            throw new SQLException(e);
        } finally {
            READ_LOCK.unlock();
        }
    }

}

Answer 2

This solution is based on Arjen's solution with some changes for it to work with Spring boot 3.

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

}

now inside the entity use: @Type(GeometryType.class) for the geometry field.

I'm not sure if the implementation of all the functions is correct (meaning I / you might encounter an error along the line) but I managed to save an entity that has a locationtech.jts.jeom.Geometry field to postgres and managed to pull the entity back from the database.