Thursday, December 17, 2015

A story about Junit, Hibernate Spatial, Spring and H2

Lately, I've been tinkering (yes, tinkering, so do not expect the article of a guru who can explain you all the hidden and nearly magical mechanics beyond the code) with geospatial stuff.

Interesting subject, definitely. Had been working with the same subject also five years ago, but the evolution of both the standards and the implementations was so huge that, now, it is basically a brand new subject.

Anyway, to start diving into it, I set some fixed point:
1. Hibernate
2. Spring
3. JPA

Although some DBMS seem to be better than others, right now (on this subject PostgreSQL seems to be a step forward,  but it's difficult to read all the documentation possible), the first aim is to remain agnostic about the implementation in data layer.

That's the reason beyond the choice of Hibernate and JPA. JPA does not support geospatial operations oout of the box, but using HQL or Hibernate criterias does the trick.

And, in all fairness, it's definitely easy. Latest versions of Hibernate Spatial (5+) handle natively geometric types. In previous versions you had to annotate the entity property and, back when I first worked on the subject, it was almost a nightmare reading and writing geospatial data. Now, everything is so straightforward that persisting a geometric field is as difficult as persisting an integer.

After a bit of coding, I wanted to test what I did (and bully myself, of course :-) ). Though having everything wired up correctly was far less easy that coding.

As I said, I opted for libraries that are almost standards, nowadays, and above all, with very large communities, and started building up a unit test over them.
The DB chosen was H2 which, reading a bit around, is the one DB who can run in memory (essential for unit testing) and has geospatial extensions, via third party libraries. Amongst them, I've been looking at geodb and h2gis and opted for the first, for the simple reason that I found more literature and tutorials on the web.

Enough chat, and a bit of code.

The dependencies added in Maven for testing:

  <id>OSGEO GeoTools repo</id>
  <id>Map Fish repo</id>



plus, of course, hibernate-spatial:5.0.5.Final and all the needed dependencies of Spring Framework (core, context and data, mostly).

Then, set up the persistence context

 xmlns="" xmlns:xsi=""
 <persistence-unit name="testPU" transaction-type="RESOURCE_LOCAL">
   <property name="hibernate.connection.url" value="jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;IGNORECASE=TRUE" />
   <property name="hibernate.connection.driver_class" value="org.h2.Driver" />
            <property name="hibernate.dialect" value="org.hibernate.spatial.dialect.h2geodb.GeoDBDialect" />
   <property name="" value="create-drop" />
   <property name="hibernate.show_sql" value="true"/>
   <property name="hibernate.format_sql" value="true"/>

Note above all the dielect used. Hibernate Spatial comes shipped with lots of dialects for geospatial operations, including MySql, PostgreSQL (Postgis), SqlServer and Oracle.

And the Spring context (only the essential part of it to wire up DB connections and transactions)

   <bean class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close" id="dataSource">
       <property name="driverClassName" value="org.h2.Driver"/>
       <property name="url" value="jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;IGNORECASE=TRUE"/>
       <property name="testOnBorrow" value="true"/>
       <property name="testOnReturn" value="true"/>
       <property name="testWhileIdle" value="true"/>
       <property name="timeBetweenEvictionRunsMillis" value="1800000"/>
       <property name="numTestsPerEvictionRun" value="3"/>
       <property name="minEvictableIdleTimeMillis" value="1800000"/>
<bean class="org.springframework.orm.jpa.JpaTransactionManager"
 <property name="entityManagerFactory" ref="entityManagerFactory" />
 <property name="persistenceUnitName" value="testPU" />
 <property name="dataSource" ref="dataSource" />

<tx:annotation-driven mode="aspectj" transaction-manager="transactionManager" />

The entity class is a very simple one:

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
import com.vividsolutions.jts.geom.Geometry;

@Table(name = "GEO_ENTITY")
public class GeoEntity {

 private static final long serialVersionUID = 6672700707065815510L;
 private int id;
 @Column(unique = true)
 private String name;
 private Geometry location;
 public int getId() {
  return id;
 public int setId(int id) { = id;
 public String getName() {
  return name;
 public void setName(String name) { = name;
 public Geometry getLocation() {
  return location;
 public void setLocation(Geometry location) {
  this.location = location;

Note the type `Geometry` used for the property `location`: it comes shipped with Hibernate Spatial, so you don't have to worry about digging some myterious maven repo to find it. Hibernate, as said, handles it natively and you don't have o worry about that.
There is a particular way for creating Geometry objects, which is provided by the library itself. Just have a look at the documentation of WKTReader.

So far, it was quite straightforward, wasn't it?
And actually, it won't become overly complicated all of a sudden.
You can now write the first unit test. Persisting an entity with geospatial data inside and check that everything worked as planned.

import static;
import static org.junit.Assert.assertThat;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;

import com.vividsolutions.jts.geom.Geometry;

@ContextConfiguration(locations = "classpath*:META-INF/spring/context.xml")
public class CreationTestCase implements TestCaseData {
 private static final Logger log = LoggerFactory.getLogger(CreationTestCase.class);
 private GeoEntityService geoEntityService;
  * @return the geoEntityService
 public GeoEntityService getGeoEntityService() {
  return geoEntityService;
  * @param geoEntityService the geoEntityService to set
 public void setGeoEntityService(GeoEntityService geoEntityService) {
  this.geoEntityService = geoEntityService;

 public void creation() throws ParseException {
  GeoEntity geoEntity = geoEntityService.createTargetInstance();
  Geometry g = new WKTReader().read(WKT_POLYGON_1);  
  assertThat(geoEntityService.count(), is(1L));
  log.debug("Reading entity");
  GeoEntity geoEntity2 = geoEntityService.findById(geoEntity.getId());
  assertThat(geoEntity.getId(), is(geoEntity2.getId()));
  assertThat(geoEntity.getLocation(), is(geoEntity2.getLocation()));

There are some things to say about this class:
  1. it's a junit4 test case
  2. uses Spring runner instead of standard Junit runner (it permits lo load the Spring context)
  3. it's marked as `Transactional` which means that the DB connection is dropped and recreated at every test. Combined with the persistence.xml, it means that every test starts with a clean DB, as it should.
  4. the GeoEntityService is a class which exposes methods for saving and reading objects from the DB. It depends on your preference how to implement it (native Hibernate, Spring JPA, ...) and its implementation is beyond the scope of this article.

Last but not least!

When it comes to query the DB with geospatial fuctions the situation became a little frustrating, because I got an error who drove me crazy for a bit, before solving it:

[ERROR] 2015-12-16 11:16:15,000: org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions:129: Function "ST_CONTAINS" not found; SQL statement:
select as id1_0_, geoentity0_.location as location2_0_, as name3_0_ from GEO_ENTITY geoentity0_ where ST_Contains(geoentity0_.location, ?)=1 [90022-190]

Looked like Hibernate wasn't able to find the correct function, but it's false. The problem is that H2, natively, does not know the functions "ST_*", and it must be instructed to add them. Fortunately, you can instruct H2 to map Java code as native functions, which is where `geodb` comes in.

At start, H2 searches for a file named `import.sql` in classpath root to initialize the DB (the name and the location of the file can be changed, but it's a useless complexity for a local unit test). And that's the point to tweak it. Just create this file, and put this content into it

CREATE ALIAS AddGeometryColumn for "geodb.GeoDB.AddGeometryColumn"
CREATE ALIAS CreateSpatialIndex for "geodb.GeoDB.CreateSpatialIndex"
CREATE ALIAS DropGeometryColumn for "geodb.GeoDB.DropGeometryColumn"
CREATE ALIAS DropGeometryColumns for "geodb.GeoDB.DropGeometryColumns"
CREATE ALIAS DropSpatialIndex for "geodb.GeoDB.DropSpatialIndex"
CREATE ALIAS EnvelopeAsText for "geodb.GeoDB.EnvelopeAsText"
CREATE ALIAS GeometryType for "geodb.GeoDB.GeometryType"
CREATE ALIAS ST_Area FOR "geodb.GeoDB.ST_Area"
CREATE ALIAS ST_AsText FOR "geodb.GeoDB.ST_AsText"
CREATE ALIAS ST_Buffer FOR "geodb.GeoDB.ST_Buffer"
CREATE ALIAS ST_Centroid FOR "geodb.GeoDB.ST_Centroid"
CREATE ALIAS ST_Crosses FOR "geodb.GeoDB.ST_Crosses"
CREATE ALIAS ST_Contains FOR "geodb.GeoDB.ST_Contains"
CREATE ALIAS ST_DWithin FOR "geodb.GeoDB.ST_DWithin"
CREATE ALIAS ST_Disjoint FOR "geodb.GeoDB.ST_Disjoint"
CREATE ALIAS ST_Distance FOR "geodb.GeoDB.ST_Distance"
CREATE ALIAS ST_Envelope FOR "geodb.GeoDB.ST_Envelope"
CREATE ALIAS ST_Equals FOR "geodb.GeoDB.ST_Equals"
CREATE ALIAS ST_GeoHash FOR "geodb.GeoDB.ST_GeoHash"
CREATE ALIAS ST_GeomFromText FOR "geodb.GeoDB.ST_GeomFromText"
CREATE ALIAS ST_Intersects FOR "geodb.GeoDB.ST_Intersects"
CREATE ALIAS ST_IsEmpty FOR "geodb.GeoDB.ST_IsEmpty"
CREATE ALIAS ST_IsSimple FOR "geodb.GeoDB.ST_IsSimple"
CREATE ALIAS ST_IsValid FOR "geodb.GeoDB.ST_IsValid"
CREATE ALIAS ST_MakePoint FOR "geodb.GeoDB.ST_MakePoint"
CREATE ALIAS ST_MakeBox2D FOR "geodb.GeoDB.ST_MakeBox2D"
CREATE ALIAS ST_Overlaps FOR "geodb.GeoDB.ST_Overlaps"
CREATE ALIAS ST_Simplify FOR "geodb.GeoDB.ST_Simplify"
CREATE ALIAS ST_Touches FOR "geodb.GeoDB.ST_Touches"
CREATE ALIAS ST_Within FOR "geodb.GeoDB.ST_Within"
CREATE ALIAS Version FOR "geodb.GeoDB.Version"

And that's it! Junit test environment is ready for use.


No comments:

Post a Comment