Hibernate Spatial PostGis PSQLException column is of type point but expression is of type bytea

Question

In a Spring Boot project, Java8, with hibernate-spatial and PostgresDB 9.4

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

application.properties

spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.database-platform=org.hibernate.spatial.dialect.postgis.PostgisPG94Dialect
spring.jpa.properties.hibernate.dialect=org.hibernate.spatial.dialect.postgis.PostgisPG94Dialect

(I tried also PostgisPG9Dialect)

My Entity has a property

...
import com.vividsolutions.jts.geom.Point;
....
@Column(columnDefinition = "Point")
private Point cityLocation;

If I save with null value it's ok, but if I put a value

setCityLocation(new GeometryFactory().createPoint(new Coordinate(lng, lat));

I have:

PSQLException: ERROR: column "city_location" is of type point but expression is of type bytea  You will need to rewrite or cast the expression.

In my db I can see the column definition as

type: point
column size: 2147483647
data type: 1111
num prec radix:     10
char octet length: 2147483647

I'M GOING CRAZY... Why It doesn't work?

UPDATE (It still don't work, I'm collecting new informations)

1) I'm thinking the problem could be the creation of the db. In my application.properties I also have :

spring.jpa.properties.hibernate.hbm2ddl.auto=update

so the schema will update 'automatically' by hibernate.

2) I can run with success a query directly on the db (I use "Squirrel SQL" as client)

update my_table set city_location = POINT(-13,23) where id = 1

and if I

select city_location from my_table where id = 1

the answer is

<Other>

I can't see the value... I got the same answer for the record with null value inside the point type...

3) After set a value to the 'point' column with a query, I'm no more able to read from the table, I receive the exception:

org.geolatte.geom.codec.WktDecodeException : Wrong symbol at position: 1 in Wkt: (-13.0,23.0)

4) I look inside the hibernate-spatial-5.2.10.Final.jar and I found two "geolatte" named classes in the package org.hibernate.spatial :

GeolatteGeometryJavaTypeDescriptor.class GeolatteGeometryType.class

5) And also (specific for Squirrel SQL client experts): if I try to change a value of a column in "my_table" (not the 'point' city_location but anyone of the other columns) I recive an error similar to the one I recive in java when I try to insert a point value:

Exception seen during check on DB. Exception was:
ERROR: operator does not exist: point = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Squirrel is made with java.. so I can accept this strange thing, may be it compose the query in a 'wrong' way, maybe it is connected to the value I see when I make a select...

Any ideas?

Answer 1

I found the solution!!

A fix to the code was needed and a magic trick I read in another stackoverflow question saved my life.

The problem was that the db column was created in a wrong way:

in the db the column type should be geometry NOT point

I removed the columnDefinition = "Point" from the @Column annotation and I ran the query

CREATE EXTENSION postgis;

on my db following these instructions: Postgis installation: type "geometry" does not exist

Krishna Sapkota you are my new super hero!

Answer 2

Just remove columnDefinition = "POINT", from @Column annotation, and just use the Point object. (i.e. Use default column definition)