How to work with PGpoint for Geolocation using PostgreSQL?

Question

I found a lot of answers that suggest to use spatial data with Hibernate spatial data geolocation but I want to know if that is the best because I found that PostgreSQL works with PGpoint for GeoLocation. I implemented but it doesn't work because doesn't save.

ERROR: column "location" is of type point but expression is of type character varying

I have the same question but nobody answered him. So let me add other question below if nobody knows about this question.

As suggestion I'd want to know what is the best way to use Geo data on Spring Boot Context

Thanks! have a good day.

Answer 1

There is no way to save/update/get/ PGpoint object directly, Then you have to create your own user type for supporting PGpoint in order to convert it, before this is saved, UserType is a class of Hibernate which allows to create custom type in order to convert it before to save on database. Here is code that you need to implement:

First: Need to create a class that implements of UserType:

public class PGPointType implements UserType {
    @Override
    public int[] sqlTypes() {
        return new int[]
                {
                        Types.VARCHAR
                };
    }

    @SuppressWarnings("rawtypes")
    @Override
    public Class<PGpoint> returnedClass() {
        return PGpoint.class;
    }

    @Override
    public boolean equals(Object obj, Object obj1) {
        return ObjectUtils.equals(obj, obj1);
    }

    @Override
    public int hashCode(Object obj) {
        return obj.hashCode();
    }

    @Override
    public Object nullSafeGet(ResultSet resultSet, String[] names, SharedSessionContractImplementor sharedSessionContractImplementor, Object o) throws SQLException {
        if (names.length == 1) {
            if (resultSet.wasNull() || resultSet.getObject(names[0]) == null) {
                return null;
            } else {
                return new PGpoint(resultSet.getObject(names[0]).toString());
            }
        }
        return null;
    }


    @Override
    public void nullSafeSet(PreparedStatement statement, Object value, int index, SharedSessionContractImplementor sharedSessionContractImplementor) throws SQLException {
        if (value == null) {
            statement.setNull(index, Types.OTHER);
        } else {
            statement.setObject(index, value, Types.OTHER);
        }
    }

    @Override
    public Object deepCopy(Object obj) {
        return obj;
    }

    @Override
    public boolean isMutable() {
        return Boolean.FALSE;
    }

    @Override
    public Serializable disassemble(Object obj) {
        return (Serializable) obj;
    }

    @Override
    public Object assemble(Serializable serializable, Object obj) {
        return serializable;
    }

    @Override
    public Object replace(Object obj, Object obj1, Object obj2) {
        return obj;
    }

}

Second: Need to add on entity header @TypeDef annotation, add a name and the PGPointType that you created it and on some field header of type PGpoint, add @Type annotation with the name that you created it:

  @TypeDef(name = "type", typeClass = PGPointType.class)
  @Entity
  public class Entity {

       @Type(type = "type")
       private PGpoint pgPoint;

       // Getters and setters 

  }    

Kind regards.