2

I'm using Spring Data JPA to map entities and tables.

In my PostgreSQL DB, I have a table called declared_location, that stores locations with long and lat. I wrote a sql native query with postgre specific syntax in order to retrieve a list of declared_location, ordered by the relative distance to a given point : 

  @Query(value="select *, 1.60934 * (point (:longRef, :latRef) <@> point (longitude, latitude)) as distance from declared_location order by distance", nativeQuery = true)
    List<DeclaredLocation> findAllSortedByDistance(@Param("longRef") double longRef, @Param("latRef") double latRef);

I'd like to map the distance calculated by Postgre (column "distance") to a field of my entity. That's where i'm stuck. Here is my entity: 

@Entity
public class DeclaredLocation {

    @Id
    @Type(type = "uuid-char")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private UUID id;

    private double latitude;

    private double longitude;

    private String address;

    @Column
    private Double relativeDistanceKm;

(...)
}

How should I tell Spring Data JPA to map the column distance created by postgre as a result of the native query to the field relativeDistanceKm of the entity? What is the right approach to do this?

Julien Berthoud
  • 721
  • 8
  • 24

1 Answers1

2

You can use hibernate @Formula for this purpose.

@Entity
public class DeclaredLocation {
   // ...

   @Formula(value = "1.60934 * (point (13,52) <@> point (longitude, latitude))")
   private Double relativeDistanceKm;
}
SternK
  • 11,649
  • 22
  • 32
  • 46
  • Thanks, but actually I don't want to calculate the distance from a fix point, instead I'd like to pass the long and lat of the changing reference point as parameters of the query. (see my edit above). I didn't mention it in my initial post to avoid overcomplicating my question. In this scenario, i'm not sure that @Formula is helpful. – Julien Berthoud Feb 17 '20 at 14:18