CriteriaBuilder radius calculation

Question

I am trying to calculate given lat/long with a distance, and only returning the rows that fall in that circular distance (Database has lat / long coordinates aswell). What I am unaware of is how I would do that. I am currently using Native SQL and have plain text with the haversine formula that calculates this, and if I am correct it is not possible to do the haversine formula with the CriteriaBuilder. However I want to have the same results using the CriteriaBuilder. I've tried using the hibernate-spatial dependency, but I am not getting it to work as I want it to. I have also followed tutorials such as this one.

https://docs.jboss.org/hibernate/search/4.2/reference/en-US/html/spatial.html

The database I am using is MySQL.
Hibernate version 4.3.10.

Also this tutorial didn't get me far Using JPA Criteria Api and hibernate spatial 4 together

So how would I build a CriteriaBuilder query with MySQL with a given lat / long and distance and retrieve only rows that fall in that area, comparing them to the lat / long coordinates stored in the database.

Quote of the Week: *I have searched the enitre internet without finding my solution.* — shmosel, May 15 '16 at 05:14
Do you use `MySQLSpatialDialect`? You may find answer here: http://stackoverflow.com/questions/36619855/mysql-spatial-dialect — Ernest Sadykov, May 17 '16 at 17:16
This would be much easier and more efficient to do with PostGIS. — Jakub Bibro, May 17 '16 at 20:38

Nikson Kanti Paul · Answer 1 · 2016-05-18T03:43:21.330

3

I have tried the following and it works.

@Query(value = "SELECT  s FROM Address s WHERE 1 = 1 AND " +
        "(pow(69.1 * (s.latitude - ?1), 2) + pow(69.1 * (?2 - s.longitude) * cos(s.latitude / 57.3), 2)) < pow(?3,2)" 

)
List<Address> findNearbyAddress(double lat, double lng, double radius);

radius in miles. ?1, ?2, ?3 are parameters placeholder.
Its need some calculation to measure the distance from latitude & longitude. You can't call database native function from CriteriaBuilder.

edited May 18 '16 at 03:43

answered May 18 '16 at 03:36

Nikson Kanti Paul

3,394
1
35
51

1

I have a similar solution but this is not do-able with the criteria builder. – Mees Kluivers May 18 '16 at 08:47

score 2 · Accepted Answer · answered May 18 '16 at 09:18

2

CriteriaBuilder is not powerful enough for such complex queries. But you can do it anyway with this trick:

Create a MySQL FUNCTION
Call this FUNCTION by using CriteriaBuilder#function(..)

It should by also pretty fast. Whereby I have to note, that mysql is not really suitable for such use case like distance calculation.

answered May 18 '16 at 09:18

alex

8,904
6
49
75

Hmm.. didn't know about this. I'll look into it thanks – Mees Kluivers May 18 '16 at 10:02

score 0 · Answer 3 · edited May 23 '17 at 12:02

I have done this in the past by querying out the rectangle as suggested above, and then doing the distance calculation on those results. Since you should have already eliminated a good chunk of the data, doing those calculations is less of a penalty. Since this is just numeric range queries it doesn't require any weird plugins or integrations either and should get results fairly quickly if you index the lat and long columns.

see: Given GPS coordinates, how do I find nearby landmarks or points-of-interest?

This site details doing the Haversine formula in SQL. I would still consider applying the bounding box as a where clause though so that the DB doesn't do that calculation for every row in the table. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

CriteriaBuilder radius calculation

3 Answers3