sequelize & postgis sort by distance from point

Question

I have a problem with search including sort by distance from some point. Here is my code and what I'm trying to do. Thanks for help

const Sequelize = require('sequelize');

var Flat = db.define('flat', {
    id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        primaryKey: true
    }
});

var FlatAddress = db.define('flat_address', {
    id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        primaryKey: true
    },
    flat_id: {
        type: Sequelize.INTEGER,
        foreignKey:true,
        allowNull:false,
        references: {
            model:'flats',
            key: 'id'
        }
    },
    city: {
        type: Sequelize.STRING(50) //post_town
    },
    location: {
        type: Sequelize.GEOMETRY('POINT')
    }
});

Flat.hasOne(FlatAddress, { as: 'Address', foreignKey: 'flat_id', otherKey: 'id', onDelete: 'cascade' });

FlatAddress.belongsTo(Flat, { foreignKey: 'id', otherKey: 'flat_id', onDelete: 'cascade' });

and i want to do something like this

var POINT = {lat, lng} ?? 
Flats.findAndCountAll({
        where: filter,
        order:  [
                [ { model: FlatAddresses, as: 'Address' },
 '//here should be something like distance from POINT//', 'ACS']
        ],
        include: [
            { model: FlatAddresses, as: 'Address'}
        ],
        offset,
        limit
    })

I didn't find examples or docs for my case. thanks

mcranston18 mcranston18 · Accepted Answer · 2018-02-27T15:17:16

The following statement will find Flats within your latitude and longitude, include a field called distance in the payload, and order it by distance.

const myDistance = 10000; // e.g. 10 kilometres
Flats.findAll({
  attributes: {
    include: [
      [
        Sequelize.fn(
          'ST_Distance',
          Sequelize.col('location'),
          Sequelize.fn('ST_MakePoint', longitude, latitude)
        ),
        'distance'
      ]
    ]
  },
  where: Sequelize.where(
    Sequelize.fn(
      'ST_DWithin',
      Sequelize.col('location'),
      Sequelize.fn('ST_MakePoint', longitude, latitude),
      myDistance
    ),
    true
  ),
  order: Sequelize.literal('distance ASC')
});

Please keep in mind: I do not know which SRID you are using. Therefore, I'm using metres to measure distance though you may have to convert metres to radius in your code.

sequelize & postgis sort by distance from point

1 Answers