topic Re: Querying document schema with Math function in Nuxeo Forum

Querying document schema with Math function

Artmoni_Mobile — Mon, 25 Aug 2014 15:06:23 GMT

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

Re: Querying document schema with Math function

Alain_ESCAFFRE — Mon, 25 Aug 2014 15:17:45 GMT

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-geodistance-aggregation.html

This will be part of the 6.0 release.

Re: Querying document schema with Math function

Artmoni_Mobile — Thu, 04 Sep 2014 13:51:14 GMT

Thanks Alain, I will test this new feature