• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Java JPA Order By with GPS Coordinates

 
RichardH Marais
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

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:



Row 2:



Then I have a user selected value of coordinates too:


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 will be:

For *Row 1*:


For *Row 2*:


Is this possible?

Here is my base code so far:



I would appreciate anyone's help.

Thanks
 
RichardH Marais
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is the Haversine formula. I just need to modify my SQL to order my the closest coordinate now.

UPDATE:

I have the following SQL so far, but it's obviously incorrect. I am no expert with SQL, so if someone could please assist, I would appreciate it.

 
RichardH Marais
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
UPDATE:

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



My Employee Model Object has 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.



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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic