Geospatial distance calculator using Sequelize & MySQL

Question

I have a Location model in Sequelize

var Sequelize = require('sequelize');

module.exports = function(sequelize, DataTypes) {
   var Location = sequelize.define('Location', {
      location_id: {
         type: Sequelize.STRING,
         primaryKey: true
      },
      location_code: {
         type: Sequelize.STRING,
         allowNull: true
      },
      location_name: {
         type: Sequelize.STRING,
         allowNull: true
      },
      latitude: {
         type: Sequelize.STRING,
         allowNull: true,
         defaultValue: null
      },
      longitude: {
         type: Sequelize.STRING,
         allowNull: true,
         defaultValue: null
      },
      location: {
         type: Sequelize.GEOMETRY('POINT'),
         allowNull: true
      }
   });
   return Location;
};

I insert latitude and longitude into location by feeding it

var location = { type: 'Point', coordinates: [latitude, longitude] };

I have a router which gets latitude and longitude as parameters. I need to find out the distance between the provided lat/long and all the locations in the Locations table.

How can I go about doing it? Does Sequelize already provide me with a function to find out the distance or do I need to write an external query to find it?

Thanks.

Answer 1

You don't need latitude and longitude attributes, they will be stored inside you location attribute. For finding places in a distance lesser than 10000 meters, you can use the following:

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

    Location.findAll({
      attributes: attributes,
      order: 'distance',
      where: sequelize.where(distance, {$lte: 10000}),
      logging: console.log
    })
    .then(function(instance){
      return res.json(200, instance);
    })

Answer 2

 const { lat, lng, distance } = req.body.filter;

    Auction.findAndCountAll({
              include: [
                {
                  model: Property,
                  attributes: [
                    ['id', 'propertyID'],
                    ..............
                  ],
                  //lat lng and distance from angument
                  where: Auction.sequelize.and(
                    lat && lng && distance ? sequelize.where(
                      sequelize.literal(`6371 * acos(cos(radians(${lat})) * cos(radians(latitude)) * cos(radians(${lng}) - radians(longitude)) + sin(radians(${lat})) * sin(radians(latitude)))`),
                      '<=',
                      distance,
                    ) : null ,
                    {
                      status: 1,
                    }
                  ),

             //your code and logic
             .........
            }).then((result) => {
              ..........
            }).catch((err) => {
              ..........
            });