Querying document schema with Math function

[Artmoni_Mobile]

Hi,

I store GPS latitude and longitude in document schema and would like to query my document database and filter on the nearest points.

A sample standard SQL query could looks like that:

SELECT geo_latitude, geo_longitude, geo_name, geo_country_code AS country, 
  (DEGREES(
    ACOS(
      SIN(RADIANS(47.470779)) * SIN(RADIANS(geo_latitude)) + 
      COS(RADIANS(47.470779)) * COS(RADIANS(geo_latitude)) * 
      COS(RADIANS(-87.890699 - geo_longitude))
    ) 
  ) * 60 * 1.1515) 
AS distance FROM `MyDatabase`.`allCountries` ORDER BY distance ASC LIMIT 20 ;

Is there a way in Nuxeo to execute Math operation in NXQL or requesting postgresql database directly?

Another solution could be to create a custom OperationChain and execute the Math operation in Java.

Thanks in advance

[Alain_ESCAFFRE · Accepted solution]

A few aggregates functions where added in NXQL with https://jira.nuxeo.com/browse/NXP-13708 but not advanced one like yours.

Actually, for queries on geographic areas, the most promising is the integration of Elasticsearch aggregation support that is being done. See the epic https://jira.nuxeo.com/browse/NXP-14882

More specifically, geo-distance will also be http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-aggregations-bucket-geo...

This will be part of the 6.0 release.

[Artmoni_Mobile]

Thanks Alain, I will test this new feature