javamysqljpapersistencecreatequery

Java check if long lat in 1 km circle

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.

enter image description here

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

Solution

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

    reference for values

    from this we can assume that

    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.