<-My Blog->

Sometimes you need to find the nearest location using latitude and longitude within a radius.

In this article, we gonna learn how to use sequelize orm for finding nearby locations.

DB Structure - users table

models/connection.js

const { Sequelize } = require('sequelize');
const config = require('../config/database'); 

const sequelize = new Sequelize(config.database, config.username, config.password, {
    host: config.host,
    dialect: 'mysql',
    operatorsAliases: 'false',
    logging: false
});  

module.exports = sequelize;

const sequelize = require("../models/connection");
const User = require("../models/user.model");

Here is an example for finding nearby 5 users within 1 km.

const latitude = 28.626137;
const longitude = 79.821602;
const distance = 1;

const haversine = `(
    6371 * acos(
        cos(radians(${latitude}))
        * cos(radians(latitude))
        * cos(radians(longitude) - radians(${longitude}))
        + sin(radians(${latitude})) * sin(radians(latitude))
    )
)`;

const users = await User.findAll({
    attributes: [
        'id',
        [sequelize.literal(haversine), 'distance'],
    ],
    where: {
        status: 1
    },
    order: sequelize.col('distance'),
    having: sequelize.literal(`distance <= ${distance}`),
    limit: 5
});

Note:- In case of miles just replace the 6371 with 3959 in the haversine formula.

Miles - 3959 

Kilometer - 6371

you can also round the distance in query. 

const users = await User.findAll({
    attributes: [
        'id',
        [sequelize.literal(`round(${haversine}, 2)`), 'distance'],
    ],
    where: {
        status: 1
    },
    order: sequelize.col('distance'),
    having: sequelize.literal(`distance <= ${distance}`),
    limit: 5
});

We can also make it reusable by defining this as a scope inside the model.

models/user.model.js

const { DataTypes } = require('sequelize');
const sequelize = require('./connection');

const User = sequelize.define('User', {
    name: {
        type: DataTypes.STRING 
    },
    email: {
        type: DataTypes.STRING 
    }, 
    latitude: {
        type: DataTypes.FLOAT(10, 6) 
    },
    longitude: {
        type: DataTypes.FLOAT(10, 6) 
    },
    status: {
        type: DataTypes.TINYINT(2)
    }
}, {
    underscored: true
}); 

User.addScope('distance', (latitude, longitude, distance, unit = "km") => {
    const constant = unit == "km" ? 6371 : 3959;
    const haversine = `(
        ${constant} * acos(
            cos(radians(${latitude}))
            * cos(radians(latitude))
            * cos(radians(longitude) - radians(${longitude}))
            + sin(radians(${latitude})) * sin(radians(latitude))
        )
    )`;
    return {
        attributes: [ 
            [sequelize.literal(haversine), 'distance'],
        ],
        having: sequelize.literal(`distance <= ${distance}`)
    }
}) 
 

module.exports = User;

Note:- I have passed km as the default unit. so you don't need to pass in case of kilometer calculation.

Now in the controller, you can call this scope.

const users = await User.scope({ 
    method: ['distance', latitude, longitude, distance] 
})
.findAll({
    attributes: [
        'id'
    ],
    where: {
        status: 1
    },
    order: sequelize.col('distance'),
    limit: 5
});

In the case of miles.

const users = await User.scope({ 
    method: ['distance', latitude, longitude, distance, 'm'] 
})
.findAll({
    attributes: [
        'id'
    ],
    where: {
        status: 1
    },
    order: sequelize.col('distance'),
    limit: 5
});

Checkout my full sequelize-geo example.

https://github.com/ultimateakash/sequelize-geo

Postgres SQL Solution

If you facing any issues. don't hesitate to comment below. I will be happy to help you.

Thanks.