Hibernate 5.1 + Vertica Spatial -- Putting it all togheter

Question

The error:

com.vertica.support.exceptions.DataException: [Vertica]VJDBC ERROR: Cannot INSERT or COPY user-defined types directly. Please compute them using appropriate user-defined functions

My current dialect

public class VerticaDialect extends org.hibernate.spatial.dialect.postgis.PostgisDialect {

private static final String SELECT_LAST_INSERT_ID = "SELECT LAST_INSERT_ID()";

@Override
public IdentityColumnSupport getIdentityColumnSupport() {

    return new IdentityColumnSupport() {
        ...
        @Override
        public String getIdentitySelectString(String arg0, String arg1, int arg2) throws MappingException {
            return SELECT_LAST_INSERT_ID;
        }
        ...
    };
}

}

My dependencies

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>5.1.1.Final</version>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-entitymanager</artifactId>
    <version>5.1.1.Final</version>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-spatial</artifactId>
     <version>5.1.1.Final</version>
</dependency>
<dependency>
    <groupId>javax.persistence</groupId>
    <artifactId>persistence-api</artifactId>
    <version>1.0</version>
</dependency>

My Vertica table

alter table something add GIS_WGS84 GEOGRAPHY NULL;

My model

import org.geolatte.geom.C2D;
import org.geolatte.geom.G2D;
import org.geolatte.geom.Polygon;

@Column(name="gis_wgs84")
public Polygon<G2D> getGisWGS84() {
    return gisWGS84;
}

public void setGisWGS84(Polygon<G2D> gisWGS84) {
    this.gisWGS84 = gisWGS84;
}

My test

Geographic2DCoordinateReferenceSystem wgs84 = CrsRegistry.getGeographicCoordinateReferenceSystemForEPSG(4326); // G2D

PositionSequence<G2D> wgs84positionSequence = 
                PositionSequenceBuilders.fixedSized(5, G2D.class)
                .add(new G2D(30.0, 60.0))
                .add(new G2D(30.0, 61.0))
                .add(new G2D(31.0, 61.0))
                .add(new G2D(31.0, 60.0))
                .add(new G2D(30.0, 60.0))
                .toPositionSequence();
Polygon<G2D> wgs84poly = new Polygon<>(wgs84positionSequence, wgs84);
something.setGisWGS84(wgs84poly);

My questions:

• Should I use org.geolatte.geom.Polygon or something else? There is also this com.vividsolutions.jts.geom package. I'm confused. Hibernate ORM documentation is of no use; it mentions both and the example doesn't show imports.
• Should I override Postgis dialect? Vertica loads data using function ST_GeographyFromText('LINESTRING(-42.0 23.0, -62.0 23.0)')

Answer 1

Overriding the dialect would have been quite a task, so we settled on loading the geometry data as WKT and then after all the rows are inserted to Vertica called a new update on the inserted rows that fills in Geometry/Geography columns.

So something like this:

session.createQuery(sql)
    .setParameterList("documentKeys", documentKeys)
    .executeUpdate();
}

 UPDATE tablename  
 SET    wgs84_geo = public.ST_GeographyFromText(wgs84_wkt) 
 WHERE  document_key IN (:documentKeys)

The bad thing is that in Vertica an update is a new delete + insert, but for query performance it is better to have these in the same table as rest of the data.

"Luckily" we then found out that Vertica does not support queries for multipolygons, which we have, so we had to move he Geometry/Geography columns away from the main table as linked tables that contain one row for each polygon of the multipolygon. These linked tables are joined to the main table for queries when geography searches are needed. Which turns out is not quite fast enough for us... But at least we no longer have to do an insert + update on the main table.