This module extends the knex query builder with selectDistance method, the method adds a virtual column to the current query (qb) with the alias as, in which resides the calculated distance from the geoPoint to the entity.location column. It also accepts some options parameters like the unit (km | mi) and the decimalPrecision of the result. Deps…

select_distance_knex.js

var knex;

/**
 * This module extends the knex query builder with selectDistance method,
 * The method adds a virtual column to the current query (qb) with the alias as, in which
 * resides the calculated distance from the geoPoint to the entity.location column.
 * It also accepts some options parameters like the unit (km | mi) and the decimalPrecision of the result.
 * Deps: Postgis

 * @example
 *   qb = knex('table_b AS b');
 *   qb.selectDistance('b.location', function() {
 *     this.geoPoint({lat: ..., lng: ...});
 *     this.unit('mi');
 *     this.decimalPrecision(2);
 *     this.as('distance');
 *   });
 *
 * @param {knex} knexModel
 */
module.exports = function(knexModel) {
  knex = knexModel;

  knex.client.QueryBuilder.prototype.selectDistance = function(column, scope) {
    var knexDistance = new KnexDistance(column);

    // setters by the client knex
    scope.call(knexDistance);

    knexDistance.buildSelectGeoDistance(this);

    return this;
  }
};

var unitConversorMap = {
  mi: 0.000621371,
  
  km: 0.001
};

function KnexDistance(column) {
  if (column) {
    this.setFromColumn(column);
  }
}

KnexDistance.prototype.setFromColumn = function(columnName) {
  this._fromColumn = columnName;
};

KnexDistance.prototype.getFromColumn = function() {
  return this._fromColumn || 'location';
};

KnexDistance.prototype.geoPoint = function(point) {
  this._geoPoint = point;
};

KnexDistance.prototype.getGeoPoint = function() {
  return this._geoPoint;
};

KnexDistance.prototype.unit = function(unit) {
  this._unit = unit;
};

KnexDistance.prototype.getUnit = function() {
  return this._unit || 'mi';
};

KnexDistance.prototype.decimalPrecision = function(decimalPrecision) {
  this._decimalPrecision = decimalPrecision;
};

KnexDistance.prototype.getDecimalPrecision = function() {
  return this._decimalPrecision || 2;
};

KnexDistance.prototype.as = function(aliasResult) {
  this._as = aliasResult;
};

KnexDistance.prototype.getAs = function() {
  return this._as || 'distance';
};

KnexDistance.prototype.buildSelectGeoDistance = function(qb) {
  var geoPoint = this.getGeoPoint();
  var sql = getDistanceQuery.call(this);

  qb
    .select(knex.raw(sql, [geoPoint.lng, geoPoint.lat]));
};

function getDistanceQuery() {
  var column = this.getFromColumn();
  var sqlDistance = 'ST_Distance_sphere(st_makepoint({column}[0], {column}[1]), ' +
    'st_makepoint(?, ?))';

  sqlDistance = setUnit.call(this, sqlDistance);
  sqlDistance = setAliasResult.call(this, sqlDistance);

  return sqlDistance.replace(/{column}/g, column || 'location');
}

function setUnit(sqlStr) {
  var unit = this.getUnit();
  var formatedUnit = [sqlStr, '*', unitConversorMap[unit]].join(' ');

  return setDecimalPlaces.call(this, formatedUnit);
}

function setDecimalPlaces(sqlStr) {
  var decimalPlaces = this.getDecimalPrecision();

  return ['trunc(cast( ' + sqlStr + ' as numeric),', decimalPlaces, '):: FLOAT'].join(' ');
}

function setAliasResult(sqlStr) {
  return [sqlStr, 'AS', this.getAs()].join(' ');
}