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

user_570016 0
Viewed 10282

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!

sequelize.js postgis geospatial postgresql mysql
3 Answers

MySQL can give an error that function ST_Distance_Sphere does not exist. In that case you can use this alternative solution:

I hold point information separately as latitude and longitude decimals. Assume you should have a model that looks something like this:

sequelize.define('Point', {latitude: DataTypes.DECIMAL(11,2)},
                          {longitude: DataTypes.DECIMAL(11,2)});

Imagine we have a lat and lng variables to define a location, and we want to find the 10 closest points to it:

db.Point.findAll({
  attributes: [[sequelize.fn('POW',sequelize.fn('ABS',sequelize.literal("latitude-"+lat)),2),'x1'],
               [sequelize.fn('POW',sequelize.fn('ABS',sequelize.literal("longitude-"+lng)),2),'x2']], 
  order: sequelize.fn('SQRT', sequelize.literal('x1+x2')),
  limit: 10
});

Update:

With Haversine Formula, distance is more accurate:

db.Point.findAll({
  attributes: [[sequelize.literal("6371 * acos(cos(radians("+lat+")) * cos(radians(latitude)) * cos(radians("+lng+") - radians(longitude)) + sin(radians("+lat+")) * sin(radians(latitude)))"),'distance']],
  order: sequelize.col('distance'),
  limit: 10
});
user_23637030
Related