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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With