Postgis spatial function not working with hibernate

Question

I have native query for a JpaRespository as:

@Query(value = "SELECT * FROM Feature f WHERE f.feature_type = :featureType AND " +
            "ST_DWithin(geometry, 'SRID=:srid;POINT(:lon :lat 0)', :range)", nativeQuery = true)

Query generated is as:

SELECT * FROM Feature f WHERE f.feature_type = ? AND ST_DWithin(geometry,'SRID=:srid;POINT(:lon :lat 0)', ?)

geometry is a column in table containing spatial data.

But traces also tell that query have a parse error:

Hint: "SR" <-- parse error at position 2 within geometry

But when I execute query in database, I get results correctly.

Why this mismatch?

I could finally solve it by:

@Query(value = "SELECT * FROM Feature f WHERE f.feature_type = :featureType AND " +
            "ST_DWithin(geometry, ST_GeographyFromText('SRID=' || :srid || ';POINT(' || :lon ||' ' || :lat || ' 0)'), :range)", nativeQuery = true)

Eklavya · Accepted Answer · 2020-06-10 16:43:23Z

1

You are binding parameter inside a string literal that's why no bind parameter replaced.

'SRID=:srid;POINT(:lon :lat 0)'

You can create a string using this data and pass the whole string in the method.

Another way is using database concat operation but parameters needs to be string

'SRID=' || :srid|| ';POINT(' || :lon ||' ' || :lat || ' 0)'

Full query like

@Query(value = "SELECT * FROM Feature f WHERE f.feature_type = :featureType AND " +
            "ST_DWithin(geometry, 'SRID=' || :srid || ';POINT(' || :lon ||' ' || :lat || ' 0)', :range)", nativeQuery = true)

Or use Database functions

ST_DWithin(geometry, ST_SetSRID(ST_Point( :lon, :lat), :srid), :range)

edited Jun 10, 2020 at 16:43

answered Jun 10, 2020 at 15:59

Eklavya

18.6k4 gold badges33 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mandroid Over a year ago

This throws: Caused by: org.postgresql.util.PSQLException: ERROR: function st_dwithin(geometry, text, double precision) is not unique Hint: Could not choose a best candidate function. You might need to add explicit type casts.

Mandroid Over a year ago

Passing string may help.

Eklavya Over a year ago

Check the function definition may be function is not unique based on data. That obviously your passing data problem

Mandroid Over a year ago

Strange. Fcuntion has 2 diff variants, with varyign number of parameters.postgis.net/docs/ST_DWithin.html

Eklavya Over a year ago

Here what is first parameter of st_dwithin function ?

|

Collectives™ on Stack Overflow

Postgis spatial function not working with hibernate

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related