Class DistanceHaversineJdbcFunction
java.lang.Object
ch.tocco.nice2.persist.core.api.hibernate.query.AbstractJdbcFunction
ch.tocco.nice2.optional.geolocation.impl.jdbc.DistanceHaversineJdbcFunction
- All Implemented Interfaces:
JdbcFunction
Writes the Haversine function using SQL functions. The function calculates the distance between two points on earth's surface. This can then be used to create queries that filter on distances.
The formula itself:
RADIUS_EARTH * 2 * ASIN(MIN(1, SQRT(POW(SIN((lat2-lat1) / 2),2) + COS(lat1) * COS(lat2) * POW(SIN((lng2-lng1)/2), 2))))
It needs 4 arguments (exactly) and expects it in the following order:
latitude1, longitude1, latitude2, longitude2
Where (latitude1, longitude1)
name the first points coordinates and
(latitude2, longitude2)
name the second points coordinates. The
coordinates are assumed to be given in RAD.
For example, consider the following query:
Query q = ctx.compileQuery("find Cities where DISTANCE(pos_lat, pos_lng, :lat, :lng) < :dst");
Position zurich = new Position(...);
q.setParameter("pos_lat", zurich.getLatitude());
q.setParameter("pos_lng", zurich.getLongitude());
q.setParameter("dst", Distance.kilometer("15").getKm());
This will select all cities that are at maximum 15km away from Zurich (assuming that the "Cities" entity has two fields "pos_lat" and "pos_lng" of respective type.)
-
Field Summary
Fields inherited from class ch.tocco.nice2.persist.core.api.hibernate.query.AbstractJdbcFunction
returnType
-
Constructor Summary
-
Method Summary
Modifier and TypeMethodDescriptionint
int[]
Order of parameters as required in SQL statement.org.hibernate.dialect.function.SQLFunction
createFunction
(org.hibernate.type.TypeResolver typeResolver, SqlWriter sqlWriter) void
setEarthRadius
(BigDecimal earthRadius) void
validateArguments
(jakarta.persistence.criteria.Expression<?>[] arguments) Methods inherited from class ch.tocco.nice2.persist.core.api.hibernate.query.AbstractJdbcFunction
checkIfFunctionCallOfType, checkIfLiteralOrParameter, checkIfReferenceOfType, findHibernateType, getReturnType, writeArgument
-
Field Details
-
EARTH_RADIUS_KM
-
EARTH_RADIUM_MILES
-
-
Constructor Details
-
DistanceHaversineJdbcFunction
-
-
Method Details
-
createFunction
public org.hibernate.dialect.function.SQLFunction createFunction(org.hibernate.type.TypeResolver typeResolver, SqlWriter sqlWriter) - Returns:
- an
SQLFunction
that contains the raw sql statement that is executed on the db. - See Also:
-
validateArguments
public void validateArguments(jakarta.persistence.criteria.Expression<?>[] arguments) throws JdbcFunctionException - Throws:
JdbcFunctionException
-
argumentCount
public int argumentCount()- Returns:
- the number of expected arguments.
-
argumentOrder
public int[] argumentOrder()Description copied from interface:JdbcFunction
Order of parameters as required in SQL statement. This method must be implemented when the order or count of query parameters is different to the SQL parameters. The returned array lists indices of params in order as they are used for the SQL query. -
setEarthRadius
-