CODEWITHSUNDEEP
hibernatepostgis
Mandroid
Mandroid

Reputation: 7504

Postgis spatial function not working with hibernate

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)

Upvotes: 0

Views: 697

Answers (1)

Eklavya
Eklavya

Reputation: 18450

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)

Upvotes: 1

Related Questions