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> getExtentByHQLQuery();
@Query(value = "select st_extent(geometry) from GeometryEntity")
Optional<Polygon> 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] using extent and 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 {{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]
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] |
|
