<-My Blog->

In this article, we gonna learn how to find the nearest location using latitude and longitude within a radius using sequelize orm and postgres sql.

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: 'postgres',
    operatorsAliases: 'false',
    logging: false
});  
 
module.exports = sequelize;

const sequelize = require('../models/connection');
const User = require('../models/user.model');
const { Op } = require('sequelize');

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: {
        [Op.and]: [
            sequelize.where(sequelize.literal(haversine), '<=', distance),
            { status: true }
        ]
    },
    order: sequelize.col('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: {
        [Op.and]: [
            sequelize.where(sequelize.literal(haversine), '<=', distance),
            { status: true }
        ]
    },
    order: sequelize.col('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
    },
    longitude: {
        type: DataTypes.FLOAT
    },
    status: {
        type: DataTypes.BOOLEAN 
    }
}, {
    underscored: true
}); 

module.exports = User;

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'],
        ],
        where: sequelize.where(sequelize.literal(haversine), '<=', 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: true
    },
    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: true
    },
    order: sequelize.col('distance'),
    limit: 5
});

Checkout my full sequelize-geo-postgres example.

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

MySQL Solution

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

Thanks.