1

I'm currently working on a web app that will rate neighbourhoods on a number of different criteria, one of which being statistics from the local Garda station (police station in Irish). I'm passing a latitude and longitude value from the client side into a controller in Spring and I want to run the following SQL query so I can find the nearest police station and return the relevant statistics.

Set @lat = x; // Local Variable for a latitude value
Set @lng = y; // Local Variable for a longitude value

SELECT * FROM garda_station
ORDER BY ((garda_station_latitude - @lat)*
         (garda_station_latitude - @lat))+
         ((garda_station_longitude - @lng)*
         (garda_station_longitude - @lng))
ASC LIMIT 1;

The garda_station table I'm trying to access is structured as follows

+-------------------------+--------------+------+-----+---------+----------------+
| Field                   | Type         | Null | Key | Default | Extra          |
+-------------------------+--------------+------+-----+---------+----------------+
| garda_station_id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| garda_station_latitude  | double       | NO   |     | NULL    |                |
| garda_station_longitude | double       | NO   |     | NULL    |                |
| garda_station_name      | varchar(255) | YES  |     | NULL    |                |
+-------------------------+--------------+------+-----+---------+----------------+

I'm passing the latitude & longitude values from the client side via an AJAX request to a Controller class in my Spring project.

@RequestMapping(value = "/results", method = RequestMethod.POST)
public @ResponseBody
String Submit(@RequestParam("latitude") String latitude,@RequestParam("longitude") String longitude) {
    // I'm printing them for now to ensure they have been sent from the client side
    System.out.println(latitude + "" + longitude);
    return "/";
}

This is my first big attempt at a spring project so if there were any other suggestions as to how I could conduct this SQL query I'd love to heat your suggestions.

Charles
  • 69
  • 11

1 Answers1

1

The simplest way is to use JDBCTemplate (see here and here). But actually it would be better to define a stored procedure to calculate disctance and use the procedure (see here and here)

StanislavL
  • 56,971
  • 9
  • 68
  • 98
  • Thanks for the reply. What would your opinion be of using a JPA query method through the Repository class, something like this, `public GardaStation findByGardaStation_LatitudeAndGardaStation_Longitude(double latitude, double longitude);`, with the possibility of a customised query to this if possible? – Charles Jan 23 '18 at 13:16
  • That's fine for me. Originally you asked without JPA/Spring data. That's why I suggested JDBCTemplate based approach. You actual query is not perfect. It's better to use more complicated formula. In opposite case result precision strongly depends on latitude. – StanislavL Jan 23 '18 at 13:34
  • Apologies I should've specified I meant JPA/Spring data. Would you know of a resource I could look at that would help me to create a customised JPA Query based on an SQL statement? – Charles Jan 23 '18 at 13:40
  • Check https://stackoverflow.com/questions/29576674/spring-query-haversine-formula-with-pageable and https://stackoverflow.com/questions/42966967/creating-a-custom-query-with-spring-data-jpa – StanislavL Jan 23 '18 at 13:47
  • Thank you. Top answer for your help. – Charles Jan 23 '18 at 13:49