Why I can't map a Point field of my entity class on a Point field on the database? column "location" is of type point but expression is of type bytea

Question

I am working on my first Spring Boot + Spring Data JPA + Hibernate 5 working on a PostgreSQL database.

I have the following problem trying to map a field having point as data type (so I am using Hibernate Spatial that is natively included into Hibernate 5.

So I have the following situation:

I have the following simple database table named cities:

Field              Type   
----------------------------------------------------------------    
id                 bigint                   (it is the PK)
name               character varying
location           point                    (it contains the coordinates)

So for example this table contains this record:

id         name             locoation
--------------------------------------
1          San Francisco    (-194,53)

Then I have this Cities entity class that map the previous cities database table:

import com.vividsolutions.jts.geom.Point;
import org.hibernate.annotations.Type;
import javax.persistence.*;
import java.io.Serializable;

@Entity
@Table(name = "cities")
public class Cities implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    private String name;

    @Column(name = "location", columnDefinition="Point")
    private Point location;

    public Cities() {
    }

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Point getLocation() {
        return location;
    }

    public void setLocation(Point location) {
        this.location = location;
    }
}

I am using this implementation of the Point class: com.vividsolutions.jts.geom.Point because I found it on some tutorial but I am not sure if it is correct because I also can chose these other implementations:

org.geolatte.geom.Point
org.springframework.data.geo.Point

Then I have this Spring Data JPA interface that represent my DAO class:

@Repository
public interface CitiesDAO extends JpaRepository<Cities, Long> {

    Cities findById(@Param("id") Long id);
}

As you can see it extends JpaRepository and the method signature "implements" my queries (using JPA).

So, into my JUnit test class I implemented this test method in which I am trying to insert a new record into the cities table on the database:

@Test
public void testCitiesDAO() {
    System.out.println("testCitiesDAO() START");

    //Cities city = citiesDAO.findById(1L);


    GeometryFactory geometryFactory = new GeometryFactory();

    Coordinate coordinate = new Coordinate();
    coordinate.x = 2;
    coordinate.y = 5;

    Point myPoint = geometryFactory.createPoint(coordinate);

    Cities rome = new Cities();

    rome.setId(new Long(2));
    rome.setName("Rome");
    rome.setLocation(myPoint);

    citiesDAO.save(rome);

    System.out.println("testCitiesDAO() END");

}

So I am creating the object to persiste (Cities rome) on wich I set a brand new Point object and I am trying to persist it by this line:

citiesDAO.save(rome);

The problem is that when try to perform the save(rome) method this excepetion is thrown:

Hibernate: select cities0_.id as id1_0_0_, cities0_.location as location2_0_0_, cities0_.name as name3_0_0_ from cities cities0_ where cities0_.id=?
Hibernate: insert into cities (location, name) values (?, ?)
2016-10-29 20:08:09.509 ERROR 7956 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: column "location" is of type point but expression is of type bytea
  Hint: You will need to rewrite or cast the expression.
  Position: 45

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement

    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:261)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:491)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
    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.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
    at com.sun.proxy.$Proxy105.save(Unknown Source)
    at com.betriuvis.controller.test.PlaceSearcherControllerTest.testCitiesDAO(PlaceSearcherControllerTest.java:111)
    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:497)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:262)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84)
    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:497)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    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.dialect.identity.GetGeneratedKeysDelegate.executeAndExtract(GetGeneratedKeysDelegate.java:57)
    at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:42)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2803)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3374)
    at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:619)
    at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:273)
    at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:254)
    at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:299)
    at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:317)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:272)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:178)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:109)
    at org.hibernate.jpa.event.internal.core.JpaMergeEventListener.saveWithGeneratedId(JpaMergeEventListener.java:56)
    at org.hibernate.event.internal.DefaultMergeEventListener.saveTransientEntity(DefaultMergeEventListener.java:255)
    at org.hibernate.event.internal.DefaultMergeEventListener.entityIsTransient(DefaultMergeEventListener.java:235)
    at org.hibernate.event.internal.DefaultMergeEventListener.entityIsDetached(DefaultMergeEventListener.java:301)
    at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:170)
    at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:69)
    at org.hibernate.internal.SessionImpl.fireMerge(SessionImpl.java:840)
    at org.hibernate.internal.SessionImpl.merge(SessionImpl.java:822)
    at org.hibernate.internal.SessionImpl.merge(SessionImpl.java:827)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.merge(AbstractEntityManagerImpl.java:1161)
    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:497)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:298)
    at com.sun.proxy.$Proxy101.merge(Unknown Source)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.save(SimpleJpaRepository.java:509)
    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:497)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:503)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:488)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:460)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    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)
    ... 41 more
Caused by: org.postgresql.util.PSQLException: ERROR: column "location" is of type point but expression is of type bytea
  Hint: You will need to rewrite or cast the expression.
  Position: 45
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:321)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
    ... 86 more

So basically the error message is:

Caused by: org.postgresql.util.PSQLException: ERROR: column "location" is of type point but expression is of type bytea

So it seems that is trying to put something wrong into the location field of the table having point as data type. Why it can't convert the com.vividsolutions.jts.geom.Point object used into the entity class?

This is my application.propertis file, the only configuration file in my application:

#No auth  protected
endpoints.shutdown.sensitive=true
#Enable shutdown endpoint
endpoints.shutdown.enabled=true
logging.file=BeTriviusController.log
logging.level.org.springframework.web=DEBUG
logging.level.org.hibernate=ERROR

# Thymeleaf
spring.thymeleaf.cache:false


# DATABASE CONFIG ----------------------------------------------------------------------------------------------------
spring.datasource.url = jdbc:postgresql://localhost:5432/test1
spring.datasource.username = postgres
spring.datasource.password = Bl4tte_Te4m
spring.datasource.driver-class-name = org.postgresql.Driver

spring.jpa.properties.hibernate.current_session_context_class=org.springframework.orm.hibernate5.SpringSessionContext

spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1

# Show or not log for each sql query
spring.jpa.show-sql = true

# Hibernate ddl auto (create, create-drop, update, validate)
spring.jpa.hibernate.ddl-auto = validate

#spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy

spring.jpa.properties.hibernate.dialect=org.hibernate.spatial.dialect.postgis.PostgisDialect

As you can see I have set the spring.jpa.hibernate.naming-strategy to the PostgisDialect dialect to use Hibernate spatial.

What is worng? What am I missing? How can I fix this issue?

score 2 · Answer 1 · answered Nov 02 '16 at 08:51

2

You have everything setup correctly for Postgresql + Postgis, but your database uses the Postgresql geometric types, not the ones provided by Postgis. Hibernate Spatial only supports the Postgis spatial extension.

Make sure Postgis is installed, and your tables uses the Postgis provided type 'Geometry' for the location column.

answered Nov 02 '16 at 08:51

Karel Maesen

751
5
7

Ok tnx, some additional questions: 1) so exist two different versions of the "Point" data type? One belonging to native PostgreSQL DB and another one belonging to the PostGIS extension? Why exist 2 different version of the same data type? and what are the difference between these two versions? 2) How do you understood that I am using the native PostgreSQL data type and not the PostGIS one? Tnx so much – AndreaNobili Nov 02 '16 at 09:52
Postgis is an implementation of the simple features specification, and has more functions/operators for working with geographic information. It is developed by a separate team. You can tell the difference between native and postgis in both the table definition (postgis uses Geometry/Geography types), and the table contents (in Postgis you would see a hex-encoded bytestring, or maybe a WKT point representation, not (x,y)) – Karel Maesen Nov 03 '16 at 11:43

score 1 · Answer 2 · answered Mar 08 '17 at 12:55

I'm using Mysql 5 with Point object and had the same issue. The was with the newest dialect version, so I downgraded it. Changing the aplication.properties from this:

spring.jpa.properties.hibernate.dialect=org.hibernate.spatial.dialect.mysql.MySQL56InnoDBSpatialDialect
spring.jpa.database-platform = org.hibernate.spatial.dialect.mysql.MySQLSpatial56Dialect

To this:

spring.jpa.properties.hibernate.dialect=org.hibernate.spatial.dialect.mysql.MySQL5InnoDBSpatialDialect
spring.jpa.database-platform = org.hibernate.spatial.dialect.mysql.MySQLSpatial56Dialect

I hope it can help you.

score 1 · Answer 3 · answered Mar 24 '17 at 23:21

1

I was using Hibernate-{core,spatial} v5.2.9 everything was as is above, except from how dialect was being set in application.properties. I NEEDED:

spring.jpa.database-platform=org.hibernate.spatial.dialect.postgis.PostgisDialect

Now it works. I don't need even any @Column annotation in my Entity.

answered Mar 24 '17 at 23:21

Tom Harvey

11
1

I also have this error... does anyone solve it ?.... Caused by: org.postgresql.util.PSQLException: ERROR: column "city_location" is of type point but expression is of type bytea Suggerimento: You will need to rewrite or cast the expression. – Paolo Biavati Aug 28 '17 at 22:47

score 0 · Answer 4 · edited May 23 '17 at 11:54

0

I would suggest using more generic approach, which is demonstrated in this answer. For me installing database extensions/ operating with geometrical types for such a simple type as Point is an overkill.

edited May 23 '17 at 11:54

Community

1
1

answered Nov 19 '16 at 12:48

Tinki

1,486
1
21
32

score 0 · Answer 5 · answered May 18 '20 at 06:44

As of Monday, 18th May 2020

Dependency is as below:

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-spatial</artifactId>
    <version>5.4.15.Final</version>
</dependency>

application.yml

spring:
  jpa:
    show-sql: true
    generate-ddl: false
    hibernate:
      ddl-auto: none
    properties:
      hibernate:
        format_sql: true
        dialect: org.hibernate.spatial.dialect.mysql.MySQL8SpatialDialect

Why I can't map a Point field of my entity class on a Point field on the database? column "location" is of type point but expression is of type bytea

5 Answers5

Linked