Postgres/PostGIS st_distance_sphere function with EntityManager.createQuery incomprehensible error

Question

I try to calculate the distance between two points using PostGIS. I figured out that postGIS has st_distance_sphere function for this, and tried the code below:

private fun getDeliveryDistance(areaCentroid: Point, buyerLocation: Point): Double {
    val query = em.createQuery(
        "select st_distance_sphere(st_makepoint(${areaCentroid.x}, ${areaCentroid.y}), st_makepoint(${buyerLocation.x}, ${buyerLocation.y}))",
        Double::class.java
    )
    val result = query.singleResult
    return result
}

but instead of correct result i got the following error:

java.lang.IllegalArgumentException: org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 
 \-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'st_distance_sphere' {originalText=st_distance_sphere}
    \-[EXPR_LIST] SqlNode: 'exprList'
       +-[METHOD_CALL] MethodNode: '('
       |  +-[METHOD_NAME] IdentNode: 'st_makepoint' {originalText=st_makepoint}
       |  \-[EXPR_LIST] SqlNode: 'exprList'
       |     +-[NUM_DOUBLE] LiteralNode: '38.970335125923135'
       |     \-[NUM_DOUBLE] LiteralNode: '45.0543938370861'
       \-[METHOD_CALL] MethodNode: '('
          +-[METHOD_NAME] IdentNode: 'st_makepoint' {originalText=st_makepoint}
          \-[EXPR_LIST] SqlNode: 'exprList'
             +-[NUM_DOUBLE] LiteralNode: '38.97033512592316'
             \-[NUM_DOUBLE] LiteralNode: '45.05439383708615'
 [select st_distance_sphere(st_makepoint(38.970335125923135, 45.0543938370861), st_makepoint(38.97033512592316, 45.05439383708615))]]

Unfortunately, I have no idea about this error. But when I copy the query in debug mode or from last line of stack trace before calling getSingleResult() and then execute it in pgAdmin console, I get the correct result. Does somebody know how can I fix it?

Answer 1

@Query(
    """select topology.st_distance(topology.st_makepoint(:restaurantLat, :restaurantLong), topology.st_makepoint(:buyerLat, :buyerLong))""",
    nativeQuery = true
)
fun calculateDistance(
    @Param("restaurantLat") restaurantLat: Double,
    @Param("restaurantLong") restaurantLong: Double,
    @Param("buyerLat") buyerLat: Double,
    @Param("buyerLong") buyerLong: Double
): Double

I just specified postgres schema explicitly and it solved my problem