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)

Answer 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)