Use Point type with PostgreSQL and JPA/Hibernate

Question

Is there a way to map Point column to Java entity field?

I tried Hibernate Spatial 4.0, but seems that it doesn't work with pure PostgreSQL without PostGIS. Here is point field definition:

import com.vividsolutions.jts.geom.Point;
...
@Column(columnDefinition = "point")
@Type(type = "org.hibernate.spatial.GeometryType")
private Point location;

Dialect in persistence.xml:

<property name="hibernate.dialect"  value="org.hibernate.spatial.dialect.postgis.PostgisDialect" />

And when I try to persist this entity, exception is thrown:

org.postgresql.util.PSQLException: Unknown type geometry.
at org.postgresql.jdbc2.AbstractJdbc2Statement.setPGobject(AbstractJdbc2Statement.java:1603)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1795)
at org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject(AbstractJdbc3gStatement.java:37)
at org.postgresql.jdbc4.AbstractJdbc4Statement.setObject(AbstractJdbc4Statement.java:46)
at org.hibernate.spatial.dialect.AbstractJTSGeometryValueBinder.bind(AbstractJTSGeometryValueBinder.java:48)
at org.hibernate.spatial.dialect.AbstractJTSGeometryValueBinder.bind(AbstractJTSGeometryValueBinder.java:39)
at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:280)
at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:275)
at org.hibernate.type.AbstractSingleColumnStandardBasicType.nullSafeSet(AbstractSingleColumnStandardBasicType.java:57)

So seems that it tries to persist value as Geometry type of PostGIS. But I want to use just simple Point.

Answer 1

INSTALL THE POSTGIS EXTENSIONS IN YOUR DATABASE.

The error you are encountering is because you have not installed anything on your database that Hibernate can relate to. It's postgres that is complaining, viz:

org.postgresql.util.PSQLException: Unknown type geometry.

Your annotations and configuration say they have a dependency on PostGIS:

<property name="hibernate.dialect"  value="org.hibernate.spatial.dialect.postgis.PostgisDialect" />

So basically you are trying to use an unsupported configuration.

PostgreSQL does not come with all of the possible data types anyone ever thought of, the way Oracle does. So when Hibernate says "I want a 'geometry' PostgreSQL just says "what's a geometry?" PostgreSQL is very extensible so that's what the PostGIS people did - they built an extension. Anyone can install it or run completely without it. The only problem it causes is when someone expects it in the out-of-the-box database rather than it being an add-on.

I suppose you might try to reverse-engineer the PostgreSQL data types and compile them in your database. Geometry is a master superclass for PostGIS, in Java at least and it looks the same in the database. I just don't see why you would set up a configuration when you won't ever get anyone to support it.

@bluish does this help?

Answer 2

You should try to have the type in your db as Geometry, I also haven't managed to do it with mySQL. Geometry works as fine for points..

Answer 3

I can offer a more universal way through @Embedded Entity (It doesn't depend on the database type!), which I've used on projects.

Example below:

Piece of SQL query for creating some table with LAT and LONG:

CREATE TABLE some_table (
  lat    NUMBER(..., ...),
  long   NUMBER(..., ...),
...
);

Embeddable Point entity:

@Embeddable
public class Point {
    private BigDecimal x;
    private BigDecimal y;
 ...
}

Your custom entity:

@Entity
@Table(name = "some_table")
public class SomeEntity {
    @Embedded
    @AttributeOverrides({
            @AttributeOverride(name = "lat", column = @Column(name = "x")),
            @AttributeOverride(name = "long", column = @Column(name = "y"))
    })
    private Point point;
...
}