How to persist geolatte Geometry in POSTGIS as geometry type?

[Krzysztof Faliński]

I am trying to use QueryDSL with geolatte (with JTS it works good in my project)

I use Spring with hibernate-spatial and POSTGIS.

This works ok (I can store point into database)

@Setter
@Getter
@Entity
public class JtsPointEntity extends BaseEntity {
    private static final long serialVersionUID = 65483473675794478L;

    private String name;
    private String code;

    @Type(type = "org.hibernate.spatial.GeometryType")
    private com.vividsolutions.jts.geom.Point jtsPoint;

This doesn't work (I get java.io.NotSerializableException: org.geolatte.geom.JTSGeometryOperations doing em.persist()): 

@Setter
@Getter
@Entity
public class GeolattePointEntity extends BaseEntity {

    private String name;
    private String code;
 
    private org.geolatte.geom.PointgeolattePoint;


(I also tried with @Convert( converter = PostgisConverter.class) 
 -  with this I get java.lang.IllegalStateException: Unable to instantiate AttributeConverter

Thanks for any help
Krzysztof Faliński

[Maciej Szajna]

I would not recommend having fields of non standard types in your entities. The Point class turns out not to be serializable which not only means Java doesn't know how to convert it to a value storable in DB, but it might also have runtime dependencies to objects that CANNOT or SHOULD NOT be created out of thin air during deserialization.

What you want to do instead is store point as field (or fields) of simple types (supported by JPA: int, String, BigDecimal...) in entity, and map it to your Point type later on, on an abstraction level above entities.

It's only a guess but Point should map easily to pair of ints or BigDecimals (wouldn't recommend floats).

public class GeolattePointEntity extends BaseEntity {
    private String name;
    private String code;

    private int latitude;
    private int longtitude;

    public com.vividstudios.jts.geom.Point getPoint() {
        //map latitude and longtitude to a Point
    }

    public void setPoint(com.vividstudios.jts.geom.Point point) {
        //update latitude and longtitude to values from point
    }

}

I don't know what exactly has to be stored to map the Point class reliably forth and back - pair of latitude and longtitude of int sound like a good shot but as well might not be - that's for you to figure out.

Cheers,

Maciej

[timowest]

Hi Krzysztof.

The stacktrace you get comes from Hibernate Spatial so I suggest you direct the question to a Hibernate Forum, as they might be able to help you better with this question.

The support Querydsl provides for Hibernate Spatial is only in the query construction.

Br,

Timo

[Krzysztof Faliński]

Ok now I know that I was wrong... I was trying to use JPA to save geolatte. (It works only with JTS)

But please tell me now how to store geolatte throught SQLQuery

 <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

        <property name="driverClassName" value="org.postgresql.Driver"/>

        <property name="url" value="jdbc:postgresql://localhost:5432/baza"/>

        <property name="username" value="postgres"/>

        <property name="password" value="123"/>

    </bean>

This should save:

       GeolattePointEntity geolattePointEntity = new GeolattePointEntity();

        org.geolatte.geom.Point point = new org.geolatte.geom.Point(Points.create3D(2.0, 2.3, 4.3));

        geolattePointEntity.setGeolattePoint(point);

        geolattePointEntity.setName("name");

        SQLTemplates templates = new PostGISTemplates();

        Configuration configuration = new Configuration(templates);

        SQLQueryFactory factory = new SQLQueryFactory(configuration, dataSource);

        SQLInsertClause insert = factory.insert(QGeolattepointentity.geolattepointentity);

        insert.populate(geolattePointEntity).execute();

And then after 

        SQLQuery query = factory.query();

        List<GeolattePointEntity> list = query.from(qGeolattePointEntity).list(qGeolattePointEntity);

        GeolattePointEntity geo = list.get(0);

I get

Caused by: java.lang.ClassCastException: org.postgresql.util.PGobject cannot be cast to core.point.GeolattePointEntity

[timowest]

Hi.

You can't use Querydsl SQL to query JPA entities. 

Here are the instructions to get Querydsl SQL working with Spatial features: http://www.querydsl.com/static/querydsl/3.6.0/reference/html/ch02s04.html

Br,

Timo