PostGIS Geometry saving: "Invalid endian flag value encountered."

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.

Micrometry

Hello again, I follow your problems with OpenGIS :) Did you try to do the twice methods @Type(type="org.hibernate.spatial.GeometryType") annotation plus the JPA annotations @Column(columnDefinition = "Geometry", nullable = true) associated to the same field. – Sadonia

Other librairies have got problems like that geodb issue n°5. I suggest you should try the postgis test suite in order to see if you are encountering the same problem. This is looking like a problem with endian character encoding, ok, but sometimes things are not like they look at first eye. Did you try to have more information with the method getNextException() or the second line of your logs is all the stuff you can get about it ? – Sadonia

I've found the following java hibernate postgis tutorial which suggest to do the things like I've told. It's in french, ok, but I could help if you need a translator (i'm french lol). In the suggested tutorial, it explains brievly the role of each annotations I've evoqued in my first comment. It seems you need to set them as I have suggested. – Sadonia

Answer 1

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!

Sadonia

This certainly fixes the endian problem, so thank you. However, predictably, I now have another internal error: Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Error while committing the transaction. The stack trace is unhelpful, and getting the hibernate config tags to work is more difficult than expected. Is this obvious to you, or does it require a new question? – Micrometry

Hello, maybe looking for any @Transactional stuff. I don't have enough time today (chess event). Maybe I could help you tomorrow. I will add a complete hibernate config so you could get a look at it. Good luck! – Sadonia

Hmmm. Maybe your databse model don't allow null values for any field concerned by the PostGIS object you want to store inside database. If you run your project with a breakpoint and hitting F6 to go step by step inside your java program, you will see when the exception will be thrown. You could hit F5 to go inside any method you want to check. Look at configuring remote debugging with eclipse and the container you are using (Tomcat, JBoss, GlassFish, etc.). – Sadonia

Good point; with Roo doing everything for me I'd almost forgotten about debugging. Think it's going to be a new question :(. – Micrometry

And here it is. If I could just get this one object to persist, I would be the happiest man in the world. I'd put a $100 bounty on it if I could. – Micrometry

I like bounty - you know the thing with chocolate and coco :D. You've juste gave me an idea for my blog, and it is enough for my help here. – Sadonia

Also future readers note that those hibernate.cfg.xml tags should be in the hibernate namespace, i.e. hibernate.show_sql and such. For Roo projects, they go in persistence.xml, and need logging turned on to at least the DEBUG level. – Micrometry

Kindly note that solution above is for hibernate 4, if you use hibernate 5 please check pilladooo's answer or my explanation – Rebatement

Answer 2

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

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

Durden

It solved the problem for me, too. I had hibernate.dialect=... instead of spring.jpa.properties.hibernate.dialect=... in application.properties. And column is defined like that: @Column(columnDefinition = "GEOGRAPHY(POINT)") private com.vividsolutions.jts.geom.Point location; – Chammy

Answer 3

For those who still struggle with this issue, I have been struggling with that for days and it turns out that my problem was, I used the wrong package. The geometries from com.vividsolutions have been moved to org.locationtech. So you should rather use org.locationtech. Maven says

Mccrea

thanks, you are a lifesaver... this was a pretty stupid bug to fix! – Coracorabel

Answer 4

pilladooo's solution works with spring boot 2.0.3, hibernate/spatial 5.2.17.Final, Postgres 9.5.

Column in entity in my case is defined as @Column(name = "geometry") private Geometry geometry;

and in database as type 'geometry' (to avoid bytea type that hibernate auto generates)

Firstly I solved 'Invalid endian flag value encountered' with adding columnDefinition = "geometry", but after that hibernate would fail schema validation with "Schema-validation: wrong column type encountered in column [geometry] in table [my_shema.my_geometry_table]; found [geometry (Types#OTHER)], but expecting [bytea (Types#VARBINARY)]"

after adding spring.jpa.properties.hibernate.dialect=org.hibernate.spatial.dialect.postgis.PostgisDialect it finally worked. ColumnDefinition is also redundant now

Rebatement

Answer 5

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.

Antho

Answer 6

I got it working with following config. I am using Spring Boot 2.5.1, PostGIS and hibernate-spatial 5.4.32.Final. Few key points:

1. The Point class I use is org.locationtech.jts.geom.Point
2. You don't need special annotations. This is my entity class.

Entity:

@Enity public class Vehicle {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;    
    
    private Point location;
}

Also in the application.properties, I use

spring.jpa.database-platform=org.hibernate.spatial.dialect.postgis.PostgisPG95Dialect

Then in the service class, I can save object to the database by calling as below.

public Vehicle createVehicle(String name, Double longitude, Double latitude) {
    GeometryFactory geometryFactory = new GeometryFactory(new PrecisionModel(), 4326);
        var point = geometryFactory.createPoint(new Coordinate(longitude, latitude));
    
        var vehicle = new Vehicle(name, point);      
        return vehicleRepository.save(vehicle);
}

Esoterica

Answer 7

My solution is something close to @quepasa's, but, IMHO, a bit easier.
I described it here.
In a nutt shell, the solution is to use these two dependencies:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<dependency>
    <groupId>org.hibernate.orm</groupId>
    <artifactId>hibernate-spatial</artifactId>
</dependency>

The second one should be exactly without version.

Weiman

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – Suggestibility

@Suggestibility Ok, thanks for the advice! I extended the answer with an in-a-nutt-shell section. – Weiman

Answer 8

After some fight with given problem here are steps that helped me to solve it. First I should mention I'm using WildFly 17 server, PostgreSQL 12 and PostGIS 3.0.0. And now steps that I think are important in this problem:

Make jboss-deployment-structure.xml file in META-INF (if you don't have one), and exclude hibernate that comes with WildFly

<jboss-deployment-structure>
    <deployment>
        <exclusions>
            <module name="org.hibernate" />
        </exclusions>
    </deployment>
</jboss-deployment-structure>

In your pom.xml add dependencies

<dependency>
    <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
    <version>5.4.12.Final</version>
</dependency>

and

<dependency>
    <groupId>org.hibernate</groupId>
        <artifactId>hibernate-spatial</artifactId>
    <version>5.4.12.Final</version>
</dependency>

Make sure that hibernate core and hibernate spatial have same version (use whatever version you like).

Your persistence.xml should have property

<property name="hibernate.dialect" value="org.hibernate.spatial.dialect.postgis.PostgisDialect"/>

And finally, I don't think this is very important but I used org.locationtech.jts for geometry in Java.

I hope I didn't skip anything important and that these are required steps. It is possible that there is something else, but many hours have passed in trying different solutions and it is possible that I forgot to include some dependency/property. Answer is based on personal experience, therefore feel free to comment, to prove me wrong or to expand answer. Anyhow I hope someone will find this answer useful.

Conduit

Answer 9

I've updated hibernate-core and hibernate-spatial to the 6.3.0.Final version, and the issue has been resolved.

Note: I'm using spring boot 3.0 and postgis 15.

Gesualdo

postgis 15 as in postgis dependency or... in your postgres DB? I'm facing the same issue after upgrading the spring boot 2 to 3 version – Coelom