I'm troubleshooting with an issue when using PostGIS function st_extent in HQL queries.
Entity/Repository:
{code:java}
@Entity
@Table(name = "field")
@Data
public class GeometryEntity {
@Id
@GeneratedValue(generator = "system-uuid")
@GenericGenerator(name = "system-uuid", strategy = "org.hibernate.id.UUIDGenerator")
private UUID id;
@Column(name = "the_geom")
private Polygon geometry;
}
public interface GeometryEntityRepository extends CrudRepository<GeometryEntity, UUID> {
@Query(value = "select extent(geometry) from GeometryEntity")
Optional<Polygon> getExtent getExtentByHQLQuery ();
@Query(value = "select envelope st_extent (geometry) from GeometryEntity")
Optional<Polygon> getEnvelope getExtentByST_PrefixedFunctionName ();
}
@Query(value = "select st_extent(the_geom) from field", nativeQuery = true)
Optional<Polygon> getExtentByNativeQuery();
} {code}
According to the doc [https://docs.jboss.org/hibernate/orm/6.1/userguide/html_single/Hibernate_User_Guide.html#spatial|https://docs.jboss.org/hibernate/orm/6.1/userguide/html_single/Hibernate_User_Guide.html#spatial|smart-link] extent and st_extent should work when using HQL queries.
I have three different queries, two of them are using HQL, the other one is written native.
None of them work, all results in an error.
The attempt to call getExtent {{getExtentByHQLQuery}} () results in:
{noformat}
2023-03-24T17:19:37.115+01:00 ERROR 456308 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: function extent(geometry) does not exist
Hinweis: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 8
org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select extent(g1_0.the_geom) from field g1_0]; SQL [n/a] {noformat}
The attempt to call { {getExtentByST_PrefixedFunctionName}}() and {{getExtentByNativeQuery()}}results in:
{ noformat} org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [org.locationtech.jts.geom.Polygon]
Using the st_envelope function works as expected at org . springframework.core.convert.support.GenericConversionService.handleConverterNotFound(GenericConversionService.java:322){noformat}
-
* spring-boot:3.0.4
- * postgres/postgis
- * hibernate-spatial:6.1.7
Simple app to reproduce the error:
[ https://github.com/marcozet/spring-boot-3-hibernate-spatial
|https://github.com/marcozet/spring-boot-3-hibernate-spatial] |
|
