Map a PostGIS geometry point field with Hibernate on Spring Boot

Question

In my PostgreSQL 9.3 + PostGIS 2.1.5 I have a table PLACE with a column coordinates of type Geometry(Point,26910).

I want to map it to Place entity in my Spring Boot 1.1.9 web application, which uses Hibernate 4.0.0 + . Place is available with a REST repository.

Unfortunately when I GET http://localhost:8080/mywebapp/places I receive this strange JSON response:

{

  "_embedded" : {

    "venues" : [ {

      "id" : 1,

      "coordinates" : {

        "envelope" : {

          "envelope" : {

            "envelope" : {

              "envelope" : {

                "envelope" : {

                  "envelope" : {

                    "envelope" : {

                      "envelope" : {

                        "envelope" : {

                          "envelope" : {

                            "envelope" : {

                              "envelope" : {

                                "envelope" : {

                                  "envelope" : {

                                    "envelope" : {

                                      "envelope" : {

                                        "envelope" : {

                                          "envelope" : {

                                            "envelope" : {

and so on indefinetely...! Spring log doesn't help..

I'm working with this application.properties:

spring.jpa.database-platform=org.hibernate.spatial.dialect.postgis.PostgisDialect
spring.jpa.show-sql=false
spring.jpa.hibernate.ddl-auto=update

spring.datasource.url=jdbc:postgresql://192.168.1.123/mywebapp
spring.datasource.username=postgres
spring.datasource.password=mypwd
spring.datasource.driverClassName=org.postgresql.Driver

First of all, is it ok to use database-platform instead of database? And maybe do I have to use following settings instead of the above?

spring.datasource.url=jdbc:postgresql_postGIS://192.168.1.123/mywebapp
spring.datasource.driverClassName=org.postgis.DriverWrapper

Anyway my entity is something like this:

@Entity
public class Place {
    @Id
    public int id;
    @Column(columnDefinition="Geometry")
    @Type(type="org.hibernate.spatial.GeometryType")    //"org.hibernatespatial.GeometryUserType" seems to be for older versions of Hibernate Spatial
    public com.vividsolutions.jts.geom.Point coordinates;
}

My pom.xml contains this relevant part:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.3-1102-jdbc41</version>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-spatial</artifactId>
    <version>4.3</version><!-- compatible with Hibernate 4.3.x -->
    <exclusions>
        <exclusion>
            <artifactId>postgresql</artifactId>
            <groupId>postgresql</groupId>
        </exclusion>
    </exclusions>
</dependency>

A bit strange configuration, I found it on the internet, it is the one that works best for now.

I hope that someone could help me with this mistery. :)

bluish · Accepted Answer · 2014-12-30T13:39:37.637

Finally I discovered that my configuration is ok and might be Jackson that cannot manage Point data type correctly. So I customized its JSON serialization and deserialization:

add these annotations to our coordinates field:

@JsonSerialize(using = PointToJsonSerializer.class)
@JsonDeserialize(using = JsonToPointDeserializer.class)

create such serializer:

import java.io.IOException;
import com.fasterxml.jackson.core.JsonGenerator;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonSerializer;
import com.fasterxml.jackson.databind.SerializerProvider;
import com.vividsolutions.jts.geom.Point;

public class PointToJsonSerializer extends JsonSerializer<Point> {

    @Override
    public void serialize(Point value, JsonGenerator jgen,
            SerializerProvider provider) throws IOException,
            JsonProcessingException {

        String jsonValue = "null";
        try
        {
            if(value != null) {             
                double lat = value.getY();
                double lon = value.getX();
                jsonValue = String.format("POINT (%s %s)", lat, lon);
            }
        }
        catch(Exception e) {}

        jgen.writeString(jsonValue);
    }

}

create such deserializer:

import java.io.IOException;
import com.fasterxml.jackson.core.JsonParser;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.DeserializationContext;
import com.fasterxml.jackson.databind.JsonDeserializer;
import com.vividsolutions.jts.geom.Coordinate;
import com.vividsolutions.jts.geom.GeometryFactory;
import com.vividsolutions.jts.geom.Point;
import com.vividsolutions.jts.geom.PrecisionModel;

public class JsonToPointDeserializer extends JsonDeserializer<Point> {

    private final static GeometryFactory geometryFactory = new GeometryFactory(new PrecisionModel(), 26910); 

    @Override
    public Point deserialize(JsonParser jp, DeserializationContext ctxt)
            throws IOException, JsonProcessingException {

        try {
            String text = jp.getText();
            if(text == null || text.length() <= 0)
                return null;

            String[] coordinates = text.replaceFirst("POINT ?\\(", "").replaceFirst("\\)", "").split(" ");
            double lat = Double.parseDouble(coordinates[0]);
            double lon = Double.parseDouble(coordinates[1]);

            Point point = geometryFactory.createPoint(new Coordinate(lat, lon));
            return point;
        }
        catch(Exception e){
            return null;
        }
    }

}

Maybe you can also use this serializer and this deserializer, available here.

I do notice that the output latitude and longitude from your Serializer are swapped. is that expected? This code explains it `double lat = value.getY();` and `double lon = value.getX();` thanks — randytan, May 25 '17 at 09:01
@randytan I used this code long time ago, but I remember that latitude and longitude where in the right order. Where do you see them swapped? — bluish, May 29 '17 at 08:16
If you send `POINT(A B)` and do `sys.out` to your variable `jsonValue = String.format("POINT (%s %s)", lat, lon);` you can see it's actually written to `POINT(B A)`. I just swap the code `value.getY()` and `value.getX()` to correct the position. Thanks — randytan, May 30 '17 at 02:36
@bluish I wonder How can I make this as a POLYGON serializer and deserializer. — horoyoi o, Feb 04 '20 at 09:50

score 7 · Answer 2 · edited May 26 '21 at 07:52

The solutions above helped me to fix the problem. I simplify it so other people can understand.

I included this library in my pom.xml:

<dependency>
  <groupId>com.bedatadriven</groupId>
  <artifactId>jackson-datatype-jts</artifactId>
  <version>2.2</version>
</dependency>

This is the POJO object I used. Then I was able to get the REST call to work without the envelope error and proper coordinates.

import com.bedatadriven.jackson.datatype.jts.serialization.GeometryDeserializer;
import com.bedatadriven.jackson.datatype.jts.serialization.GeometrySerializer;
import com.fasterxml.jackson.databind.annotation.JsonDeserialize;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.vividsolutions.jts.geom.Geometry;

@Entity
@Table(name = "boundary")
public class Boundary {

    private int id;
    private Geometry geometry;

    @Id
    public int getId() {
        return ogc_fid;
    }

    public void setId(int id) {
        this.id = id;
    }
    
    @JsonSerialize(using = GeometrySerializer.class)
    @JsonDeserialize(using = GeometryDeserializer.class)
    @Column(name = "geometry", columnDefinition = "Geometry")
    public Geometry getGeometry() {
        return geometry;
    }

    public void setGeometry(Geometry geometry) {
        this.geometry = geometry;
    }
}

My table had these 2 columns:

id       | integer            
geometry | geometry(Geometry,4326) |

@Martin Naughton If I use `@JsonDeserialize(using = JsonDeserialize.class)`, it returns this error. ```incompatible types found java.lang.class com.fasterxml.jackson.databind.annotation.jsondeserialize required: java.lang.Class extends com.fasterxml.jackson.databind.JsonDeserializer>>``` how can I use deserializer? — horoyoi o, Feb 04 '20 at 10:01

score 4 · Answer 3 · edited Apr 20 '21 at 06:45

With Hibernate 5.4 and Posgis 13+ its became very easy. We can just add latest hibernate-spatial dependency

    <!-- Hibernate Spatial for storing and retrieving geometries -->
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-spatial</artifactId>
        <version>${hibernate.version}</version>
    </dependency>

Use latest Posgis dialect. Add in application.properties:

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

Then we can simply use JST geometry because its supported by Hibernate (see here).

import org.locationtech.jts.geom.Point;

@Entity(name = "address")
public class Address {
 private Point location;
}

SQL to create entity table :

CREATE TABLE if not EXISTS address (location geometry);

Further JTS GeoJSON Reader and Writer can be used to translate geometries into JSON (see here)

Helped a lot. Thank you – Mohammed shafeek Jan 19 '22 at 14:37 — Mohammed shafeek, Jan 19 '22 at 14:37

score 2 · Answer 4 · answered Jun 02 '15 at 18:17

2

This serialization/deserialization also worked fine for me.

https://github.com/bedatadriven/jackson-datatype-jts

answered Jun 02 '15 at 18:17

thiagogcm

103
1
10

score 0 · Answer 5 · answered Dec 28 '14 at 00:02

0

The problem doesn't appear to be related to PostgreSQL. It appears that your POJO has a backreference, which means that your mapper doesn't know how to handle it. You need to explicitly define the recursive relationships so that the mapper knows when to stop. (My Goto link --> http://vard-lokkur.blogspot.com/2010/10/json-jackson-to-rescue.html)

answered Dec 28 '14 at 00:02

minnow73

26
2

Having no reference to any other entity, this is not the issue. Thank you anyway! – bluish Dec 30 '14 at 13:40

score 0 · Answer 6 · answered Feb 24 '19 at 10:05

If you don't want to add the annotation on all your fields that are using a Point, you can also use the @JsonComponent to register your JsonSerializer and JsonDeserializer.

@JsonComponent
public class PointSerializer extends JsonSerializer<com.vividsolutions.jts.geom.Point>{

    @Override
 public void serialize(com.vividsolutions.jts.geom.Point value, JsonGenerator gen, SerializerProvider provider) throws IOException {
     gen.writeStartObject();
     gen.writeNumberField("lat", value.getY());
     gen.writeNumberField("lon", value.getX());
     gen.writeEndObject();
 }
}

Map a PostGIS geometry point field with Hibernate on Spring Boot

6 Answers6

Linked