20

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.

Anton
  • 235
  • 1
  • 4
  • 9

5 Answers5

9

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?

Andrew Wolfe
  • 2,020
  • 19
  • 25
  • 1
    Although you gave a good answer, the OP asked for a solution without PostGIS. – bluish Dec 18 '14 at 08:14
  • If OP wants to use the postgis dialect, it will only work reliably with postgis installed in the database. Got to get rid of the dialect. – Andrew Wolfe Dec 18 '14 at 20:02
6

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..

foxTox
  • 128
  • 1
  • 8
  • 1
    What do you mean with this? I can't see Geometry data type in my PostgreSQL 9.3 – bluish Dec 11 '14 at 12:51
  • Maybe @foxTox means the PostGIS `Geometry` data type. Actually Hibernate Spatial doesn’t support the `Geography` type (see [How do I map a 'Geography(POINT, 4326)' type?](http://hibernate-spatial.1140993.n2.nabble.com/How-do-I-map-a-Geography-POINT-4326-type-td7572610.html)). – bluish Dec 30 '14 at 08:46
  • I'm able to save the points as as a geometry type, but it's unreadable when looking at the data in the database. Is there any way around this? – daniel9x Dec 05 '17 at 20:01
6

As I am not entirely sure of the question, let me try paraphrasing first:

You want to map an entity having a field of type com.vividsolutions.jts.geom.Point to a PostgreSQL database column of type "point" (cf. postgreSQL geometric types) using hibernate, restricting yourself to the "standard" (i.e. hibernate provided) dialect PostgreSQLDialect.

Some research leads me to the conclusion that the above mentioned hibernate supported standard dialect does not support the PostgreSQL point type at all. So you may want to provide your own UserType. Look at Custom types from the hibernate manual or Understanding and writing Hibernate custom user types for an example from a blog.

domids
  • 515
  • 5
  • 21
Hille
  • 4,096
  • 2
  • 22
  • 32
  • 1
    Thanks Hille. I'm not restricted to `com.vividsolutions.jts.geom.Point`. If there is another object that can map PostgreSQL `point` type, it would be ok. If I have to implement a `UserType`, I wonder if it would be better to use 2 `double` fields for lat and long instead. But I fear I'll loose some useful functions available for `point` type. – bluish Dec 16 '14 at 13:23
  • a) Meant to say (edited now), that PostgreSQL "point" type isn't supported at all by the hibernate provided dialects, no matter what Java type you're trying to map to. b) If there's no reason to use PostgreSQL "point" type in the first place, one may try to use a JPA 2.1 `AttributeConverter` (requires hibernate >= 4.3). – Hille Dec 16 '14 at 14:02
  • Curious as to the direction you recided to go bluish, even if I'm 3 years late to the party – daniel9x Dec 05 '17 at 20:02
  • I think this answer is closest to what the original question asked for. The missing bit is that one should use `org.postgresql.geometric.PGpoint` from the Postgres driver (driver can serialise it in prepared statements natively) instead of `com.vividsolutions.jts.geom.Point`. Then implement a UserType for Hibernate as this answer suggests or see [this answer](https://stackoverflow.com/a/21182705/1377864) for copy-paste example. – Yaroslav Admin Dec 23 '21 at 14:33
6

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;
...
}
Pianov
  • 1,533
  • 9
  • 16
  • then what is your solution when we need polygone and linestring? – Ali Abazari Dec 19 '16 at 14:44
  • @AliReza19330 for these kind of structures I prefer to find a way start using vividsolutions lib, maybe now it works fine with hibernate5, sorry I haven't had any deal with it. – Pianov Dec 27 '16 at 23:15
0

this link will help you LINK : https://www.compose.com/articles/geofile-everything-in-the-radius-with-postgis/

Beside that, distanceWithin() function take distance in degree which you have to cast to geograph but hibernate Spatial doesn't support so there is another way by which you can achieve this by providing distance in decimal according to your requirement.link is mention below for decimal points.

for decimal convertion LINK : https://en.wikipedia.org/wiki/Decimal_degrees

aizaz
  • 11
  • 4
  • Links will die over time. When that happens to the links you have shared, your answer will point to broken links. I suggest that you could write at least a summary to the links. – Lajos Arpad Nov 29 '20 at 21:23