PostGIS Geometry saving: “Invalid endian flag valu

Question

I have a Spring Roo + Hibernate project which takes a JTS well-known text (WKT) String input from the client application, converts it into a JTS Geometry object, and then attempts to write it to the PostGIS database. I had some problems with the JDBC connection and types, but these seem to have been resolved with:

@Column(columnDefinition = "Geometry", nullable = true) 
private Geometry centerPoint;

And the conversion does:

Geometry geom = new WKTReader(new GeometryFactory(new PrecisionModel(), 4326)).read(source);

However now when Hibernate tries to write my Geometry object to the database, I get an error:

2012-08-31 21:44:14,096 [tomcat-http--18] ERROR org.hibernate.util.JDBCExceptionReporter - Batch entry 0 insert into land_use (center_point, version, id) values ('<stream of 1152 bytes>', '0', '1') was aborted.  Call getNextException to see the cause.
2012-08-31 21:44:14,096 [tomcat-http--18] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: Invalid endian flag value encountered.

It seems clear that the error is related to the binary representation, which is presumably generated as a well-known binary (WKB) with some endianness. However with Hibernate hiding all the persistence away, I can't really tell which way things are going.

I've been fighting this Geometry stuff for days, and there's very little information out there on these error, so does anyone have any bright ideas? Can I specify the endianness somewhere (Hibernate or PostGIS), or perhaps store in a different format (WKT)?

EDIT: I should also mention that I'm using the newest of everything, which generally seems to be compatible:

• Spring 3.1.1, Roo 1.2.1
• hibernate 3.6.9
• hibernate-spatial 4.0-M1
• jts 1.12
• PostgreSQL 9.1
• postgis-jdbc 1.5.3 (not the latest, but recommended for hibernate-spatial, compiled from source)
• postgis-jdbc 2.0.1 (just tried this now to match the version installed with PostgreSQL, same problem)

The Hibernate Spatial 4 tutorial suggests I do the property annotation as:

@Type(type="org.hibernate.spatial.GeometryType")
private Geometry centerPoint;

... but when I do this I get this other error, which the current annotation resolves.

Answer 1

I solve this problem adding to 'application.properties' this line:

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

Answer 2

See also, http://trac.osgeo.org/postgis/ticket/1830 An issue arose around the time of Postgresql 9xx and Postgis 2xx coming out which caused the same "an invalid endian flag" error when using the postgres utility pgsql2shp. It can be fixed be removing old versions of the library libpq.so, as it was due to Postgres changing default behavior of bytea.

Answer 3

The solution seems to be the following:
@Column to map the field to the desired column with JPA annotations
@Type to specify the Hibernate mapping with the dialect.

@Column(columnDefinition = "Geometry", nullable = true) 
@Type(type = "org.hibernate.spatial.GeometryType")
public Point centerPoint;

You could add the Hibernate property inside the hibernate.cfg.xml file to see the db request and try to catch the string-encoded problem with a text based editor like Notepad++ with "UTF-8"/"ANSI"/"other charsets"

<!--hibernate.cfg.xml -->
<property name="show_sql">true</property>
<property name="format_sql">true</property>
<property name="use_sql_comments">true</property>

To add the hibernate properties you will have an hibernate.cfg.xml file with the following stuff. Don't copy/paste it because it is MySQL oriented. Just look where I have inserted the properties I evocated previously.

 <?xml version="1.0" encoding="utf-8"?>
 <!DOCTYPE hibernate-configuration PUBLIC
 "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
 "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
 <hibernate-configuration>
      <session-factory>
           <property name="hibernate.bytecode.use_reflection_optimizer">true</property>
           <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
           <property name="hibernate.connection.password">db-password</property>
           <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/db-name</property>
           <property name="hibernate.connection.username">db-username</property>
           <property name="hibernate.default_entity_mode">pojo</property>
           <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
           <property name="hibernate.format_sql">true</property>
           <property name="hibernate.search.autoregister_listeners">false</property>
           **<property name="hibernate.show_sql">true</property>**
           <property name="hibernate.use_sql_comments">false</property>

           <mapping ressource="...." />
           <!-- other hbm.xml mappings below... -->

      </session-factory>
 </hibernate-configuration>

Another way to log all sql is to add package specific properties inside a log4j.properties file:

log4j.logger.org.hibernate.SQL=DEBUG
log4j.logger.org.hibernate.type=TRACE

Good luck!