序列化地理空间查询:找到"n".最接近某个位置的点 [英] Sequelize geospatial query: find "n" closest points to a location
问题描述
如果我想找到与"a"最接近的点,请使用序列化和地理空间查询.特定位置,Sequelize查询应如何?
Using Sequelize and geospatial queries, if I want to find the "n" closest points to a certain location, how should the Sequelize query be?
假设我有一个看起来像这样的模型:
Assume I have a model that looks something like this:
sequelize.define('Point', {geo: DataTypes.GEOMETRY('POINT')});
现在假设我们通过类似以下方式在数据库中输入100个随机点:
Now let's say we input 100 random points in the db through something like:
db.Point.create({geo: {type: 'Point', coordinates: [randomLng,randomLat]}});
想象一下,我们有一个lat
和lng
变量来定义一个位置,我们想找到最接近它的10个点.当我运行此查询时,我得到一个错误:
Imagine we have a lat
and lng
variables to define a location, and we want to find the 10 closest points to it. when I run this query I get an error:
const location = sequelize.literal(`ST_GeomFromText('POINT(${lat} ${lng})', 4326)`);
db.Point.findAll({
attributes: [['distance', sequelize.fn('ST_Distance', sequelize.col('Point'), location)]],
order: 'distance',
limit: 10
});
// -> TypeError: s.replace is not a function
任何想法是什么问题/如何解决?
Any idea what is the issue / how to fix it?
谢谢!
推荐答案
用括号将 sequelize.fn 包围时,还必须包括一个字符串作为别名:
When you surround sequelize.fn with brackets, you must also include a string as an alias:
[sequelize.fn('ST_Distance_Sphere', sequelize.literal('geolocation'), location), 'ALIASNAME']
此外,尝试将ST_Distance
更改为ST_Distance_Sphere
.所以:
Also, try changing ST_Distance
to ST_Distance_Sphere
. So:
const location = sequelize.literal(`ST_GeomFromText('POINT(${lng} ${lat})', 4326)`);
User.findAll({
attributes: [[sequelize.fn('ST_Distance_Sphere', sequelize.literal('geolocation'), location),'distance']],
order: 'distance',
limit: 10,
logging: console.log
})
.then(function(instance){
console.log(instance);
})
这实际上对我有用. obs:确保用具有几何数据类型的模型替换用户".
This is actually working for me. obs: be sure you substitute 'User' with the model in which you have the geometry data type.
更新:如果您仍然无法使用order: 'distance'
进行订购,也许您应该在var中声明它,并使用order: distance
不带引号,例如:
Update: If you still can't order using order: 'distance'
, maybe you should declare it in a var and use order: distance
without quotes, like this:
var lat = parseFloat(json.lat);
var lng = parseFloat(json.lng);
var attributes = Object.keys(User.attributes);
var location = sequelize.literal(`ST_GeomFromText('POINT(${lng} ${lat})')`);
var distance = sequelize.fn('ST_Distance_Sphere', sequelize.literal('geolocation'), location);
attributes.push([distance,'distance']);
var query = {
attributes: attributes,
order: distance,
include: {model: Address, as: 'address'},
where: sequelize.where(distance, {$lte: maxDistance}),
logging: console.log
}
距离精度更新:
sarikaya 提到的解决方案似乎更准确.这是使用postgres的方法:
The solution mentioned by sarikaya does seem to be more accurate. Here is how to do it using postgres:
var distance = sequelize.literal("6371 * acos(cos(radians("+lat+")) * cos(radians(ST_X(location))) * cos(radians("+lng+") - radians(ST_Y(location))) + sin(radians("+lat+")) * sin(radians(ST_X(location))))");
这篇关于序列化地理空间查询:找到"n".最接近某个位置的点的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!