I am using Java 8 with JPA and Hibernate 5.2.1 and MySQL database.
I have a table with users GPS coordinates:
Row 1:
LAT_1: 28.85, LONG_1: -25.766667
LAT_2: 27.15, LONG_2: -26.133333
Row 2:
LAT_1: 27.25, LONG_1: -26.234567
LAT_2: 28.98, LONG_2: -25.456666
Then I have a user selected value of coordinates too:
LAT_USER: 27.22, LONG_USER: -26.89998
I need the result set in from the database query to return rows ordered by distance from the closest GPS point (asc).
In this example, the closest GPS point to LAT_USER & LONG_USER will be:
For Row 1:
LAT_2 & LONG_2
For Row 2:
LAT_1 & LONG_1
Is this possible?
Here is my base code so far:
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<T> criteriaQuery = criteriaBuilder.createQuery(entityClass);
// from
Root<T> from = criteriaQuery.from(entityClass);
criteriaQuery.select(from);
// order by
List<Order> order = new ArrayList<Order>();
// ????
criteriaQuery.orderBy(order);
List<T> results = (List<T>) entityManager.createQuery(criteriaQuery).setFirstResult(firstResult)
.setMaxResults(maxResults).getResultList();
return results;
I would appreciate anyone's help.
Thanks
UPDATE:
My table structure is as follows (I have other join tables, but this is for simplicity sake):
---------- ------------------- -----------
| EMPLOYEE | | EMPLOYEE_LOCATION | | LOCATION |
---------- ------------------- -----------
|ID | |EMP_ID | | ID |
| | |LOC_ID | | LATITUDE |
| | | | | LONGITUDE |
---------- ------------------- -----------
My Employee Model Object has locations:
@OneToMany(cascade = CascadeType.ALL, fetch=FetchType.EAGER)
@JoinTable
(
name="employee_location",
joinColumns={ @JoinColumn(name="EMP_ID", referencedColumnName="ID") },
inverseJoinColumns={ @JoinColumn(name="LOC_ID", referencedColumnName="ID", unique=true) }
)
private Set<Location> locations;
I understand to achieve what I need, I need to do the Haversine formula calculation on the database because I need to order the result set with the calculations result.
I know this will result in a pretty complicated query because it needs to find the lowest distance, and order by that.
SELECT e.id,
( 6371 * acos( cos( radians(37) ) * cos( radians( l.LATITUDE) ) * cos( radians( l.LONGITUDE) - radians(-122) ) + sin( radians(37) ) * sin( radians( l.LATITUDE) ) ) ) AS distance
FROM www.employee AS e
inner join
www.employee_location as el
on e.id=el.EMP_ID
inner join
www.location as l
on el.LOC_ID=l.ID
order by DISTANCE
This returns the ID and the DISTANCE correctly, however it returns 3 EMPLOYEES because there are 3 LOCATIONS for that EMPLOYEE. When it is wired in JPA (as above), this returns 1 EMPLOYEE with 3 LOCATIONS.
Does anyone know how I could preferably keep this in JPA objects and add custom SQL for the Haversine formula (much simpler because I have other joins. Or is this not possible?). Then, I need to order by the distance of the lowest LOCATION for that EMPLOYEE.
UPDATE
I will use something like this:
select ID, min(distance) as mindistance from (
select e.ID,
( 6371 * acos( cos( radians(37) ) * cos( radians( o.LATITUDE) ) * cos( radians( o.LONGITUDE) - radians(-122) ) + sin( radians(37) ) * sin( radians( o.LATITUDE) ) ) ) AS distance
from
www.employee as e
inner join
www.employee_location as eo
on e.id=eo.EMP_ID
inner join
www.location as o
on eo.LOC_ID=o.ID ) xxx
group by ID
order by mindistance;
