CriteriaBuilder radius calculation

Question

I am trying to calculate given lat/long with a distance, and only returning the rows that fall in that circular distance (Database has lat / long coordinates aswell). What I am unaware of is how I would do that. I am currently using Native SQL and have plain text with the haversine formula that calculates this, and if I am correct it is not possible to do the haversine formula with the CriteriaBuilder. However I want to have the same results using the CriteriaBuilder. I've tried using the hibernate-spatial dependency, but I am not getting it to work as I want it to. I have also followed tutorials such as this one.

https://docs.jboss.org/hibernate/search/4.2/reference/en-US/html/spatial.html

The database I am using is MySQL.
Hibernate version 4.3.10.

Also this tutorial didn't get me far Using JPA Criteria Api and hibernate spatial 4 together

So how would I build a CriteriaBuilder query with MySQL with a given lat / long and distance and retrieve only rows that fall in that area, comparing them to the lat / long coordinates stored in the database.

Answer 1

I have tried the following and it works.

@Query(value = "SELECT  s FROM Address s WHERE 1 = 1 AND " +
        "(pow(69.1 * (s.latitude - ?1), 2) + pow(69.1 * (?2 - s.longitude) * cos(s.latitude / 57.3), 2)) < pow(?3,2)" 

)
List<Address> findNearbyAddress(double lat, double lng, double radius);  

radius in miles. ?1, ?2, ?3 are parameters placeholder.
Its need some calculation to measure the distance from latitude & longitude. You can't call database native function from CriteriaBuilder.

Answer 2

CriteriaBuilder is not powerful enough for such complex queries. But you can do it anyway with this trick:

1. Create a MySQL FUNCTION
2. Call this FUNCTION by using CriteriaBuilder#function(..)

It should by also pretty fast. Whereby I have to note, that mysql is not really suitable for such use case like distance calculation.