Hibernate and Postgres : function st_buffer(bytea, numeric) is not unique

Question

I'm working with Hibernate 5.1.0 in a Java application. I'm connecting both to a Postgres 9.5 with Postgis extensions and Oracle databases. I need to find all the geometries in my database that intersect with a given geometry to which I apply a buffer, such as :

Query query = session
            .createQuery("select b from Block b where intersects(b.geom, buffer(:geometry, " + bufferDistance + ")) = "
                    + UtilsHelper.getTrueBooleanValue(em));
query.setParameter("geometry", geom);
List<Block> blocks = query.list();

That works in Oracle, but in Postgres I will get the error :

Caused by: org.postgresql.util.PSQLException: ERROR: function st_buffer(bytea, numeric) is not unique
Hint: Could not choose a best candidate function. You might need to add explicit type casts.
Position: 243
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)

This makes sense, as it will not be able to choose between one of the following functions :

geometry ST_Buffer(geometry g1, float radius_of_buffer);
geography ST_Buffer(geography g1, float radius_of_buffer_in_meters);

The UtilsHelper.getTrueBooleanValue(em) will just get the right boolean value depending on the entity manager, that is, 0/1 for Oracle and true/false for Postgres.

One obvious solution would be to drop one of the functions. Other than that, is there any way I can fix this?

score 3 · Answer 1 · answered Jun 01 '16 at 06:25

I won't claim to know much about Hibernate, but it seems that a simple fix is to explicitly cast the bytea with CAST(:geometry AS geometry), and modernize the rest query to add a "ST_" prefix, which is used with newer PostGIS versions.

More importantly, you should never write a query with the form:

SELECT b
FROM Block b
WHERE ST_Intersects(b.geom, ST_Buffer(CAST(:geometry AS geometry), :bufferDistance)) = TRUE;

Using a buffer to select a region is slower and imperfect than using a distance-based ST_DWithin function to find the geometries that are within a distance. ST_DWithin can also use a spatial index, if available. And a boolean operator does not need to have the = + UtilsHelper.getTrueBooleanValue(em) part (i.e. TRUE = TRUE is TRUE). Just remove it.

Try to write a function that looks more like:

SELECT b
FROM Block b
WHERE ST_Dwithin(b.geom, CAST(:geometry AS geometry), :distance);

(using two parameters :geometry and :distance)

Thanks for your answer, Mike T. I'm trying to make it work with that CAST. Anyway that will probably be good only for Postgres and not for Oracle. As commented, I need this to work with both DBs. Precisely that's why I'm relying on Hibernate to generate the actual query, be it with ST_Buffer or any ST_ prefix for Postgres or the SDO equivalent in Oracle. — krause, Jun 09 '16 at 14:36
@krause again I'm not familiar with Hibernate, but Oracle has [SDO_WITHIN_DISTANCE](https://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_operat.htm#i77653) which is equivalent to ST_DWithin mentioned above. — Mike T, Jun 09 '16 at 21:19

Hibernate and Postgres : function st_buffer(bytea, numeric) is not unique

1 Answers1