Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sequelize & postgis sort by distance from point

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

like image 365
Vitalii Kulyk Avatar asked Jan 03 '23 18:01

Vitalii Kulyk


1 Answers

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.

like image 143
mcranston18 Avatar answered Jan 05 '23 15:01

mcranston18