我在MongoDB中有一个巨大的机器位置数据集，我使用的是node.js.我想查询每小时的值(每小时)

Question

数据如下所示。我需要计算用户行驶的距离。我做到了，可以指定一个日期范围，它显示了结果。但现在我需要计算每小时的数值。用户每一小时。

{
"_id" : ObjectId("576"),
"Date" : ISODate("2016-06-16T09:54:29.379Z"),
"MessageType" : "GPGGA",
"Location" : {
"type" : "Point",
"coordinates" : [ 
11.20, 
48.83
]
},
"Quality" : "Rtk",
"TimeSinceLastDgpsUpdate" : "09:57:27.2000000",
"Username" : "xyz"
}

Answer 1

首先生成Date的小时值。然后计算这些小时和用户的距离。

function haversine() {
var radians = Array.prototype.map.call(arguments, function (deg) { return deg / 180.0 * Math.PI; });
var lat1 = radians[0], lon1 = radians[1], lat2 = radians[2], lon2 = radians[3];
if (!lat1 || !lon1)
return 0;
var R = 6372.8 * 1000; // meters
var dLat = lat2 - lat1;
var dLon = lon2 - lon1;
var a = Math.sin(dLat / 2) * Math.sin(dLat / 2) + Math.sin(dLon / 2) * Math.sin(dLon / 2) * Math.cos(lat1) * Math.cos(lat2);
var c = 2 * Math.asin(Math.sqrt(a));
return R * c;
}
var preLon, preLat, preUser, preHour
db.collection.aggregate([
{ $addFields: { DateParts: { $dateToParts: { date: "$Date" } } } },
{
$addFields: {
Date_Hour: {
$dateFromParts: {
year: "$DateParts.year",
month: "$DateParts.month",
day: "$DateParts.day",
hour: "$DateParts.hour"
}
}
}
},
{ $sort: { Username: 1, Date: 1 } }
]).forEach(function (doc) {
if ( preUser != doc.Username || preHour != doc.Date_Hour.getTime() ) {
// maybe other conditions for reset should be added
preLon = null;
preLat = null;
preUser = null;
preHour = null;      
}
distance = haversine(preLat, preLon, doc.Location.coordinates[1], doc.Location.coordinates[0])
preLon = doc.Location.coordinates[0];
preLat = doc.Location.coordinates[1];
preUser = doc.Username;
preHour = doc.Date_Hour.getTime();
db.collection.updateOne(
{ _id: doc._id },
{ $set: { distance: distance, Date_Hour: doc.Date_Hour } }
)
})

之后你可以按用户和小时对距离求和：

db.collection.aggregate([
{
$group: {
_id: { Username: "$Username", hour: "$Date_Hour" },
distance: { $sum: "$distance" }
}
},
{ $replaceRoot: { newRoot: { $mergeObjects: ["$$ROOT", "$_id"] } } },
{ $unset: "_id" }
])

样本结果：

{ 
"distance" : 111226.34257109408, 
"Username" : "abc", 
"hour" : ISODate("2016-06-16T21:00:00.000+0200")
}
{ 
"distance" : 111226.34257109408, 
"Username" : "abc", 
"hour" : ISODate("2016-06-16T11:00:00.000+0200")
}
{ 
"distance" : 8108.472378934691, 
"Username" : "xyz", 
"hour" : ISODate("2016-06-16T11:00:00.000+0200")
}

另一种方法(没有JavaScript功能(是这样的：

db.collection.aggregate([
{ $addFields: { DateParts: { $dateToParts: { date: "$Date" } } } },
// Create hourly value 
{
$addFields: {
Date_Hour: {
$dateFromParts: {
year: "$DateParts.year",
month: "$DateParts.month",
day: "$DateParts.day",
hour: "$DateParts.hour"
}
}
}
},
// Sort documents in order to get proper track
{ $sort: { Username: 1, Date: 1 } },
// Group by user and hour
{
$group: {
_id: {Username: "$Username", Date_Hour: "$Date_Hour"},
Locations: { $push: "$Location" }
}
},
// Put "Location" & "Location+1" into array in order to get a line
{
$addFields: {
distance: {
$map: {
input: { $range: [0, { $size: "$Locations" }] },
in: {
$cond: {
if: { $eq: ["$$this", 0] },
then: null,
else: [
{ $arrayElemAt: [{ $arrayElemAt: ["$Locations.coordinates", { $subtract: ["$$this", 1] }] }, 0] },
{ $arrayElemAt: [{ $arrayElemAt: ["$Locations.coordinates", { $subtract: ["$$this", 1] }] }, 1] },
{ $arrayElemAt: [{ $arrayElemAt: ["$Locations.coordinates", "$$this"] }, 0] },
{ $arrayElemAt: [{ $arrayElemAt: ["$Locations.coordinates", "$$this"] }, 1] }
]
}
}
}
}
}
},
// Calculate the haversine 
{
$set: {
distance: {
$map: {
input: "$distance",
in: {
$let: {
vars: {
dlon: { $degreesToRadians: { $subtract: [{ $arrayElemAt: ["$$this", 0] }, { $arrayElemAt: ["$$this", 2] }] } },
dlat: { $degreesToRadians: { $subtract: [{ $arrayElemAt: ["$$this", 1] }, { $arrayElemAt: ["$$this", 3] }] } },
lat1: { $degreesToRadians: { $arrayElemAt: ["$$this", 1] } },
lat2: { $degreesToRadians: { $arrayElemAt: ["$$this", 3] } }
},
in: {
// sin²(dLat / 2) + sin²(dLon / 2) * cos(lat1) * cos(lat2);
$add: [
{ $pow: [{ $sin: { $divide: ["$$dlat", 2] } }, 2] },
{ $multiply: [{ $pow: [{ $sin: { $divide: ["$$dlon", 2] } }, 2] }, { $cos: "$$lat1" }, { $cos: "$$lat2" }] }
]
}
}
}
}
}
}
},
// Calculate the distance (in meters, given by "6372.8 * 1000")
{
$set: {
distance: {
$map: {
input: "$distance",
in: { $multiply: [6372.8, 1000, 2, { $asin: { $sqrt: "$$this" } }] }
}
}
}
},
// Sum up distance parts
{ $set: { distance: { $sum: "$distance" } } },
// Remove unused fields
{ $unset: "Locations" }
])

参见Mongo游乐场

你可以看看$geoNear。这通过distanceField自动提供距离。但是，不能指定矩形(即$box(作为过滤器，必须指定一个点加上maxDistance。

Answer 2

const geo = require('geojson-tools');  // package required to calculate the distance
// fetching the collection from MongoDB database
const collectionResult = db.collection.aggregate([
{
$match: {
Date: {
// mention the date range from and until
$gte: new Date("2019-06-25T09:49:32.398Z"),  
$lt: new Date("2019-06-28T09:54:29.379Z")
}
}
},    
{ $addFields: { DateParts: { $dateToParts: { date: "$Date" } } } },
// Create hourly value 
{
$addFields: {
Date_Hour: {
$dateFromParts: {
year: "$DateParts.year",
month: "$DateParts.month",
day: "$DateParts.day",
hour: "$DateParts.hour"
}
}
}
},
// Sort documents in order to get proper track
{ $sort: { Username: 1, Date: 1 } },
// Group by user and hour
{
$group: {
_id: {username: "$Username", date_hour: "$Date_Hour"},
items: {
$push: {
locations: "$Location"
}
}
}
},
// Sort documents in order to get proper track
{ $sort: {username: 1, date_hour: 1 } }
])
// save the collection results to array for calculating the distance 
const distanceResults = collectionResult.toArray(function (err, result) {
if (err) throw err;
const distances = result.map((item) => {
// console.info(item._id.username)
const coordinates = item.items.map(x => x.locations).map(x => x.coordinates)
const distance = {
username: item._id.username,
date: item._id.date_hour,
distance: geo.getDistance(coordinates, 4)
}
console.info(JSON.stringify(distance))
return distance
})
db.close();
return distances
})
return distanceResults
}) 
}

它显示了我认为的完美结果，使用geojson包可以很容易地计算距离。

非常感谢你的帮助。

答案是这样的

{"username":"ABC","date":"2019-06-25T12:00:00.000Z","distance":1.5888}
{"username":"CDE","date":"2019-06-25T15:00:00.000Z","distance":0.188}
{"username":"FGH","date":"2019-06-27T06:00:00.000Z","distance":12.041}
{"username":"IJK","date":"2019-06-27T09:00:00.000Z","distance":1.5506}
{"username":"LMN","date":"2019-06-28T08:00:00.000Z","distance":0.0206}