JTS geometry in Hibernate spatial generating " ERROR: function within(geometry, bytea) does not exist"

1.2k views Asked by At

Hibernate-spatial 5.4.22, hibernate.dialect = org.hibernate.spatial.dialect.postgis.PostgisDialect

A very straightforward query:

    import org.locationtech.jts.geom.Geometry;
...
@Query(value = "Select s from #{#entityName} s where within(s.shape, :bounds )= true")
public List<SiteModel> findWithinBounds(Geometry bounds);

The bounds geometry is generated by:

GeometryFactory gf = new GeometryFactory();
Polygon bounds = gf.createPolygon(sc);
bounds.setSRID(4326);
return newSiteService.findWithinBounds(bounds);

But it generates error

    select
    sitemodel0_.site_id as site_id1_1_,
    sitemodel0_.accuracy as accuracy2_1_,
    sitemodel0_.comment as comment3_1_,
    sitemodel0_.country_code as country_4_1_,
    sitemodel0_.directions as directio5_1_,
    sitemodel0_.flag as flag6_1_,
    sitemodel0_.height as height7_1_,
    sitemodel0_.h_accuracy as h_accura8_1_,
    sitemodel0_.h_method_id as h_method9_1_,
    sitemodel0_.latitude as latitud10_1_,
    sitemodel0_.longitude as longitu11_1_,
    sitemodel0_.method_id as method_12_1_,
    sitemodel0_.orig_coord as orig_co13_1_,
    sitemodel0_.orig_system_id as orig_sy14_1_,
    sitemodel0_.owner_id as owner_i15_1_,
    sitemodel0_.shape as shape16_1_,
    sitemodel0_.site_name as site_na17_1_ 
from
    sc.site_proposed sitemodel0_ 
where
    within(sitemodel0_.shape, ?)=true
WARN : SQL Error: 0, SQLState: 42883
ERROR: ERROR: function within(geometry, bytea) does not exist

So it seems to picked up that postgis shape field is geometry ok. (it is postgis geometry type), but is failing to understand the JTS geometry object. I have seen many questions about the reverse, but not this error.

2

There are 2 answers

3
Karel Maesen On

PostgisDialect has been deprecated for quite some time. You should use one of more recent dialects for Postgis such as PostgisPG95Dialect. You should see then in the SQL a st_within() function rather than within().

0
Scaddenp On

Thanks to the hint from @Karel Maesen, I did get it working. I need to put

hibernate.dialect = org.hibernate.spatial.dialect.postgis.PostgisDialect
spring.jpa.properties.hibernate.dialect = org.hibernate.spatial.dialect.postgis.PostgisPG95Dialect

into properties. With that done, the spatial queries, with and dwithin are both work.

@Query(value = "Select s from #{#entityName} s where within(s.shape, :bounds )= true")
public List<SiteModel> findWithinBounds(Geometry bounds);

@Query(value = "Select s from #{#entityName} s where dwithin(s.shape, :point, :distance)= true")
public List<SiteModel> findCloseTo(Geometry point, double distance);