Get nearest position with Haversine Formula in Spring

Question

I have an entitiy which has two columns in the database like this:

@Column(name = "latitude", nullable = false)
private float currentPositionLatitude;

@Column(name = "longitude", nullable = false)
private float currentPositionLongitude;

And I have multiple entrys in my database(PostgresQl) for the entity with different latitudes and longitudes for all of them. Now I want to give a point (lat,lon) and a radius and show all entitys which are near the point. I first tried it with the Harversine formula like this in Spring Jpa :

   String HAVERSINE_PART = "(6371 * acos(cos(radians(:latitude)) * cos(radians(s.latitude)) *" +
        " cos(radians(s.longitude) - radians(:longitude)) + sin(radians(:latitude)) * sin(radians(s.latitude))))";

    @Query("SELECT currentPositionLatitude,currentPositionLongitude FROM Entity WHERE  '%:HAVERSINE_PART%' < :distance ORDER BY '%:HAVERSINE_PART%' DESC")
      public List<Entity> findEntiryWithLocation(
      @Param("currentPositionLatitude") final float latitude,
      @Param("currentPositionLongitude") final float longitude, @Param("distance") final double distance )

this didnt work so I tried:

    @Query("SELECT s FROM Entity s WHERE " + HAVERSINE_PART + " < :distance ORDER BY "+ HAVERSINE_PART + " DESC")
List<Entity> findEntityWithInDistance(
@Param("latitude") double latitude,
 @Param("longitude") double longitude,
 @Param("distance") double distanceWithInKM );

this also didnt work so then I tried:

@Query(value = "SELECT *, earth_distance(ll_to_earth(:latitude, :longitude), ll_to_earth(latitude, longitude)) as distance FROM Car WHERE  (earth_box(ll_to_earth(:latitude, :longitude), :radiusInMeters) @> ll_to_earth(latitude, longitude))ORDER BY distance ASC",
nativeQuery = true)
List<Entity> getPlacesNear ( @Param("latitude") float latitude,
                             @Param("longitude")float longitude,
                             @Param("radiusInMeters") float radiusInMeters);

This compiled but when I tried to put in values in my controller I got the error: jdbc error :2022 no dialect mapping

Here my methods for the service and the controller(the datatypes have changed maybe):

public List<Entity>showNearestEntityByDistances(float latitude, float longitude, float distance){
        return EntityRepository.getPlacesNear(latitude,longitude,distance);
    }

and the endpoint:

@GetMapping("/location")
public List<Entity>showEntityNearby(@RequestParam float latitude,@RequestParam float longitude,@RequestParam float distance){
  

    return carService.showNearestEntitysByDistances(latitude,longitude,distance);
}

I already looked at : https://www.postgresql.org/docs/current/earthdistance.html and postgresql jpa earth_distance query

but it didnt worked. Has anyone an idea what Im doing wrong ? Thanks in regard.

GreyBeardedGeek · Answer 1 · 2022-02-09T01:40:17.363

The following worked for me:

first, run:

 create extension cube;

 create extension earthdistance;

This installs extensions that enable, among other things, location-based queries without installing PostGIS

Then, Instead of storing your lat and long in separate columns, store them together in a 'point' type column, keeping in mind that the longitude comes first, not the latitude as you would expect, e.g.

  create table Entity (city varchar(100), location point);

and store your data, e.g.

insert into places(name, location) values ('lambertville', point((74.9429,40.3659)));
    
insert into places(name, location) values ('ny', point(74.0060, 40.7128));
    
insert into places(name, location) values ('phila', point( 75.1652, 39.9526));

(note that Lambertville, NJ is somewhere around half-way between Philly and New York).

Then, if, for example, you want to select Entities that are less than a certain distance from another entity, you can do something like:

select * from places 
 where 
location <@> (select location from places where name = 'ny') < 70  
  and name != 'ny';

or, if you have the coordinates already, and want to find entities withing a certain distance,

select * from places 
 where 
location <@> point(74.0060, 40.7128) < 70    
  and name != 'ny';

I have not checked whether these queries can be adapted to work with JPA @Query annotations, YMMV.

@Query( "FROM Entity m where function('cube_contains', function('earth_box', function('ll_to_earth',:#{#latitude},:#{#longitude},:#{#distance}), function ('ll_to_earth',m.latitude,m.longitude)))=TRUE ") List getPlacesNear(@Param("latitude") double latitude, @Param("longitude") double longitude, @Param("distance") double distance); — Okabe, Feb 09 '22 at 11:01
It compiles but when I search for entities it wont show me any — Okabe, Feb 09 '22 at 11:02

score 1 · Accepted Answer · answered Feb 09 '22 at 21:20

I got it working changing a few things:

 @Query(value = "SELECT * FROM Entity s WHERE " + HAVERSINE_PART + " < :distance ORDER BY "+ HAVERSINE_PART + " DESC",nativeQuery = true)
List<Entity> findEntityWithInDistance(
@Param("latitude") double latitude,
 @Param("longitude") double longitude,
 @Param("distance") double distanceWithInKM );

Get nearest position with Haversine Formula in Spring

2 Answers2