title: Spring Query: Haversine formula with pageable
body: <p>I'm trying to use the <a href="https://developers.google.com/maps/articles/phpsqlsearch_v3#findnearsql" rel="noreferrer">Haversine formula</a> to find entities near to a location in a <a href="http://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/Query.html" rel="noreferrer">Spring Data JPA Query</a> with <a href="http://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/domain/Pageable.html" rel="noreferrer">Pageable</a> but I dont get it done.</p>
  
  <p>My first approach looks like this</p>
  
  <pre><code>    @Query("SELECT m, (6371 * acos(cos(radians(:latitude)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:longitude)) + sin(radians(:latitude)) * sin(radians(m.latitude)))) as dist FROM Entity m WHERE dist &lt; :distance ORDER BY dist DESC")
      public List&lt;Entity&gt; findEntitiesByLocation(@Param("latitude") final double latitude, @Param("longitude") final double longitude, @Param("distance") final double distance, Pageable pageable);
  </code></pre>
  
  <p>but it fails as Spring/JPA seems to be not capable of using aliases in the where clause. The SQL in the stacktrace looks like this</p>
  
  <pre><code>select (...),(haversine) as col_1_0_ where dist&lt;? order by col_1_0_ DESC
  </code></pre>
  
  <p>so the alias in the where clause is not replaced. Using "col_1_0_" (without the ") instead of dist also does not work.</p>
  
  <p>According to this <a href="https://stackoverflow.com/a/153641/1089459">SO Answer</a>, at least MySQL is interpreted inside out and using aliases in the Where clause is not possible. A suggested solution would be using HAVING instead of WHERE, but also in a HAVING clause, the alias is not resolved.</p>
  
  <p>I know that I can move the Haversine Formula into the where clause, but I still need it in the Order By clause and I think it might slow down performance using the same long Haversine formula in the Order By clause as I select from a few hundred thousands entities.</p>
  
  <p>I then tried to create the Query manually but I don't know how to apply the Pageable to this:</p>
  
  <pre><code>    @Override
      public List&lt;Entity&gt; findEntitiesByLocation(final double latitude, final double longitude, final double distance, Pageable pageable) {
      final javax.persistence.Query query = this.entityManager.createQuery(SELECT_ENTITES_BY_DISTANCE);
  
      query.setParameter("latitude", latitude);
      query.setParameter("longitude", longitude);
      query.setParameter("distance", distance);
  
      final List&lt;Entity&gt; entities = query.getResultList();
      // order by distance
      entities .sort(new EntityDistanceComparator(latitude, longitude));
  
      return entities ;
  }
  </code></pre>
  
  <p>So, I either need the first approach with @Query to work (which I'd prefer) OR the second approach with Pageable</p>