按距点的距离排序的序列化邮政编码(&A) [英] sequelize & postgis sort by distance from point

问题描述

我在搜索时遇到了一个问题，包括从某个点按距离排序。以下是我的代码和我正在尝试做的事情。感谢您的帮助

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' });

我想做这样的事情

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
    })

我找不到适用于我的案例的示例或文档。谢谢

推荐答案

以下语句将在您的经纬度范围内查找Flats，在负载中包含一个名为distance的字段，并按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')
});

请记住：我不知道您使用的是哪个SRID。因此，我使用米来测量距离，尽管您可能需要在代码中将米转换为半径。

这篇关于按距点的距离排序的序列化邮政编码(&A)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！