JPQL to get nearest records by latitude and longitude

Question

I'm have entities with latitude and longitude and I would like to select those nearest to a given point.

I found this example that looks like it would work

SELECT *,
    SQRT(POW((69.1 * (locations.latitude - 27.950898)) , 2 ) +
    POW((53 * (locations.longitude - -82.461517)), 2)) AS distance
FROM locations
ORDER BY distance ASC
LIMIT 5

But I would prefer to do it with JPQL since I have a bunch of other joins etc that feels easier to do in JPQL instead of native query.

When I try something like this the JPA is complaining about the SQRT token.

SELECT DISTINCT p, SQRT(....) AS distance, FROM Place p .... ORDER BY distance ASC

Anyone has knows how to write a query like this, or maybe another better approach?

Thanks! /Oskar

Answer 1

The JPQL language reference says that:

functions_returning_numerics ::= ABS(simple_arithmetic_expression) | SQRT(simple_arithmetic_expression) | MOD(simple_arithmetic_expression, simple_arithmetic_expression) | SIZE(collection_valued_path_expression)

So, you don't have POW.

You can safely use a native query.

The approach I'd prefer, though, is to make the query without the calculations and then make the calculation on the result in your code. It shouldn't be a big performance hazard.

Answer 2

As far as I can tell from researching this and may trial and errors JPQL is simply not equipped to handle this scenario. I had to rewrite my queries as native.