CODEWITHSUNDEEP
javapostgresqlspring-data-jpapostgis
Paco Abato
Paco Abato

Reputation: 4065

Execute PostGIS functions in a JpaRepository's query

I have a JpaRepository like this one:

public interface PipeRepository extends JpaRepository<Pipe, Long> {
    @Query("select p from Pipe p where st_intersects(p.geometry, ?1)=true")
    Collection<Pipe> find(Geometry envelope);

    @Query(value = "SELECT st_extent(p.geometry) FROM Pipe p WHERE p.id IN ?1")
    Geometry getPipe(Collection<Number> id);
}

The first one works OK but the second one throws this exception during initialization:

org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode
\-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'st_extent' {originalText=st_extent}
    \-[EXPR_LIST] SqlNode: 'exprList'
       \-[DOT] DotNode: 'pipe0_.geometry' {propertyName=geometry,dereferenceType=PRIMITIVE,getPropertyPath=geometry,path=p.geometry,tableAlias=pipe0_,className=es.x.model.Pipe,classAlias=p}
          +-[ALIAS_REF] IdentNode: 'pipe0_.id' {alias=p, className=es.x.model.Pipe, tableAlias=pipe0_}
          \-[IDENT] IdentNode: 'geometry' {originalText=geometry}

Any hint of the reason why st_intersects works and st_extent doesn't?

UPDATE: I think that the issue is with Spring Data JPA not recognizing the PostGIS functions. This aggregate function (SQL's sum) works correctly:

@Query("select sum(p.id) from Pipe p where p.id in ?1")
Number getPipesSum(Collection<Number> ids);

Upvotes: 3

Views: 2830

Answers (2)

paulscott56
paulscott56

Reputation: 506

I had a similar issue, and solved it as follows:

In your Spring Boot Repository interface, you can use st_astext() to receive your query results as WKT. 

@Query(value = "SELECT st_astext(st_extent(p.geometry)) FROM Pipe p WHERE p.id IN ?1")
String getPipe(Collection<Number> id);

You will see that the return type is a String (which may or may not be good).

Once you have your WKT back from the database, I used org.locationtech.jts.geom.GeometryFactory to convert it back into a Geometry

@Autowired
private GeometryFactory factory;

WKTReader reader = new WKTReader(factory);
Geometry geom = reader.read(yourReturnVariable);

If you look at geom.getGeometryType() you will see that it is a viable Geometry that you can then use in other operations.

As I said before, this may not be a good solution, but it does work

Upvotes: 2

gaston
gaston

Reputation: 498

Maybe, because the st_extent funtion returns box2d dataType.

The result for the query is

"BOX(x1 y1,x2 y2)" , but you can cast to geometry

SELECT st_extent(p.geometry)::geometry FROM Pipe p WHERE p.id = ?1

Or perhaps (to avoid the use of ::)

SELECT st_geometryFromText(st_astext(st_extent(p.geometry))),<your SRID>) FROM Pipe p WHERE p.id = ?1

I hope it works

Upvotes: 1

Related Questions