Java check if long lat in 1 km circle

Question

I'm working on a Java EE project an have and entity like this:

@Entity
public class Location {

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long messageId;

  @ManyToOne
  private User user;

  private String name;

  private Float latitude;

  private Float longitude;
}

And I need to filter these locations with a center point if they are in 1 km diameter circle.

I need a method like this returning only A, B, C, E locations.

public List<Location> findLocations(Float longitude, Float latitude) {
    List<Location> locations = 
            entityManager.createQuery("select l from Location where ???")
            .setParameter("longitude", longitude)
            .setParameter("latitude", latitude)
            .getResultList();
    return locations;
}

I found some code samples, but I must iterate over all locations on db (this will be really costed)

Can I do it directly with createQuery()?

Note: I'm using MySQL

Answer 1

assuming for a rough estimate for the latlong values are near the equator are the following

• One degree of longitude is roughly 111.32 km
• One degree of latitude is roughly 110.57 km

reference for values

from this we can assume that

• 1km of longtitude 1 is equal to 0.0089831deg
• 1km of latitude 1 is equal to 0.009044deg

we can eliminate the majority of the data by the adding the following to the Where of you sql statement

SELECT * FROM location WHERE ( latitude BETWEEN (latValue? - 0.009044) AND (LatValue?+0.009044)) AND (longtitude BETWEEN (longValue? - 0.0089831) AND (longValue?+0.0089831));

to get a more accurate result you still need to filter the resulting value with either Vincenty or Haversine formula.

Edit:

Or you could also implement Vincenty or Haversine formula directly to your query. now whether it is more efficient to do so, I haven't tested yet. I always keep my database transaction to a minimal. storing and retrieving data with very minimal computations.