java - 将地理位置保存到Postgis数据库

Question

作为我项目的一部分，我试图将带有地理定位的java对象保存到Postgis数据库中，以便将Geolocation正确转换为Postgis空间数据格式，并且我可以使用Postgis地理功能性进行简单距离或半径对象搜索。

问题是-我找不到可以转换为Postgis DB的“geometry”列的Java数据对象类型。

目前，尝试将测试对象保存到数据库时出现错误。启动应用程序时，数据库表本身是正确创建的，并且它保存的对象没有地理数据，因此我认为DB-Connection不是问题。使用Postgis站点上的扩展指南将数据库升级到Postgis。
我试图解决的问题:

使用不同的类进行点创建-Java Point，vividsolutions几何

插入其他jdbc依赖项-mysql-connector-java，PostgreSQL JDBC驱动程序。还使用了休眠空间依赖性进行注释操作。

使用其他方言-PostgisDialect

的不同版本

使用不同的注释:

@Column(columnDefinition =“geometry(Point，4326)”)

导致相同的错误，

@Column(columnDefinition = "Geometry", nullable = true)
@Type(type = "org.hibernate.spatial.GeometryType")

导致ClassNotFoundException-GeometryType。

这些的当前版本如下。这似乎是最稳定的配置，没有保存点就没有错误。使用的seve功能是标准的CRUD保存功能。

类声明:

@Entity
@Table(name = "proposals")
public class Proposal implements Serializable {

@Id
@Exclude(CallType.REQUEST)
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id")
private int id;

@Column(name = "name")
@Required
private String name;

@Column(columnDefinition = "Geometry")
private Point gis_location;//using org.postgis.Point

//getter, setter, constructor
}

Application.properties:

server.port=8100
spring.datasource.url = jdbc:postgresql://localhost:5432/postgres_proposals
spring.datasource.username = postgres
spring.datasource.password = pass

# JPA# JPA/Hibernate properties
spring.jpa.show-sql = true

spring.jpa.hibernate.ddl-auto = update

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.naming.strategy = org.hibernate.cfg.ImprovedNamingStrategy

Gradle依赖项:

dependencies {
compile('org.springframework.boot:spring-boot-starter-data-jpa')
compile('org.springframework.boot:spring-boot-starter-data-rest')
compile('org.springframework.boot:spring-boot-starter-jersey')
compile group: 'org.slf4j', name: 'slf4j-simple', version: '1.8.0-beta2'
compile group: 'org.slf4j', name: 'slf4j-api', version: '1.8.0-beta2'
compile group: 'net.postgis', name: 'postgis-jdbc', version: '2.3.0'
runtime ('net.postgis:postgis-jdbc:2.3.0')
testCompile('org.springframework.boot:spring-boot-starter-test')
testCompile group: 'org.mockito', name: 'mockito-core', version: '2.21.0'
testCompile 'junit:junit:4.12'
}

测试对象的创建:

Proposal p = new Proposal();
p.setId(0);
p.setName("prop1");
Point point = new Point(50,8.5,12);
p.setGis_location(point);
proposalRepository.save(p);

目前，尝试将测试对象保存到数据库时出现错误-仅当我尝试保存wis gis_location Point时。当评论p.setGis_location(point);应用程序将正常启动，并且该对象出现在数据库中-没有地理数据。在未注释此行的情况下运行应用程序将导致以下错误:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'proposalApplication': Invocation of init method failed; nested exception is org.springframework.orm.jpa.JpaSystemException: could not execute statement; nested exception is org.hibernate.exception.GenericJDBCException: could not execute statement
Caused by: org.springframework.orm.jpa.JpaSystemException: could not execute statement; nested exception is org.hibernate.exception.GenericJDBCException: could not execute statement
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:333)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:521)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:761)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:730)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:518)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:292)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy91.save(Unknown Source)
at service.proposal.ProposalApplication.init(ProposalApplication.java:49)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleElement.invoke(InitDestroyAnnotationBeanPostProcessor.java:366)
at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:311)
at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:134)
... 18 more
Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:207)
at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2949)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3449)
at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:89)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:582)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:456)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1282)
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:465)
at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2963)
at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2339)
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:485)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:147)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$100(JdbcResourceLocalTransactionCoordinatorImpl.java:38)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:231)
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:65)
at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:61)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:517)
... 42 more
Caused by: org.postgresql.util.PSQLException: ERROR: Invalid endian flag value encountered.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:135)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
at com.sun.proxy.$Proxy96.executeUpdate(Unknown Source)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
... 61 more

线

service.proposal.ProposalApplication.init(ProposalApplication.java:49)

错误中提到的确实是save()函数。

我很感谢我如何解决此错误的任何建议。

Answer 1

您可以尝试以WKT(众所周知的文本)的形式将PostGIS原始坐标抛出。

例如，如果您可以从Java应用程序中获得以坐标对作为属性的点对象，例如[48.858401, 2.294474]，则可以尝试将其包装为PostGIS可以理解的形式:

insert into mytable (geometrycolumn) values (ST_GeomFromText('POINT(48.858401 2.294474)')