mysql - Sequelize geospatial query: find "n" closest points to a location

Question

Using Sequelize and geospatial queries, if I want to find the "n" closest points to a certain location, how should the Sequelize query be?

Assume I have a model that looks something like this:

sequelize.define('Point', {geo: DataTypes.GEOMETRY('POINT')});

Now let's say we input 100 random points in the db through something like:

db.Point.create({geo: {type: 'Point', coordinates: [randomLng,randomLat]}});

Imagine we have a lat and lng variables to define a location, and we want to find the 10 closest points to it. when I run this query I get an error:

const location = sequelize.literal(`ST_GeomFromText('POINT(${lat} ${lng})', 4326)`);

db.Point.findAll({
  attributes: [['distance', sequelize.fn('ST_Distance', sequelize.col('Point'), location)]],
  order: 'distance',
  limit: 10
});

// -> TypeError: s.replace is not a function

Any idea what is the issue / how to fix it?

Thx!

Answer 1

When you surround sequelize.fn with brackets, you must also include a string as an alias:

[sequelize.fn('ST_Distance_Sphere', sequelize.literal('geolocation'), location), 'ALIASNAME']

Also, try changing ST_Distance to ST_Distance_Sphere. So:

    const location = sequelize.literal(`ST_GeomFromText('POINT(${lng} ${lat})', 4326)`);

    User.findAll({
      attributes: [[sequelize.fn('ST_Distance_Sphere', sequelize.literal('geolocation'), location),'distance']],
      order: 'distance',
      limit: 10,
      logging: console.log
    })
    .then(function(instance){
      console.log(instance);
    })

This is actually working for me. obs: be sure you substitute 'User' with the model in which you have the geometry data type.

Update: If you still can't order using order: 'distance', maybe you should declare it in a var and use order: distance without quotes, like this:

    var lat = parseFloat(json.lat);
    var lng = parseFloat(json.lng);
    var attributes = Object.keys(User.attributes);

    var location = sequelize.literal(`ST_GeomFromText('POINT(${lng} ${lat})')`);
    var distance = sequelize.fn('ST_Distance_Sphere', sequelize.literal('geolocation'), location);
    attributes.push([distance,'distance']);

    var query = {
      attributes: attributes,
      order: distance,
      include: {model: Address, as: 'address'},
      where: sequelize.where(distance, {$lte: maxDistance}),
      logging: console.log
    }

Update on distance accuracy:

The solution mentioned by sarikaya does seem to be more accurate. Here is how to do it using postgres:

var distance = sequelize.literal("6371 * acos(cos(radians("+lat+")) * cos(radians(ST_X(location))) * cos(radians("+lng+") - radians(ST_Y(location))) + sin(radians("+lat+")) * sin(radians(ST_X(location))))");