Spring Data repository and native query with Geometry return type

I'm creating a project that uses some spatial queries.
I use Spring boot with spring data repositories and PostgreSQL with PostGIS extension as database.

I created this repository:

import com.vividsolutions.jts.geom.Geometry;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface AreaRepository extends CrudRepository<Area, Long>

/*
extra queries for Area here
*/

@Query(value="select st_intersection(" +
":base_layer ," +
":filter_layer" +
")", nativeQuery = true)
Geometry geometryIntersectGeometry(@Param("base_layer") Geometry baseGeometry,@Param("filter_layer") Geometry filterGeometry);




It contains some queries for the Area entity. I also want to use some PostGIS functions to do some calculations, so I created geometryIntersectGeometry to call the st_intersection function from PostGis, this should return a geometry.

I set the hibernate dialect to PostGIS in the settings:

spring.datasource.driverClassName=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/test_db
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.jpa.properties.hibernate.dialect = org.hibernate.spatial.dialect.postgis.PostgisDialect


And I have the dependencies for hibernate spatial:

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-spatial</artifactId>
<version>$hibernate.version</version>
</dependency>
...


Calling the geometryIntersectGeometry function results in an error:

No Dialect mapping for JDBC type: 1111; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111,


How do I tell JPA/Spring Data to map the geometry (PostGIS type) response to a Geometry(com.vividsolutions.jts.geom.Geometry) object?

3 Answers
3

Have you added Hibernate Spatial?

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


This has support for GIS data: http://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html#spatial

Managed to fix it by writing a custom implementation of the repository and register the type (thx Simon Martinelli)

repository:

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface AreaRepository extends CrudRepository<Area, Long>, AreaGisRepository

/*
extra queries for Area here
*/




interface:

import com.vividsolutions.jts.geom.Geometry;

public interface AreaGisRepository

Geometry geometryIntersectGeometry(Geometry baseGeometry, Geometry filterGeometry);



and implementation:

import com.vividsolutions.jts.geom.Geometry;
import org.hibernate.spatial.JTSGeometryType;
import org.hibernate.spatial.dialect.postgis.PGGeometryTypeDescriptor;
import org.springframework.beans.factory.annotation.Autowired;

import javax.persistence.EntityManager;

public class AreaGisRepositoryImpl implements AreaGisRepository

private EntityManager entityManager;

@Autowired
public AreaGisRepositoryImpl(EntityManager entityManager)
this.entityManager = entityManager;


@Override
public Geometry geometryIntersectGeometry(Geometry baseGeometry, Geometry filterGeometry)
return (Geometry) entityManager
.createNativeQuery(
"select st_intersection(:base_layer , :filter_layer) as geom")
.setParameter("base_layer", baseGeometry)
.setParameter("filter_layer", filterGeometry)
.unwrap(org.hibernate.query.NativeQuery.class)
.addScalar("geom", new JTSGeometryType(PGGeometryTypeDescriptor.INSTANCE))
.getSingleResult();





It works perfect, but I now have a hard coded dependency on Postgis (not likely we will use something else, but ...)

I think there is a simpler way to get this done. Hibernate Spatial registers a number of spatial functions for use in HQL/JQL. So the following should work

@Query(value="select intersection(" +
":base_layer ," +
":filter_layer" +
")")
Geometry geometryIntersectGeometry(@Param("base_layer") Geometry baseGeometry,@Param("filter_layer") Geometry filterGeometry);


See the documentation for a list of functions available in the Spatial Dialects.

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy