How to query for a BigDecimal range in JPA

Question

So I made the decision to store latitude and longitude as BigDecimals

but now when I query for a value I want to allow a little bit of leeway so that if we query for two points metres apart we don't treat them as different (the points that we are specifying are always going to be at least 100m apart)

The below criteria query doesn't select anything even when I'm populating with known test data

public List<Location> findLocations(BigDecimal latitude, BigDecimal longitude) {
        BigDecmial geoPointDifferenceThreshold = new BigDecimal(0.0001).setScale(12, RoundingMode.HALF_EVEN);
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Location> cq =cb.createQuery(Location.class);

        Root<Location> locationRoot = cq.from(Location.class);
        Path<BigDecimal> latitudePath = locationRoot.<BigDecimal>get("latitude");
        Path<BigDecimal> longitudePath = locationRoot.<BigDecimal>get("longitude");

        Predicate latitudeBetweenRange = cb.between(latitudePath, latitude.subtract(geoPointDifferenceThreshold), latitude.add(geoPointDifferenceThreshold));
        Predicate longitudeBetweenRange = cb.between(longitudePath, longitude.subtract(geoPointDifferenceThreshold), longitude.add(geoPointDifferenceThreshold));

        cq.select(locationRoot).where(latitudeBetweenRange); //within 11m
        cq.select(locationRoot).where(longitudeBetweenRange); //within 11m
        return em.createQuery(cq).getResultList();
    }

I've no doubt the problem is between the chair and the keyboard. But I can't see what it is.

thanks in advance

The test environment is JPA2 with Hibernate on my Windows 7 dev machine running SQL 2008 Express R2

Answer 1

It seems that the best way to find a stupid error is to post a question on StackOverflow.

I was sure that there was test data available. But it turns out I had to be really sure because I wasn't actually adding any.

When there really, really is test data the above query works fine.

I'd be interested to see opinions on ways to improve the query as it is a bit verbose for my tastes but...