我如何在postgres中插入一个点/多重多边形(使用postgis)与Spring Data JPA？

55ooxyrt  于 2023-03-07  发布在  Spring
关注(0)|答案(1)|浏览(251)

我使用leaflet创建了一个Map，并尝试使用spring data JPA将点/多多边形绘图发送到我的postgres数据库。服务器最初接收到一个字符串geoJSON（通过以下反序列化方法转换为org. locationtech. jts. geom. MultiPolygon和org. locationtech. jts. geom. Point）。我注意到geojson字符串始终有效

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

他们显然工作得很好，例如：多重多边形（（（-42.538033 -19.461896、-42.542667 -19.489894、-42.487564 -19.478404、-42.538033 -19.461896））和点（-42.5270802 -19.4777807）
这是我的模型

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

但是，当我尝试将合作伙伴保存在数据库中时，我收到：

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]...
  • 我尝试了许多"JPA属性转换器"的实现，总是出现同样的错误。此外，我在某处（不确定）读到多多边形和点定位技术可以直接插入@Column（columnDefinition ="geometry（MultiPolygon，4326）"）和@Column（columnDefinition ="geometry（Point，4326）"）。
  • 另请阅读有关hib@Type的内容......但显然在hibspatial版本5.4.32中不起作用（已弃用？）
  • 只是为了确保，我试图设置coverageArea和地址空之前，试图保存在数据库中，它保存正确。

我的应用程序属性：

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

我的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>

这是我的第一个问题，如果我犯了错误或忘记了什么，对不起。谢谢
尝试使用JpaRepository "save"在postgres数据库中保存"Partner"（模型）。预期保存所有属性，但始终收到相同的错误（org. postgresql. util. PSQLException：错误：遇到无效的字节序标志值。）当我试图保存多重多边形或/和点时。

spring

1条答案

按热度按时间
sczxawaw

sczxawaw1#

1.您可以尝试此AttributeConverter接口实现。postgis存储格式中的几何图形是WKB。
1.在pojo字段中添加@Convert（转换器=几何属性转换器.类）

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 javax.persistence.AttributeConverter;
import java.util.concurrent.locks.ReentrantLock;

/**
 * @author Arjen10
 * @date 2023/3/6 14:42
 */
public class GeometryAttributeConverter 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();
        }
    }

}
赞(0）分享  回复(0）举报  2023-03-07
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com