Java JPA Order By with GPS Coordinates
<p>I am using Java 8 with JPA and Hibernate 5.2.1 and MySQL database.</p>

<p>I have a table with users GPS coordinates:</p>

<p><em>Row 1:</em></p>

<pre><code>LAT_1: 28.85, LONG_1: -25.766667
LAT_2: 27.15, LONG_2: -26.133333
</code></pre>

<p><em>Row 2:</em></p>

<pre><code>LAT_1: 27.25, LONG_1: -26.234567
LAT_2: 28.98, LONG_2: -25.456666
</code></pre>

<p>Then I have a <em>user selected</em> value of coordinates too:</p>

<pre><code>LAT_USER: 27.22, LONG_USER: -26.89998
</code></pre>

<p>I need the result set in from the database query to return rows <strong>ordered by distance from the closest GPS point</strong> (asc).</p>

<p>In this example, the closest GPS point to <code>LAT_USER &amp; LONG_USER</code> will be:</p>

<p>For <em>Row 1</em>:</p>

<pre><code> LAT_2 &amp; LONG_2
</code></pre>

<p>For <em>Row 2</em>:</p>

<pre><code> LAT_1 &amp; LONG_1
</code></pre>

<p>Is this possible?</p>

<p>Here is my base code so far:</p>

<pre><code>    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery&lt;T&gt; criteriaQuery = criteriaBuilder.createQuery(entityClass);

    // from
    Root&lt;T&gt; from = criteriaQuery.from(entityClass);
    criteriaQuery.select(from);

    // order by
    List&lt;Order&gt; order = new ArrayList&lt;Order&gt;();
    // ????
    criteriaQuery.orderBy(order);

    List&lt;T&gt; results = (List&lt;T&gt;) entityManager.createQuery(criteriaQuery).setFirstResult(firstResult)
            .setMaxResults(maxResults).getResultList();
    return results;
</code></pre>

<p>I would appreciate anyone's help. </p>

<p>Thanks</p>

<p><strong>UPDATE:</strong></p>

<p>My table structure is as follows (I have other join tables, but this is for simplicity sake):</p>

<pre><code> ----------     -------------------    -----------
| EMPLOYEE |   | EMPLOYEE_LOCATION |  | LOCATION  |
 ----------     -------------------    -----------
|ID        |   |EMP_ID             |  | ID        |
|          |   |LOC_ID             |  | LATITUDE  |
|          |   |                   |  | LONGITUDE |
 ----------     -------------------    -----------
</code></pre>

<p>My <strong>Employee</strong> Model Object has <strong>locations</strong>:</p>

<pre><code>@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&lt;Location&gt; locations;
</code></pre>

<p>I understand to achieve what I need, I need to do the <a href="https://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula">Haversine formula</a> calculation on the database because I need to order the result set with the calculations result. </p>

<p>I know this will result in a pretty complicated query because <strong>it needs to find the lowest distance, and order by that</strong>.</p>

<pre><code>    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
</code></pre>

<p>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.</p>

<p>Does anyone know how I could preferably keep this in JPA objects and add custom SQL for the <a href="https://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula">Haversine formula</a> (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.</p>

<p>UPDATE</p>

<p>I will use something like this:</p>

<pre><code>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; 
</code></pre>