public static void noMapMatching(InsertDatum insertDatum, DataRow dataRow)
{
string text = "There is a out of registered links DriverID: " + insertDatum.DriverId
+ "StartTime: " + dataRow.Field <DateTime>(AndroidGpsRawDao.ColumnJst);
commentToSlack(text);
}
public static void InsertAcc(List <string> insertFileList, InsertConfig config, List <InsertDatum> insertDatumList)
{
foreach (var filePath in insertFileList)
//Parallel.For(0, insertFileList.Count, i =>
{
string[] word = filePath.Split('\\');
// ACCファイルでない場合はcontinue
if (System.Text.RegularExpressions.Regex.IsMatch(word[word.Length - 1], @"\d{14}Unsent16HzAccel.csv"))
{
var datum = new InsertDatum()
{
DriverId = DriverNames.GetDriverId(word[DriverIndex]),
CarId = CarNames.GetCarId(word[CarIndex]),
SensorId = SensorNames.GetSensorId(word[SensorIndex]),
StartTime = config.StartDate,
EndTime = config.EndDate,
EstimatedCarModel = EstimatedCarModel.GetModel(config.CarModel)
};
InsertDatum.AddDatumToList(insertDatumList, datum);
InsertAccRaw(filePath, datum);
}
//});
}
}
public static void InsertEcologDopplerNotMM(InsertDatum datum, MainWindowViewModel.UpdateTextDelegate updateTextDelegate, InsertConfig.GpsCorrection correction)
{
var tripsTable = TripsDopplerNotMMDao.Get(datum);
//int i = 1;
//foreach (DataRow row in tripsTable.Rows)
//{
// updateTextDelegate($"Insetring ECOLOG ... , {i} / {tripsTable.Rows.Count}");
// LogWritter.WriteLog(LogWritter.LogMode.Ecolog, $"Insetring ECOLOG... , { i} / { tripsTable.Rows.Count}, Datum: {datum}");
// var ecologTable = HagimotoEcologCalculator.CalcEcolog(row, datum, correction);
// EcologDao.Insert(ecologTable);
// i++;
//}
Parallel.For(0, tripsTable.Rows.Count, i =>
{
if (tripsTable.Rows[i][(TripsDopplerNotMMDao.ColumnConsumedEnergy)] == DBNull.Value)
{
updateTextDelegate($"Insetring ECOLOGDopplerNotMM ... , {i + 1} / {tripsTable.Rows.Count}");
LogWritter.WriteLog(LogWritter.LogMode.Ecolog, $"Insetring ECOLOGDopplerNotMM... , { i} / { tripsTable.Rows.Count}, Datum: {datum}");
var ecologTable = HagimotoEcologCalculator.CalcEcologDoppler(tripsTable.Rows[i], datum, correction);
EcologDopplerNotMMDao.Insert(ecologTable);
}
});
TripsDopplerNotMMDao.UpdateConsumedEnergy();
}
private static DataTable InsertGpsRaw(string filePath, InsertDatum datum, InsertConfig.GpsCorrection correction)
{
var gpsRawTable = GpsFileHandler.ConvertCsvToDataTable(filePath, datum, correction);
if (correction == InsertConfig.GpsCorrection.Normal ||
correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching ||
correction == InsertConfig.GpsCorrection.MapMatching)
{
gpsRawTable = GpsFileHandler.ConvertCsvToDataTable(filePath, datum, correction);
}
else if (correction == InsertConfig.GpsCorrection.DopplerSpeed || correction == InsertConfig.GpsCorrection.DopplerNotMM)
{
gpsRawTable = GpsFileHandler.ConvertCsvToDataTableDoppler(filePath, datum, correction);
}
if (gpsRawTable.Rows.Count != 0)
{
if (correction == InsertConfig.GpsCorrection.Normal ||
correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching ||
correction == InsertConfig.GpsCorrection.MapMatching)
{
AndroidGpsRawDao.Insert(gpsRawTable);
}
else if (correction == InsertConfig.GpsCorrection.DopplerSpeed || correction == InsertConfig.GpsCorrection.DopplerNotMM)
{
AndroidGpsRawDopplerDao.Insert(gpsRawTable);
}
}
return(gpsRawTable);
}
public static DataTable GetNormalized(DateTime startTime, DateTime endTime, InsertDatum datum)
{
//GPRMC車速から参照するように改造済み
var query = new StringBuilder();
query.AppendLine($"WITH convert_gps");
query.AppendLine($"AS (");
query.AppendLine($" SELECT GPS.{ColumnDriverId}");
query.AppendLine($" ,GPS.{ColumnCarId}");
query.AppendLine($" ,GPS.{ColumnSensorId}");
query.AppendLine($" ,CONVERT(DATETIME, CONVERT(VARCHAR(30), CONVERT(DATETIME, (");
query.AppendLine($" CASE ");
query.AppendLine($" WHEN DATEPART(Ms, GPS.{ColumnJst}) >= 500");
query.AppendLine($" THEN DATEADD(SECOND, 1, GPS.{ColumnJst})");
query.AppendLine($" ELSE GPS.{ColumnJst}");
query.AppendLine($" END");
query.AppendLine($" )), 20)) AS {ColumnJst}");
query.AppendLine($" ,GPS.{ColumnLatitude}");
query.AppendLine($" ,GPS.{ColumnLongitude}");
query.AppendLine($" ,CASE ");
query.AppendLine($" WHEN GPS.{ColumnBearing} IS NULL");
query.AppendLine($" THEN RMC.MOVING_SPEED * 1.852");
query.AppendLine($" ELSE GPS.{ColumnSpeed}");
query.AppendLine($" END AS {ColumnSpeed}");
query.AppendLine($" ,{ColumnHeading}");
query.AppendLine($" ,{ColumnDistanceDifference}");
query.AppendLine($" ,{ColumnLinkId}");
query.AppendLine($" ,{ColumnRoadTheta}");
query.AppendLine($" FROM {TableName} AS GPS");
query.AppendLine($" LEFT JOIN GPRMC_RAW AS RMC");
query.AppendLine($" ON GPS.{ColumnSensorId} = RMC.{ColumnSensorId}");
query.AppendLine($" AND GPS.{ColumnJst} = RMC.{ColumnJst}");
query.AppendLine($" WHERE GPS.{ColumnDriverId} = {datum.DriverId}");
query.AppendLine($" AND GPS.{ColumnCarId} = {datum.CarId}");
query.AppendLine($" AND GPS.{ColumnSensorId} = {datum.SensorId}");
query.AppendLine($" AND GPS.{ColumnJst} >= '{startTime}'");
query.AppendLine($" AND GPS.{ColumnJst} <= '{endTime}'");
query.AppendLine($" )");
query.AppendLine($"SELECT {ColumnDriverId}");
query.AppendLine($" ,{ColumnCarId}");
query.AppendLine($" ,{ColumnSensorId}");
query.AppendLine($" ,{ColumnJst}");
query.AppendLine($" ,AVG({ColumnLatitude}) AS {ColumnLatitude}");
query.AppendLine($" ,AVG({ColumnLongitude}) AS {ColumnLongitude}");
query.AppendLine($" ,CAST(AVG({ColumnSpeed}) AS real) AS {ColumnSpeed}");
query.AppendLine($" ,CAST(AVG({ColumnHeading}) AS real) AS {ColumnHeading}");
query.AppendLine($" ,CAST(SUM({ColumnDistanceDifference}) AS real) AS {ColumnDistanceDifference}");
query.AppendLine($" ,MIN({ColumnLinkId}) AS {ColumnLinkId}");
query.AppendLine($" ,CAST(AVG({ColumnRoadTheta}) AS real) AS {ColumnRoadTheta}");
query.AppendLine($"FROM convert_gps");
query.AppendLine($"WHERE speed IS NOT NULL");
query.AppendLine($"GROUP BY {ColumnDriverId}");
query.AppendLine($" ,{ColumnCarId}");
query.AppendLine($" ,{ColumnSensorId}");
query.AppendLine($" ,{ColumnJst}");
query.AppendLine($"ORDER BY {ColumnJst}");
return(DatabaseAccesser.GetResult(query.ToString()));
}
public static Task InsertGps(List <string> insertFileList, InsertConfig config, int correctionIndex, List <InsertDatum> insertDatumList)
{
var tasks = new List <Task>();
foreach (string filePath in insertFileList)
{
Console.WriteLine("GPSinserting:" + filePath);
string[] word = filePath.Split('\\');
// GPSファイルでない場合はcontinue
if (!System.Text.RegularExpressions.Regex.IsMatch(word[word.Length - 1], @"\d{14}UnsentGPS.csv"))
{
continue;
}
var datum = new InsertDatum()
{
DriverId = DriverNames.GetDriverId(word[DriverIndex]),
CarId = CarNames.GetCarId(word[CarIndex]),
SensorId = SensorNames.GetSensorId(word[SensorIndex]),
StartTime = config.StartDate,
EndTime = config.EndDate,
EstimatedCarModel = EstimatedCarModel.GetModel(config.CarModel)
};
InsertDatum.AddDatumToList(insertDatumList, datum);
LogWritter.WriteLog(LogWritter.LogMode.Gps, $"インサートデータ, FilePath: {filePath}, DriverId: {datum.DriverId}, CarId: {datum.CarId}, SensorId: {datum.SensorId}");
// ファイルごとの処理なので主キー違反があっても挿入されないだけ
var gpsRawTable = InsertGpsRaw(filePath, datum, config.Correction[correctionIndex]);
if (config.Correction[correctionIndex] == InsertConfig.GpsCorrection.SpeedLPFMapMatching ||
config.Correction[correctionIndex] == InsertConfig.GpsCorrection.MapMatching)
{
gpsRawTable = MapMatching.getResultMapMatching(gpsRawTable, datum);
}
else if (config.Correction[correctionIndex] == InsertConfig.GpsCorrection.DopplerSpeed)
{
gpsRawTable = MapMatching.getResultMapMatchingDoppler(gpsRawTable, datum);
}
if (gpsRawTable.Rows.Count != 0)
{
var task = Task.Run(() =>
{
InsertCorrectedGps(gpsRawTable, config.Correction[correctionIndex]);
});
tasks.Add(task);
TripInserter.InsertTripRaw(gpsRawTable, config.Correction[correctionIndex]);
//TripInserter.InsertTrip(datum, config.Correction[correctionIndex]);
}
else
{
LogWritter.WriteLog(LogWritter.LogMode.Gps, $"ファイルの行数が0行のためインサートを行いませんでした: {filePath}");
}
}
return(Task.WhenAll(tasks));
}
private static bool IsHome(double latitude, double longitude, DateTime date, InsertDatum datum)
{
DataTable dataTable = PlaceGetter.GetInstance().getDataTable();
DataRow[] dataRows = dataTable.Select("property = 'home'");
for (int i = 0; i < dataRows.Length; i++)
{
if (dataRows[i][PlaceDao.ColumnStartDate] == DBNull.Value && dataRows[i][PlaceDao.ColumnEndDate] == DBNull.Value)
{
if (latitude > dataRows[i].Field <double>(PlaceDao.ColumnStartLatitude) &&
latitude <dataRows[i].Field <double>(PlaceDao.ColumnEndLatitude) &&
longitude> dataRows[i].Field <double>(PlaceDao.ColumnStartLongitude) &&
longitude < dataRows[i].Field <double>(PlaceDao.ColumnEndLongitude))
{
return(true);
}
}
else if (dataRows[i][PlaceDao.ColumnStartDate] != DBNull.Value && dataRows[i][PlaceDao.ColumnEndDate] == DBNull.Value)
{
if (latitude > dataRows[i].Field <double>(PlaceDao.ColumnStartLatitude) &&
latitude <dataRows[i].Field <double>(PlaceDao.ColumnEndLatitude) &&
longitude> dataRows[i].Field <double>(PlaceDao.ColumnStartLongitude) &&
longitude <dataRows[i].Field <double>(PlaceDao.ColumnEndLongitude) &&
date> dataRows[i].Field <DateTime>(PlaceDao.ColumnStartDate))
{
return(true);
}
}
else if (dataRows[i][PlaceDao.ColumnStartDate] == DBNull.Value && dataRows[i][PlaceDao.ColumnEndDate] != DBNull.Value)
{
if (latitude > dataRows[i].Field <double>(PlaceDao.ColumnStartLatitude) &&
latitude <dataRows[i].Field <double>(PlaceDao.ColumnEndLatitude) &&
longitude> dataRows[i].Field <double>(PlaceDao.ColumnStartLongitude) &&
longitude < dataRows[i].Field <double>(PlaceDao.ColumnEndLongitude) &&
date < dataRows[i].Field <DateTime>(PlaceDao.ColumnEndDate))
{
return(true);
}
}
else
{
if (latitude > dataRows[i].Field <double>(PlaceDao.ColumnStartLatitude) &&
latitude <dataRows[i].Field <double>(PlaceDao.ColumnEndLatitude) &&
longitude> dataRows[i].Field <double>(PlaceDao.ColumnStartLongitude) &&
longitude <dataRows[i].Field <double>(PlaceDao.ColumnEndLongitude) &&
date> dataRows[i].Field <DateTime>(PlaceDao.ColumnStartDate) &&
date < dataRows[i].Field <DateTime>(PlaceDao.ColumnEndDate))
{
return(true);
}
}
}
return(false);
}
private static DataTable InsertGpsRaw(string filePath, InsertDatum datum)
{
var gpsRawTable = GpsFileHandler.ConvertCsvToDataTable(filePath, datum);
if (gpsRawTable.Rows.Count != 0)
{
AndroidGpsRawDao.Insert(gpsRawTable);
}
return(gpsRawTable);
}
private static bool IsHome(double latitude, double longitude, DateTime date, InsertDatum datum)
{
if (latitude > Coordinate.TommyHome.LatitudeStart &&
latitude < Coordinate.TommyHome.LatitudeEnd &&
longitude > Coordinate.TommyHome.LongitudeStart &&
longitude < Coordinate.TommyHome.LongitudeEnd)
{
return(true);
}
if (latitude > Coordinate.MoriHome.LatitudeStart &&
latitude < Coordinate.MoriHome.LatitudeEnd &&
longitude > Coordinate.MoriHome.LongitudeStart &&
longitude < Coordinate.MoriHome.LongitudeEnd)
{
return(true);
}
if (latitude > Coordinate.TamuraHomeBefore.LatitudeStart &&
latitude < Coordinate.TamuraHomeBefore.LatitudeEnd &&
longitude > Coordinate.TamuraHomeBefore.LongitudeStart &&
longitude < Coordinate.TamuraHomeBefore.LongitudeEnd &&
date < Coordinate.TamuraHomeBefore.EndDate)
{
return(true);
}
if (latitude > Coordinate.TamuraHomeAfter.LatitudeStart &&
latitude < Coordinate.TamuraHomeAfter.LatitudeEnd &&
longitude > Coordinate.TamuraHomeAfter.LongitudeStart &&
longitude < Coordinate.TamuraHomeAfter.LongitudeEnd &&
date > Coordinate.TamuraHomeAfter.StartDate)
{
return(true);
}
if (latitude > Coordinate.AyaseCityHall.LatitudeStart &&
latitude < Coordinate.AyaseCityHall.LatitudeEnd &&
longitude > Coordinate.AyaseCityHall.LongitudeStart &&
longitude < Coordinate.AyaseCityHall.LongitudeEnd)
{
return(true);
}
if (latitude > Coordinate.UemuraHome.LatitudeStart &&
latitude < Coordinate.UemuraHome.LatitudeEnd &&
longitude > Coordinate.UemuraHome.LongitudeStart &&
longitude < Coordinate.UemuraHome.LongitudeEnd)
{
return(true);
}
return(false);
}
/***
* このレベルになるとDB側にテーブル関数作ったほうがいいよね...
* 頑張れる方、リファクタリングしてください
* Elizabeth
* Your tears and my fears are almost disappear
* So let's share the perfect time,For you and me
* You knocked on my door
* So let's start our journey
* Because you came to see me first
***/
public static DataTable GetAccurateStoppingAccRaw(int timeDiff, InsertDatum datum)
{
string query = "with LOW_SPEED as ";
query += "( ";
query += " select DRIVER_ID,SENSOR_ID,DATEADD(second,-1,JST) as START_TIME, JST as END_TIME ";
query += " from CORRECTED_GPS ";
query += " where DRIVER_ID = "+ datum.DriverId + " ";
query += " and SENSOR_ID = "+ datum.SensorId + " ";
query += " and SPEED < 1 ";
query += " and JST >= '"+ datum.StartTime + "' ";
query += " and JST <= '"+ datum.EndTime + "' ";
query += ") ";
query += ", UPPER_TIME as ";
query += "( ";
query += " select ROW_NUMBER() over(order by START_TIME) as NUMBER, DRIVER_ID, SENSOR_ID, START_TIME ";
query += " from ";
query += " ( ";
query += " select DRIVER_ID,SENSOR_ID,START_TIME ";
query += " from LOW_SPEED ";
query += " except ";
query += " select DRIVER_ID,SENSOR_ID,END_TIME ";
query += " from LOW_SPEED ";
query += " ) as UP ";
query += ") ";
query += ", LOWER_TIME as ";
query += "( ";
query += " select ROW_NUMBER() over(order by END_TIME) as NUMBER, DRIVER_ID,SENSOR_ID,END_TIME ";
query += " from ";
query += " ( ";
query += " select DRIVER_ID,SENSOR_ID,END_TIME ";
query += " from LOW_SPEED ";
query += " except ";
query += " select DRIVER_ID,SENSOR_ID,START_TIME ";
query += " from LOW_SPEED ";
query += " ) as LOW ";
query += ") ";
query += ", LOW_SPEED_SPAN as ";
query += "( ";
query += " select LOWER_TIME.DRIVER_ID,LOWER_TIME.SENSOR_ID,UPPER_TIME.START_TIME,LOWER_TIME.END_TIME ";
query += " from LOWER_TIME,UPPER_TIME ";
query += " where LOWER_TIME.NUMBER = UPPER_TIME.NUMBER ";
query += ") ";
query += "select AVG(ACC_X) as ACC_X,AVG(ACC_Y) as ACC_Y,AVG(ACC_Z) as ACC_Z ";
query += "from ANDROID_ACC_RAW,LOW_SPEED_SPAN ";
query += "where ANDROID_ACC_RAW.DRIVER_ID = LOW_SPEED_SPAN.DRIVER_ID ";
query += "and ANDROID_ACC_RAW.SENSOR_ID = LOW_SPEED_SPAN.SENSOR_ID ";
query += "and ANDROID_ACC_RAW.DATETIME <= DATEADD(MILLISECOND,-1*" + timeDiff + ",LOW_SPEED_SPAN.END_TIME) ";
query += "and ANDROID_ACC_RAW.DATETIME > DATEADD(MILLISECOND,-1*" + timeDiff + ",LOW_SPEED_SPAN.START_TIME) ";
return(DatabaseAccesser.GetResult(query));
}
public static DataTable Get(DateTime startTime, DateTime endTime, InsertDatum datum)
{
var query = new StringBuilder();
query.AppendLine($"SELECT *");
query.AppendLine($" FROM {TableName}");
query.AppendLine($" WHERE {ColumnDriverId} = {datum.DriverId}");
query.AppendLine($" AND {ColumnCarId} = {datum.CarId}");
query.AppendLine($" AND {ColumnSensorId} = {datum.SensorId}");
query.AppendLine($" AND {ColumnJst} >= '{startTime}'");
query.AppendLine($" AND {ColumnJst} <= '{endTime}'");
return(DatabaseAccesser.GetResult(query.ToString()));
}
public static DataTable Get(InsertDatum datum)
{
var query = new StringBuilder();
query.AppendLine("SELECT *");
query.AppendLine($"FROM {TableName}");
query.AppendLine($"WHERE {TripsDao.ColumnDriverId} = {datum.DriverId}");
query.AppendLine($"AND {TripsDao.ColumnCarId} = {datum.CarId}");
query.AppendLine($"AND {TripsDao.ColumnSensorId} = {datum.SensorId}");
query.AppendLine($"AND {TripsDao.ColumnStartTime} >= '{datum.StartTime}'");
query.AppendLine($"AND {TripsDao.ColumnEndTime} <= '{datum.EndTime}'");
query.AppendLine($"ORDER BY {ColumnStartTime}");
return(DatabaseAccesser.GetResult(query.ToString()));
}
private static void InsertAccRaw(string filePath, InsertDatum datum)
{
var accRawTable = AccFileHandler.ConvertCsvToDataTable(filePath, datum.DriverId, datum.CarId, datum.SensorId);
accRawTable = SortTableByDateTime(accRawTable);
if (accRawTable.Rows.Count != 0)
{
var normalizedAccTable = DataTableUtil.GetAndroidAccRawTable();
#region インデックス 0 の場合
var firstRow = normalizedAccTable.NewRow();
firstRow.SetField(AndroidAccRawDao.ColumnDateTime, accRawTable.Rows[0].Field <DateTime>(AndroidAccRawDao.ColumnDateTime));
firstRow.SetField(AndroidAccRawDao.ColumnDriverId, accRawTable.Rows[0].Field <int>(AndroidAccRawDao.ColumnDriverId));
firstRow.SetField(AndroidAccRawDao.ColumnCarId, accRawTable.Rows[0].Field <int>(AndroidAccRawDao.ColumnCarId));
firstRow.SetField(AndroidAccRawDao.ColumnSensorId, accRawTable.Rows[0].Field <int>(AndroidAccRawDao.ColumnSensorId));
firstRow.SetField(AndroidAccRawDao.ColumnAccX, accRawTable.Rows[0].Field <Single>(AndroidAccRawDao.ColumnAccX));
firstRow.SetField(AndroidAccRawDao.ColumnAccY, accRawTable.Rows[0].Field <Single>(AndroidAccRawDao.ColumnAccY));
firstRow.SetField(AndroidAccRawDao.ColumnAccZ, accRawTable.Rows[0].Field <Single>(AndroidAccRawDao.ColumnAccZ));
normalizedAccTable.Rows.Add(firstRow);
#endregion
for (int i = 1; i < accRawTable.Rows.Count; i++)
{
//SQLSERVERのDATETIME型のミリ秒は0,3,7しか取らないため、5ミリ秒よりも短いデータが存在する場合は挿入時にエラーを出す可能性がある
if ((accRawTable.Rows[i].Field <DateTime>(AndroidAccRawDao.ColumnDateTime) -
accRawTable.Rows[i - 1].Field <DateTime>(AndroidAccRawDao.ColumnDateTime)).TotalMilliseconds >= 4)
{
var row = normalizedAccTable.NewRow();
row.SetField(AndroidAccRawDao.ColumnDateTime, accRawTable.Rows[i].Field <DateTime>(AndroidAccRawDao.ColumnDateTime));
row.SetField(AndroidAccRawDao.ColumnDriverId, accRawTable.Rows[i].Field <int>(AndroidAccRawDao.ColumnDriverId));
row.SetField(AndroidAccRawDao.ColumnCarId, accRawTable.Rows[i].Field <int>(AndroidAccRawDao.ColumnCarId));
row.SetField(AndroidAccRawDao.ColumnSensorId, accRawTable.Rows[i].Field <int>(AndroidAccRawDao.ColumnSensorId));
row.SetField(AndroidAccRawDao.ColumnAccX, accRawTable.Rows[i].Field <Single>(AndroidAccRawDao.ColumnAccX));
row.SetField(AndroidAccRawDao.ColumnAccY, accRawTable.Rows[i].Field <Single>(AndroidAccRawDao.ColumnAccY));
row.SetField(AndroidAccRawDao.ColumnAccZ, accRawTable.Rows[i].Field <Single>(AndroidAccRawDao.ColumnAccZ));
normalizedAccTable.Rows.Add(row);
}
}
// ファイルごとの処理なので主キー違反があっても挿入されないだけ
AndroidAccRawDao.Insert(normalizedAccTable);
}
}
public static DataTable CalcEcolog(DataRow tripRow, InsertDatum datum, InsertConfig.GpsCorrection correction)
{
var correctedGpsTable = new DataTable();
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching) //補正GPS取得元変更
{
correctedGpsTable = CorrectedGpsSpeedLPF005MMDao.GetNormalized(tripRow.Field <DateTime>(TripsDao.ColumnStartTime),
tripRow.Field <DateTime>(TripsDao.ColumnEndTime), datum);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching)
{
correctedGpsTable = CorrectedGPSMMDao.GetNormalized(tripRow.Field <DateTime>(TripsDao.ColumnStartTime),
tripRow.Field <DateTime>(TripsDao.ColumnEndTime), datum);
}
else if (correction == InsertConfig.GpsCorrection.Normal)
{
correctedGpsTable = CorrectedGpsDao.GetNormalized(tripRow.Field <DateTime>(TripsDao.ColumnStartTime),
tripRow.Field <DateTime>(TripsDao.ColumnEndTime), datum);
}
var ecologTable = DataTableUtil.GetEcologTable();
if (correctedGpsTable.Rows.Count == 0)
{
return(ecologTable);
}
var firstRow = GenerateFirstEcologRow(
ecologTable.NewRow(), tripRow, correctedGpsTable.Rows[0], datum);
ecologTable.Rows.Add(firstRow);
var beforeRow = ecologTable.NewRow();
beforeRow.ItemArray = firstRow.ItemArray;
for (int i = 1; i < correctedGpsTable.Rows.Count; i++)
{
var row = GenerateEcologRow(
ecologTable.NewRow(), beforeRow, tripRow, correctedGpsTable.Rows[i], datum);
ecologTable.Rows.Add(row);
beforeRow.ItemArray = row.ItemArray;
}
return(ecologTable);
}
public static DataTable GetNormalized(DateTime startTime, DateTime endTime, InsertDatum datum)
{
var query = new StringBuilder();
query.AppendLine($"WITH convert_gps");
query.AppendLine($"AS (");
query.AppendLine($" SELECT {ColumnDriverId}");
query.AppendLine($" ,{ColumnCarId}");
query.AppendLine($" ,{ColumnSensorId}");
query.AppendLine($" ,CONVERT(DATETIME, CONVERT(VARCHAR(30), CONVERT(DATETIME, (");
query.AppendLine($" CASE ");
query.AppendLine($" WHEN DATEPART(Ms, {ColumnJst}) >= 500");
query.AppendLine($" THEN DATEADD(SECOND, 1, {ColumnJst})");
query.AppendLine($" ELSE {ColumnJst}");
query.AppendLine($" END");
query.AppendLine($" )), 20)) AS {ColumnJst}");
query.AppendLine($" ,{ColumnLatitude}");
query.AppendLine($" ,{ColumnLongitude}");
query.AppendLine($" ,{ColumnSpeed}");
query.AppendLine($" ,{ColumnHeading}");
query.AppendLine($" ,{ColumnDistanceDifference}");
query.AppendLine($" FROM {TableName}");
query.AppendLine($" WHERE {ColumnDriverId} = {datum.DriverId}");
query.AppendLine($" AND {ColumnCarId} = {datum.CarId}");
query.AppendLine($" AND {ColumnSensorId} = {datum.SensorId}");
query.AppendLine($" AND {ColumnJst} >= '{startTime}'");
query.AppendLine($" AND {ColumnJst} <= '{endTime}'");
query.AppendLine($" )");
query.AppendLine($"SELECT {ColumnDriverId}");
query.AppendLine($" ,{ColumnCarId}");
query.AppendLine($" ,{ColumnSensorId}");
query.AppendLine($" ,{ColumnJst}");
query.AppendLine($" ,AVG({ColumnLatitude}) AS {ColumnLatitude}");
query.AppendLine($" ,AVG({ColumnLongitude}) AS {ColumnLongitude}");
query.AppendLine($" ,CAST(AVG({ColumnSpeed}) AS real) AS {ColumnSpeed}");
query.AppendLine($" ,CAST(AVG({ColumnHeading}) AS real) AS {ColumnHeading}");
query.AppendLine($" ,CAST(SUM({ColumnDistanceDifference}) AS real) AS {ColumnDistanceDifference}");
query.AppendLine($"FROM convert_gps");
query.AppendLine($"GROUP BY {ColumnDriverId}");
query.AppendLine($" ,{ColumnCarId}");
query.AppendLine($" ,{ColumnSensorId}");
query.AppendLine($" ,{ColumnJst}");
query.AppendLine($"ORDER BY {ColumnJst}");
return(DatabaseAccesser.GetResult(query.ToString()));
}
public static void InsertCorrectedAcc(InsertDatum datum, InsertConfig config)
{
Console.WriteLine("CALLED: InsertCorrectedAcc, " + datum);
var tripsTable = new DataTable();
//if (config.Correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching)
//{
// tripsTable = TripsSpeedLPF005MMDao.Get(datum);
//}
//else
//{
// tripsTable = TripsDao.Get(datum);
//}
foreach (DataRow row in tripsTable.Rows)
{
var correctedAccTable = AccCorrector.CorrectAcc(datum.StartTime, datum.EndTime, datum, row);
// Tripsテーブルの1行ごろの処理なので主キー違反があっても挿入されないだけ
// Trip単位で途中で挿入が異常終了した場合は、DELETEが必要
CorrectedAccDao.Insert(correctedAccTable);
}
}
public static int GetMilliSencodTimeDiffBetweenJstAndAndroidTime(DateTime startTime, DateTime endTime, InsertDatum datum)
{
var query = new StringBuilder();
query.AppendLine($"SELECT AVG(DATEDIFF(MILLISECOND, {ColumnAndroidTime}, {ColumnJst})) AS time_diff");
query.AppendLine($"FROM {TableName}");
query.AppendLine($"WHERE {ColumnJst} >= '{startTime}'");
query.AppendLine($" AND {ColumnJst} <= '{endTime}'");
query.AppendLine($" AND {ColumnDriverId} = {datum.DriverId}");
query.AppendLine($" AND {ColumnSensorId} = {datum.SensorId}");
return(DatabaseAccesser.GetResult(query.ToString()).Rows[0].Field <int?>("time_diff") ?? 0);
}
public static DataTable ConvertCsvToDataTable(string filePath, InsertDatum datum, InsertConfig.GpsCorrection correction)
{
var parser = GetParser(filePath);
var gpsRawTable = new DataTable();
gpsRawTable = DataTableUtil.GetAndroidGpsRawTable();
string beforeJst = null;
while (!parser.EndOfData)
{
try
{
string[] fields = parser.ReadFields();
DataRow row = gpsRawTable.NewRow();
row.SetField(AndroidGpsRawDao.ColumnDriverId, datum.DriverId);
row.SetField(AndroidGpsRawDao.ColumnCarId, datum.CarId);
row.SetField(AndroidGpsRawDao.ColumnSensorId, datum.SensorId);
DateTime jst = DateTime.Parse(fields[0].ToString());
DateTime androidTime = DateTime.Parse(fields[1].ToString());
TimeSpan span = jst - androidTime;
#region Jstの設定
//android端末で取得したGPSの時刻が1日進む現象への対処
if (span.TotalHours > 23 && span.TotalHours < 25)
{
jst = jst.AddDays(-1);
}
row.SetField <DateTime>(AndroidGpsRawDao.ColumnJst, jst);
#endregion
#region AndroidTimeの設定
if (androidTime.Year == 1970)
{
androidTime = androidTime.AddYears(42);
androidTime = androidTime.AddMonths(6);
}
row.SetField(AndroidGpsRawDao.ColumnAndroidTime, androidTime);
// TODO string から DateTimeに変えて影響がないか
// row[AndroidGpsRawDao.ColumnAndroidTime] = androidTime.ToString(StringUtil.JstFormat);
#endregion
row.SetField(AndroidGpsRawDao.ColumnLatitude, fields[2]); // VALID
row.SetField(AndroidGpsRawDao.ColumnLongitude, fields[3]); // LATITUDE
row.SetField(AndroidGpsRawDao.ColumnAltitude, fields[4]); // LONGITUDE
if (correction == InsertConfig.GpsCorrection.DopplerSpeed && jst < NmeaStartDate)
{
}
else if (beforeJst != jst.ToString(StringUtil.JstFormat))
{
gpsRawTable.Rows.Add(row);
}
beforeJst = jst.ToString(StringUtil.JstFormat);
}
catch (NullReferenceException nre)
{
// TODO エラー処理
continue;
}
catch (IndexOutOfRangeException iore)
{
// TODO エラー処理
continue;
}
catch (FormatException fe)
{
Console.WriteLine(fe.Message);
// TODO エラー処理
}
catch (ArgumentException ae)
{
Console.WriteLine(ae.Message);
}
}
parser.Close();
return(gpsRawTable);
}
private static DataRow GenerateEcologDopplerRow(DataRow newRow, DataRow beforeRow, DataRow tripRow, DataRow correctedGpsRow, InsertDatum datum)
{
newRow.SetField(EcologDao.ColumnTripId, tripRow.Field <int>(TripsDao.ColumnTripId));
newRow.SetField(EcologDao.ColumnDriverId, tripRow.Field <int>(TripsDao.ColumnDriverId));
newRow.SetField(EcologDao.ColumnCarId, tripRow.Field <int>(TripsDao.ColumnCarId));
newRow.SetField(EcologDao.ColumnSensorId, tripRow.Field <int>(TripsDao.ColumnSensorId));
newRow.SetField(EcologDao.ColumnJst, correctedGpsRow.Field <DateTime>(CorrectedGpsDao.ColumnJst));
newRow.SetField(EcologDao.ColumnLatitude, correctedGpsRow.Field <double>(CorrectedGpsDao.ColumnLatitude));
newRow.SetField(EcologDao.ColumnLongitude, correctedGpsRow.Field <double>(CorrectedGpsDao.ColumnLongitude));
double speed = correctedGpsRow.Field <Single>(CorrectedGpsDao.ColumnSpeed);
double speedMeterPerSec = speed / 3.6;
//Console.WriteLine("SPEED: " + speed);
newRow.SetField(EcologDao.ColumnSpeed, speed);
newRow.SetField(EcologDao.ColumnHeading,
correctedGpsRow.Field <Single>(CorrectedGpsDao.ColumnHeading));
double distanceDiff = correctedGpsRow.Field <Single>(CorrectedGpsDao.ColumnDistanceDifference);
//Console.WriteLine("DISTANCE_DIFF: " + distanceDiff);
newRow.SetField(EcologDao.ColumnDistanceDifference, distanceDiff);
var meshAndAltitude = AltitudeCalculator.GetInstance().CalcAltitude(
correctedGpsRow.Field <double>(CorrectedGpsDao.ColumnLatitude),
correctedGpsRow.Field <double>(CorrectedGpsDao.ColumnLongitude));
newRow.SetField(EcologDao.ColumnTerraubAltitude, meshAndAltitude.Item2);
newRow.SetField(EcologDao.ColumnMeshId, meshAndAltitude.Item1);
double terrainAltitudeDiff = meshAndAltitude.Item2 -
beforeRow.Field <Single>(EcologDao.ColumnTerraubAltitude);
//Console.WriteLine("ALTITUDE_DIFF: " + terrainAltitudeDiff);
newRow.SetField(EcologDao.ColumnTerrainAltitudeDiffarencce, terrainAltitudeDiff);
// TODO 加速度を追加する場合はここへ
newRow.SetField(EcologDao.ColumnLongitudinalAcc, DBNull.Value);
newRow.SetField(EcologDao.ColumnLateralAcc, DBNull.Value);
newRow.SetField(EcologDao.ColumnVerticalAcc, DBNull.Value);
double airResistancePower = 0;
if (speed > 1 && distanceDiff > 0)
{
airResistancePower = AirResistanceCalculator.CalcPower(
Rho, datum.EstimatedCarModel.CdValue, datum.EstimatedCarModel.FrontalProjectedArea, (speed + WindSpeed) / 3.6, speedMeterPerSec);
}
//Console.WriteLine("AIR: " + airResistancePower);
newRow.SetField(
EcologDao.ColumnEnergyByAirResistance,
airResistancePower);
double rollingResistancePower = 0;
if (speed > 1 && distanceDiff > 0)
{
rollingResistancePower = RollingResistanceCalculator.CalcPower(
Myu, datum.EstimatedCarModel.Weight, Math.Atan(terrainAltitudeDiff / distanceDiff), speedMeterPerSec);
}
//Console.WriteLine("ROLLING: " + rollingResistancePower);
newRow.SetField(
EcologDao.ColumnEnergyByRollingResistance,
rollingResistancePower);
double climbingResistancePower = 0;
if (speed > 1 && distanceDiff > 0)
{
climbingResistancePower = ClimbingResistanceCalculator.CalcPower(
datum.EstimatedCarModel.Weight, Math.Atan(terrainAltitudeDiff / distanceDiff), speedMeterPerSec);
}
//Console.WriteLine("CLIMBING: " + climbingResistancePower);
newRow.SetField(
EcologDao.ColumnEnergyByClimbingResistance,
climbingResistancePower);
double accResistancePower = 0;
if (speed > 1 && distanceDiff > 0)
{
accResistancePower = AccResistanceCalculator.CalcPower(
beforeRow.Field <Single>(EcologDao.ColumnSpeed) / 3.6,
beforeRow.Field <DateTime>(EcologDao.ColumnJst),
speedMeterPerSec, correctedGpsRow.Field <DateTime>(CorrectedGpsDao.ColumnJst),
datum.EstimatedCarModel.Weight);
}
//Console.WriteLine("ACC: " + accResistancePower);
newRow.SetField(
EcologDao.ColumnEnergyByAccResistance,
accResistancePower);
double drivingResistancePower =
airResistancePower + rollingResistancePower + climbingResistancePower + accResistancePower;
double torque = 0;
if (drivingResistancePower > 0 && speed > 0)
{
torque = drivingResistancePower * 1000 * 3600 / speedMeterPerSec * datum.EstimatedCarModel.TireRadius /
datum.EstimatedCarModel.ReductionRatio;
}
int efficiency = EfficiencyCalculator.GetInstance().GetEfficiency(datum.EstimatedCarModel, speedMeterPerSec, torque);
//Console.WriteLine("EFFICIENCY: " + efficiency);
newRow.SetField(EcologDao.ColumnEfficiency, efficiency);
double convertLoss = ConvertLossCaluculator.CalcEnergy(
drivingResistancePower, datum.EstimatedCarModel, speedMeterPerSec, efficiency);
newRow.SetField(EcologDao.ColumnConvertLoss, convertLoss);
//Console.WriteLine("CONVERTLOSS: " + convertLoss);
double regeneEnergy = RegeneEnergyCalculator.CalcEnergy(drivingResistancePower,
speedMeterPerSec, datum.EstimatedCarModel, efficiency);
newRow.SetField(EcologDao.ColumnRegeneEnergy, regeneEnergy);
double regeneLoss = RegeneLossCalculator.CalcEnergy(drivingResistancePower, regeneEnergy,
datum.EstimatedCarModel, speedMeterPerSec, efficiency);
newRow.SetField(EcologDao.ColumnRegeneLoss, regeneLoss);
double lostEnergy = LostEnergyCalculator.CalcEnergy(convertLoss, regeneLoss, airResistancePower,
rollingResistancePower);
newRow.SetField(EcologDao.ColumnLostEnergy, lostEnergy);
newRow.SetField(EcologDao.ColumnConsumedElectricEnergy, ConsumedEnergyCaluculator.CalcEnergy(drivingResistancePower, datum.EstimatedCarModel, speedMeterPerSec, efficiency));
newRow.SetField(EcologDao.ColumnLostEnergyByWellToWheel, DBNull.Value);
newRow.SetField(EcologDao.ColumnConsumedFuel, DBNull.Value);
newRow.SetField(EcologDao.ColumnConsumedFuelByWellToWheel, DBNull.Value);
newRow.SetField(EcologDao.ColumnEnergyByEquipment,
EquipmentEnergyCalculator.CalcEquipmentEnergy(correctedGpsRow.Field <DateTime>(CorrectedGpsDao.ColumnJst)));
newRow.SetField(EcologDao.ColumnEnergyByCooling, DBNull.Value);
newRow.SetField(EcologDao.ColumnEnergyByHeating, DBNull.Value);
newRow.SetField(EcologDao.ColumnTripDirection, tripRow.Field <string>(TripsDao.ColumnTripDirection));
newRow.SetField(EcologDao.ColumnLinkId, correctedGpsRow.Field <string>(CorrectedGPSDopplerDao.ColumnLinkId));
newRow.SetField(EcologDao.ColumnRoadTheta, correctedGpsRow.Field <Single>(CorrectedGPSDopplerDao.ColumnRoadTheta));
return(newRow);
}
private static DataRow GenerateFirstEcologDopplerRow(DataRow newRow, DataRow tripRow, DataRow correctedGpsRow, InsertDatum datum)
{
newRow.SetField(EcologDao.ColumnTripId, tripRow.Field <int>(TripsDao.ColumnTripId));
newRow.SetField(EcologDao.ColumnDriverId, tripRow.Field <int>(TripsDao.ColumnDriverId));
newRow.SetField(EcologDao.ColumnCarId, tripRow.Field <int>(TripsDao.ColumnCarId));
newRow.SetField(EcologDao.ColumnSensorId, tripRow.Field <int>(TripsDao.ColumnSensorId));
newRow.SetField(EcologDao.ColumnJst, correctedGpsRow.Field <DateTime>(CorrectedGpsDao.ColumnJst));
newRow.SetField(EcologDao.ColumnLatitude, correctedGpsRow.Field <double>(CorrectedGpsDao.ColumnLatitude));
newRow.SetField(EcologDao.ColumnLongitude, correctedGpsRow.Field <double>(CorrectedGpsDao.ColumnLongitude));
newRow.SetField(EcologDao.ColumnSpeed, 0);
newRow.SetField(EcologDao.ColumnHeading, 0);
newRow.SetField(EcologDao.ColumnDistanceDifference, 0);
var meshAndAltitude = AltitudeCalculator.GetInstance().CalcAltitude(
correctedGpsRow.Field <double>(CorrectedGpsDao.ColumnLatitude),
correctedGpsRow.Field <double>(CorrectedGpsDao.ColumnLongitude));
newRow.SetField(EcologDao.ColumnTerraubAltitude, meshAndAltitude.Item2);
newRow.SetField(EcologDao.ColumnMeshId, meshAndAltitude.Item1);
newRow.SetField(EcologDao.ColumnTerrainAltitudeDiffarencce, 0);
// TODO 加速度を挿入する場合はここへ
newRow.SetField(EcologDao.ColumnLongitudinalAcc, DBNull.Value);
newRow.SetField(EcologDao.ColumnLateralAcc, DBNull.Value);
newRow.SetField(EcologDao.ColumnVerticalAcc, DBNull.Value);
newRow.SetField(EcologDao.ColumnEnergyByAirResistance, 0);
newRow.SetField(EcologDao.ColumnEnergyByRollingResistance, 0);
newRow.SetField(EcologDao.ColumnEnergyByClimbingResistance, 0);
newRow.SetField(EcologDao.ColumnEnergyByAccResistance, 0);
newRow.SetField(EcologDao.ColumnConvertLoss, 0);
newRow.SetField(EcologDao.ColumnRegeneLoss, 0);
newRow.SetField(EcologDao.ColumnRegeneEnergy, 0);
newRow.SetField(EcologDao.ColumnLostEnergy, 0);
newRow.SetField(EcologDao.ColumnEfficiency, 0);
newRow.SetField(EcologDao.ColumnConsumedElectricEnergy, 0);
newRow.SetField(EcologDao.ColumnLostEnergyByWellToWheel, DBNull.Value);
newRow.SetField(EcologDao.ColumnConsumedFuel, DBNull.Value);
newRow.SetField(EcologDao.ColumnConsumedFuelByWellToWheel, DBNull.Value);
newRow.SetField(EcologDao.ColumnEnergyByEquipment,
EquipmentEnergyCalculator.CalcEquipmentEnergy(correctedGpsRow.Field <DateTime>(CorrectedGpsDao.ColumnJst)));
newRow.SetField(EcologDao.ColumnEnergyByCooling, DBNull.Value);
newRow.SetField(EcologDao.ColumnEnergyByHeating, DBNull.Value);
newRow.SetField(EcologDao.ColumnTripDirection, tripRow.Field <string>(TripsDao.ColumnTripDirection));
newRow.SetField(EcologDao.ColumnLinkId, correctedGpsRow.Field <string>(CorrectedGPSDopplerDao.ColumnLinkId));
newRow.SetField(EcologDao.ColumnRoadTheta, correctedGpsRow.Field <Single>(CorrectedGPSDopplerDao.ColumnRoadTheta));
return(newRow);
}
public static void InsertTrip(InsertDatum datum, InsertConfig.GpsCorrection correction, bool isCheckedSightseeingInsert)
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, $"TRIP挿入開始, DRIVER_ID: {datum.DriverId}, CAR_ID: {datum.CarId}, SENSOR_ID: {datum.SensorId}");
var tripsRawTable = new DataTable();
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching)
{
tripsRawTable = TripsRawSpeedLPF005MMDao.Get(datum);
TripsSpeedLPF005MMDao.DeleteTrips(); //途中中断された際に作成したトリップを削除
}
else if (correction == InsertConfig.GpsCorrection.MapMatching)
{
tripsRawTable = TripsRawMMDao.Get(datum);
TripsMMDao.DeleteTrips(); //途中中断された際に作成したトリップを削除
}
else if (correction == InsertConfig.GpsCorrection.Normal)
{
tripsRawTable = TripsRawDao.Get(datum);
TripsDao.DeleteTrips(); //途中中断された際に作成したトリップを削除
}
else if (correction == InsertConfig.GpsCorrection.DopplerSpeed)
{
tripsRawTable = TripsRawDopplerDao.Get(datum);
TripsDopplerDao.DeleteTrips(); //途中中断された際に作成したトリップを削除
}
else if (correction == InsertConfig.GpsCorrection.DopplerNotMM)
{
tripsRawTable = TripsRawDopplerNotMMDao.Get(datum);
TripsDopplerNotMMDao.DeleteTrips();
}
LogWritter.WriteLog(LogWritter.LogMode.Trip, $"挿入対象のRAWデータ: {tripsRawTable.Rows.Count}");
for (int i = 0; i < tripsRawTable.Rows.Count; i++)
{
DataTable tripsTable = DataTableUtil.GetTripsTable();
// 観光オプションによるインサート処理はあらかじめ切り分ける。
if (isCheckedSightseeingInsert)
{
InsertSightSeeingTrip(tripsRawTable, tripsTable, datum, i, correction);
}
// 自宅出発
else if (IsHome(tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLongitude),
tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime),
datum))
{
InsertOutwardTrip(tripsRawTable, tripsTable, datum, i, correction);
}
// YNU出発
else if (IsYnu(tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLongitude)))
{
InsertHomewardTrip(tripsRawTable, tripsTable, datum, i, correction);
}
// 1トリップごとなので主キー違反があっても挿入されないだけ
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching)
{
TripsSpeedLPF005MMDao.Insert(tripsTable);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching)
{
TripsMMDao.Insert(tripsTable);
}
else if (correction == InsertConfig.GpsCorrection.Normal)
{
TripsDao.Insert(tripsTable);
}
else if (correction == InsertConfig.GpsCorrection.DopplerSpeed)
{
if (tripsTable.Rows.Count != 0)
{
var gpsCorrectedTable = CorrectedGPSDopplerDao.GetNormalized(
tripsTable.Rows[0].Field <DateTime>(TripsDopplerDao.ColumnStartTime),
tripsTable.Rows[0].Field <DateTime>(TripsDopplerDao.ColumnEndTime),
datum);
if (gpsCorrectedTable.Rows.Count != 0)
{
TripsDopplerDao.Insert(tripsTable);
}
}
}
else if (correction == InsertConfig.GpsCorrection.DopplerNotMM)
{
if (tripsTable.Rows.Count != 0)
{
var gpsCorrectedTable = CorrectedGpsDopplerNotMMDao.GetNormalized(
tripsTable.Rows[0].Field <DateTime>(TripsDopplerDao.ColumnStartTime),
tripsTable.Rows[0].Field <DateTime>(TripsDopplerDao.ColumnEndTime),
datum);
if (gpsCorrectedTable.Rows.Count != 0)
{
TripsDopplerNotMMDao.Insert(tripsTable);
}
}
}
}
}
/*
* InsertSightSeeingTrip()
* 観光用トリップを挿入するためのメソッド
* YNU出発と観光地出発の2種類があることに注意が必要になる。
*/
private static void InsertSightSeeingTrip(DataTable tripsRawTable, DataTable tripsTable, InsertDatum datum, int startIndex, InsertConfig.GpsCorrection correction)
{
// InsertHomewardTripと同じような処理を記述
// tripのスタートのtripsRawのインデックスをstartIndex
// 現在注目しているtripsRawのインデックスをcurrentIndex
int currentIndex = startIndex;
// tripChangeFlagはループを抜けるためのフラグ
// このフラグが立つと、異なるトリップに到達したことを示す
bool tripChangeFlag = false;
// TripsRawを結合してTripsを生成する。
while (currentIndex < tripsRawTable.Rows.Count && tripChangeFlag == false)
{
// スタートがynuか観光地 かつ ゴールがynuか観光地
// でもynuからynuのトリップは考えない
// スタート,ゴールがynuであるか
bool isStartYnu = IsYnu(tripsRawTable.Rows[startIndex].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[startIndex].Field <double>(TripsRawDao.ColumnStartLongitude));
bool isEndYnu = IsYnu(tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnEndLatitude),
tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnEndLongitude));
// スタートゴールが観光地であるか
bool isStartSightseeingSpot = IsSightseeing(tripsRawTable.Rows[startIndex].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[startIndex].Field <double>(TripsRawDao.ColumnStartLongitude));
bool isEndSightseeingSpot = IsSightseeing(tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnEndLatitude),
tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnEndLongitude));
// スタートゴールが自宅であるか
bool isStartHome = IsHome(tripsRawTable.Rows[startIndex].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[startIndex].Field <double>(TripsRawDao.ColumnStartLongitude),
tripsRawTable.Rows[startIndex].Field <DateTime>(TripsRawDao.ColumnStartTime),
datum);
bool isEndHome = IsHome(tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnEndLatitude),
tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnEndLongitude),
tripsRawTable.Rows[currentIndex].Field <DateTime>(TripsRawDao.ColumnEndTime),
datum);
// YNU to YNUは排除
if (isStartYnu && isEndYnu)
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "YNU⇒YNUトリップなので挿入しません。"
+ ConvertRowToString(tripsRawTable.Rows[startIndex],
tripsRawTable.Rows[currentIndex]));
tripChangeFlag = true;
}
// 自宅 to 自宅 も排除
else if (isStartHome && isEndHome)
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "Home⇒Homeトリップなので挿入しません。"
+ ConvertRowToString(tripsRawTable.Rows[startIndex],
tripsRawTable.Rows[currentIndex]));
tripChangeFlag = true;
}
// 候補地 to 候補地のトリップを挿入
else if ((isStartYnu || isStartSightseeingSpot || isStartHome) &&
(isEndYnu || isEndSightseeingSpot || isEndHome))
{
var row = tripsTable.NewRow();
row.SetField(TripsDao.ColumnTripId, GetMaxTripId(correction));
row.SetField(TripsDao.ColumnDriverId, tripsRawTable.Rows[startIndex].Field <int>(TripsRawDao.ColumnDriverId));
row.SetField(TripsDao.ColumnCarId, tripsRawTable.Rows[startIndex].Field <int>(TripsRawDao.ColumnCarId));
row.SetField(TripsDao.ColumnSensorId, tripsRawTable.Rows[startIndex].Field <int>(TripsRawDao.ColumnSensorId));
row.SetField(TripsDao.ColumnStartTime, tripsRawTable.Rows[startIndex].Field <DateTime>(TripsRawDao.ColumnStartTime));
row.SetField(TripsDao.ColumnEndTime, tripsRawTable.Rows[currentIndex].Field <DateTime>(TripsRawDao.ColumnEndTime));
row.SetField(TripsDao.ColumnStartLatitude, tripsRawTable.Rows[startIndex].Field <double>(TripsRawDao.ColumnStartLatitude));
row.SetField(TripsDao.ColumnStartLongitude, tripsRawTable.Rows[startIndex].Field <double>(TripsRawDao.ColumnStartLongitude));
row.SetField(TripsDao.ColumnEndLatitude, tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnEndLatitude));
row.SetField(TripsDao.ColumnEndLongitude, tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnEndLongitude));
row.SetField(TripsDao.ColumnTripDirection, "tourism");
TimeSpan span = tripsRawTable.Rows[currentIndex].Field <DateTime>(TripsRawDao.ColumnEndTime)
- tripsRawTable.Rows[startIndex].Field <DateTime>(TripsRawDao.ColumnStartTime);
if (span.TotalHours > 12)
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "別々のトリップを結合する可能性があるので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[startIndex],
tripsRawTable.Rows[currentIndex]));
break;
}
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching &&
!TripsSpeedLPF005MMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching &&
!TripsMMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.Normal &&
!TripsDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.DopplerSpeed &&
!TripsDopplerDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.DopplerNotMM && !TripsDopplerNotMMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "既にこのトリップは挿入されているので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[startIndex],
tripsRawTable.Rows[currentIndex]));
}
tripChangeFlag = true;
}
currentIndex++;
if (currentIndex == tripsRawTable.Rows.Count || tripChangeFlag)
{
break;
}
// YNUにも観光地にも自宅にも到着しないまま、開始地点がYNUか観光地か自宅になった場合
if (IsYnu(tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnStartLongitude)) ||
IsSightseeing(tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnStartLongitude),
tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnStartLongitude)) ||
IsHome(tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnStartLongitude),
tripsRawTable.Rows[currentIndex].Field <DateTime>(TripsRawDao.ColumnStartTime),
datum)
)
{
tripChangeFlag = true;
LogWritter.WriteLog(LogWritter.LogMode.Trip, "YNUor自宅or観光地⇒？トリップなので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[startIndex], tripsRawTable.Rows[currentIndex]));
}
}
}
public Tuple <string, double?> MatchLink(double latitude, double longitude, Single
heading, string direction,
InsertDatum datum)
{
//System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
//sw.Start();
// 探索コスト削減のため、Link ID と 道路勾配をいっぺんに返す
int multiple = 10000; //緯度経度整数に変換用
string linkId = null;
double?roadTheta = null;
DataRow[] selectedRows = null;
int intlatitude = Convert.ToInt32(latitude * multiple);
int intlongitude = Convert.ToInt32(longitude * multiple);
#region リンクマッチング
// TODO 以下のアルゴリズムを見直すとけっこうな高速化が計れそう
// TODO 今すごくいい案思いついた、しかもデータベース屋っぽい案
// TODO latitude, longitudeを引数とするテーブル関数定義して、
// TODO 中身はじつは緯度、経度からリンクを返すルックアップテーブル
// TODO これはけっこうクール
if (datum.DriverId != 4 && datum.DriverId != 9)
{
if (direction == "outward")
{
selectedRows = _outwardHighwaySemanticLinkTable
.AsEnumerable()
.Where(row => Math.Abs(row.Field <double>(2) - latitude) < 0.002 && Math.Abs(row.Field <double>(3) - longitude) < 0.002)
.ToArray();
if (selectedRows.Length != 0)
{
linkId = SelectLink(latitude, longitude, heading, selectedRows);
}
}
else if (direction == "homeward")
{
selectedRows = _homewardHighwaySemanticLinkTable
.AsEnumerable()
.Where(row => Math.Abs(row.Field <double>(2) - latitude) < 0.002 && Math.Abs(row.Field <double>(3) - longitude) < 0.002)
.ToArray();
if (selectedRows.Length != 0)
{
linkId = SelectLink(latitude, longitude, heading, selectedRows);
}
}
if (selectedRows == null || selectedRows.Length == 0)
{
selectedRows = _semanticLinkTable
.AsEnumerable()
.Where(row => Math.Abs(row.Field <double>(2) - latitude) < 0.0001 && Math.Abs(row.Field <double>(3) - longitude) < 0.0001)
.ToArray();
if (selectedRows.Length != 0)
{
linkId = SelectLink(latitude, longitude, heading, selectedRows);
}
}
//if (selectedRows.Length == 0)
//{
// selectedRows = _linkTable
// .AsEnumerable()
// .Where(row => Math.Abs(row.Field<double>(1) - latitude) < 0.002 && Math.Abs(row.Field<double>(2) - longitude) < 0.002)
// .ToArray();
// if (selectedRows.Length != 0)
// linkId = SelectLink(latitude, longitude, heading, selectedRows);
//}
if (selectedRows.Length == 0)
{
selectedRows = LinksForSearchDao.GetLinkId(intlatitude, intlongitude).Select();
if (selectedRows.Length != 0)
{
linkId = SelectLink(latitude, longitude, heading, selectedRows);
}
}
}
#endregion
if (linkId != null)
{
var linkDetail = LinkDetailDao.Get(linkId);
if (linkDetail.Rows.Count == 1)
{
Single linkHeading = linkDetail.Rows[0].Field <Single>("road_heading");
Single carHeading = heading;
double angle = Math.Abs(linkHeading - carHeading);
if (angle < 90 || angle > 270)
{
roadTheta = linkDetail.Rows[0].Field <Single>("road_theta");
}
else
{
roadTheta = -linkDetail.Rows[0].Field <Single>("road_theta");
}
}
}
//sw.Stop();
//LogWritter.WriteLog(LogWritter.LogMode.Elapsedtime, "One Link Match Calculaton Time:" + sw.Elapsed);
return(new Tuple <string, double?>(linkId, roadTheta));
}
public static void InsertCorrectedGps(InsertDatum datum, InsertConfig.GpsCorrection correction)
{
var tripsTable = TripsDao.Get(datum);
foreach (DataRow tripsRow in tripsTable.Rows)
{
DataTable gpsRawTable =
AndroidGpsRawDao.Get(tripsRow.Field <DateTime>(TripsDao.ColumnStartTime),
tripsRow.Field <DateTime>(TripsDao.ColumnEndTime), datum);
if (gpsRawTable.Rows.Count == 0)
{
return;
}
DataTable correctedGpsTable = DataTableUtil.GetCorrectedGpsTable();
#region インデックスが 0 の場合
DataRow firstRow = correctedGpsTable.NewRow();
CopyRawDataToCorrectedRow(firstRow, gpsRawTable.Rows[0]);
firstRow.SetField(CorrectedGpsDao.ColumnDistanceDifference, 0);
//firstRow.SetField(CorrectedGpsDao.ColumnSpeed, 0);
firstRow.SetField(CorrectedGpsDao.ColumnHeading, 0);
var meshAndAltitude = AltitudeCalculator.GetInstance().CalcAltitude(
gpsRawTable.Rows[0].Field <double>(CorrectedGpsDao.ColumnLatitude),
gpsRawTable.Rows[0].Field <double>(CorrectedGpsDao.ColumnLongitude));
firstRow.SetField(CorrectedGpsDao.ColumnTerrainAltitude, meshAndAltitude.Item2);
var linkAndTheta = LinkMatcher.GetInstance().MatchLink(
firstRow.Field <double>(CorrectedGpsDao.ColumnLatitude),
firstRow.Field <double>(CorrectedGpsDao.ColumnLongitude),
0f, tripsRow.Field <string>(TripsDao.ColumnTripDirection), datum);
firstRow.SetField(CorrectedGpsDao.ColumnLinkId, linkAndTheta.Item1);
firstRow.SetField(CorrectedGpsDao.ColumnRoadTheta, linkAndTheta.Item2);
correctedGpsTable.Rows.Add(firstRow);
#endregion
for (int i = 1; i < gpsRawTable.Rows.Count - 1; i++)
{
DataRow row = correctedGpsTable.NewRow();
CopyRawDataToCorrectedRow(row, gpsRawTable.Rows[i]);
// 距離の算出
row.SetField(CorrectedGpsDao.ColumnDistanceDifference, DistanceCalculator.CalcDistance(
gpsRawTable.Rows[i - 1].Field <double>(AndroidGpsRawDao.ColumnLatitude),
gpsRawTable.Rows[i - 1].Field <double>(AndroidGpsRawDao.ColumnLongitude),
gpsRawTable.Rows[i].Field <double>(AndroidGpsRawDao.ColumnLatitude),
gpsRawTable.Rows[i].Field <double>(AndroidGpsRawDao.ColumnLongitude)));
meshAndAltitude = AltitudeCalculator.GetInstance().CalcAltitude(
gpsRawTable.Rows[i].Field <double>(CorrectedGpsDao.ColumnLatitude),
gpsRawTable.Rows[i].Field <double>(CorrectedGpsDao.ColumnLongitude));
row.SetField(CorrectedGpsDao.ColumnTerrainAltitude, meshAndAltitude.Item2);
// 速度の算出
//row.SetField(CorrectedGpsDao.ColumnSpeed, SpeedCalculator.CalcSpeed(
// gpsRawTable.Rows[i - 1].Field<double>(AndroidGpsRawDao.ColumnLatitude),
// gpsRawTable.Rows[i - 1].Field<double>(AndroidGpsRawDao.ColumnLongitude),
// gpsRawTable.Rows[i - 1].Field<DateTime>(AndroidGpsRawDao.ColumnJst),
// gpsRawTable.Rows[i + 1].Field<double>(AndroidGpsRawDao.ColumnLatitude),
// gpsRawTable.Rows[i + 1].Field<double>(AndroidGpsRawDao.ColumnLongitude),
// gpsRawTable.Rows[i + 1].Field<DateTime>(AndroidGpsRawDao.ColumnJst),
// gpsRawTable.Rows[i].Field<double>(AndroidGpsRawDao.ColumnLatitude),
// gpsRawTable.Rows[i].Field<double>(AndroidGpsRawDao.ColumnLongitude)));
//速度が1km以上になったらHEADINGを更新する(停止時に1つ1つ計算するとHEADINDが暴れるため)
if (row.Field <Single?>(CorrectedGpsDao.ColumnSpeed) > 1.0)
{
row.SetField(CorrectedGpsDao.ColumnHeading, HeadingCalculator.CalcHeading(
gpsRawTable.Rows[i - 1].Field <double>(AndroidGpsRawDao.ColumnLatitude),
gpsRawTable.Rows[i - 1].Field <double>(AndroidGpsRawDao.ColumnLongitude),
gpsRawTable.Rows[i].Field <double>(AndroidGpsRawDao.ColumnLatitude),
gpsRawTable.Rows[i].Field <double>(AndroidGpsRawDao.ColumnLongitude)));
}
else
{
row.SetField(CorrectedGpsDao.ColumnHeading, correctedGpsTable.Rows[i - 1].Field <double>(CorrectedGpsDao.ColumnHeading));
}
linkAndTheta = LinkMatcher.GetInstance().MatchLink(
row.Field <double>(CorrectedGpsDao.ColumnLatitude),
row.Field <double>(CorrectedGpsDao.ColumnLongitude),
Convert.ToSingle(row.Field <double>(CorrectedGpsDao.ColumnHeading))
, tripsRow.Field <string>(TripsDao.ColumnTripDirection), datum
);
row.SetField(CorrectedGpsDao.ColumnLinkId, linkAndTheta.Item1);
row.SetField(CorrectedGpsDao.ColumnRoadTheta, linkAndTheta.Item2);
correctedGpsTable.Rows.Add(row);
}
#region インデックスが最後の場合
DataRow lastRow = correctedGpsTable.NewRow();
CopyRawDataToCorrectedRow(lastRow, gpsRawTable.Rows[gpsRawTable.Rows.Count - 1]);
lastRow.SetField(CorrectedGpsDao.ColumnDistanceDifference, 0);
lastRow.SetField(CorrectedGpsDao.ColumnSpeed, 0);
lastRow.SetField(CorrectedGpsDao.ColumnHeading, 0);
meshAndAltitude = AltitudeCalculator.GetInstance().CalcAltitude(
gpsRawTable.Rows[gpsRawTable.Rows.Count - 1].Field <double>(CorrectedGpsDao.ColumnLatitude),
gpsRawTable.Rows[gpsRawTable.Rows.Count - 1].Field <double>(CorrectedGpsDao.ColumnLongitude));
lastRow.SetField(CorrectedGpsDao.ColumnTerrainAltitude, meshAndAltitude.Item2);
linkAndTheta = LinkMatcher.GetInstance().MatchLink(
firstRow.Field <double>(CorrectedGpsDao.ColumnLatitude),
firstRow.Field <double>(CorrectedGpsDao.ColumnLongitude),
0f, tripsRow.Field <string>(TripsDao.ColumnTripDirection), datum);
lastRow.SetField(CorrectedGpsDao.ColumnLinkId, linkAndTheta.Item1);
lastRow.SetField(CorrectedGpsDao.ColumnRoadTheta, linkAndTheta.Item2);
correctedGpsTable.Rows.Add(lastRow);
#endregion
// ファイルごとの挿入なので主キー違反があっても挿入されないだけ
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching)//速度にローパスフィルタを適用
{
DataTable correctedGpsSpeedLPFTable = LowPassFilter.speedLowPassFilter(correctedGpsTable, 0.05);
CorrectedGpsSpeedLPF005MMDao.Insert(correctedGpsSpeedLPFTable);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching)
{
CorrectedGPSMMDao.Insert(correctedGpsTable);
}
else
{
CorrectedGpsDao.Insert(correctedGpsTable);
}
}
}
public static DataTable GetAccurateAccBreakingRaw(DateTime startTime, DateTime endTime, InsertDatum datum)
{
string query = "with LOW_SPEED as ";
query += "( ";
query += " select g1.DRIVER_ID,g1.SENSOR_ID,g1.JST ";
query += " from CORRECTED_GPS g1,CORRECTED_GPS g2 ";
query += " where g1.DRIVER_ID = "+ datum.DriverId + " ";
query += " and g1.SENSOR_ID = "+ datum.SensorId + " ";
query += " and g1.SPEED < 10 ";
query += " and g2.DRIVER_ID = "+ datum.DriverId + " ";
query += " and g2.SENSOR_ID = "+ datum.SensorId + " ";
query += " and g2.SPEED > 10 ";
query += " and g1.JST = DATEADD(second,1,g2.JST) ";
query += " and g1.JST > '"+ startTime + "' ";
query += " and g1.JST < '"+ endTime + "' ";
query += ") ";
query += "select * ";
query += "from LOW_SPEED ";
query += "except ";
query += "select LOW_SPEED.DRIVER_ID,LOW_SPEED.SENSOR_ID,CONVERT(varchar,LOW_SPEED.JST,121) as JST ";
query += "from CORRECTED_GPS,LOW_SPEED ";
query += "where CORRECTED_GPS.DRIVER_ID = LOW_SPEED.DRIVER_ID ";
query += "and CORRECTED_GPS.SENSOR_ID = LOW_SPEED.SENSOR_ID ";
query += "and CORRECTED_GPS.JST < LOW_SPEED.JST ";
query += "and CORRECTED_GPS.JST >= DATEADD(second,-10,LOW_SPEED.JST) ";
query += "and SPEED < 10 ";
query += "group by LOW_SPEED.DRIVER_ID,LOW_SPEED.SENSOR_ID,LOW_SPEED.JST ";
return(DatabaseAccesser.GetResult(query));
}
//TRIP_DIRECTION = 'other' のインサート
private static void InsertOtherTrip(DataTable tripsRawTable, DataTable tripsTable, InsertDatum datum, int i, InsertConfig.GpsCorrection correction)
{
int j = i;
bool tripChangeFlag = false;
// TripsRaw を結合して Trips を生成するループ
while (j < tripsRawTable.Rows.Count && tripChangeFlag == false)
{
// その他登録地点 ⇒ その他登録地点
if (IsOther(tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude), tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude)))
{
var row = tripsTable.NewRow();
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching)
{
row.SetField(TripsDao.ColumnTripId, TripsSpeedLPF005MMDao.GetMaxTripId() + 1);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching)
{
row.SetField(TripsDao.ColumnTripId, TripsMMDao.GetMaxTripId() + 1);
}
else
{
row.SetField(TripsDao.ColumnTripId, TripsDao.GetMaxTripId() + 1);
}
row.SetField(TripsDao.ColumnDriverId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnDriverId));
row.SetField(TripsDao.ColumnCarId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnCarId));
row.SetField(TripsDao.ColumnSensorId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnSensorId));
row.SetField(TripsDao.ColumnStartTime, tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime));
row.SetField(TripsDao.ColumnEndTime, tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime));
row.SetField(TripsDao.ColumnStartLatitude, tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLatitude));
row.SetField(TripsDao.ColumnStartLongitude, tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLongitude));
row.SetField(TripsDao.ColumnEndLatitude, tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude));
row.SetField(TripsDao.ColumnEndLongitude, tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude));
row.SetField(TripsDao.ColumnTripDirection, "other");
TimeSpan span = tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime)
- tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime);
if (span.TotalHours > 12)
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "別々のトリップを結合する可能性があるので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
break;
}
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching && !TripsSpeedLPF005MMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching && !TripsMMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.Normal && !TripsDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "既にこのトリップは挿入されているので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
}
tripChangeFlag = true;
}
// その他登録地点 ⇒ YNU
if (IsYnu(tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude), tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude)))
{
var row = tripsTable.NewRow();
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching)
{
row.SetField(TripsDao.ColumnTripId, TripsSpeedLPF005MMDao.GetMaxTripId() + 1);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching)
{
row.SetField(TripsDao.ColumnTripId, TripsMMDao.GetMaxTripId() + 1);
}
else
{
row.SetField(TripsDao.ColumnTripId, TripsDao.GetMaxTripId() + 1);
}
row.SetField(TripsDao.ColumnDriverId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnDriverId));
row.SetField(TripsDao.ColumnCarId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnCarId));
row.SetField(TripsDao.ColumnSensorId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnSensorId));
row.SetField(TripsDao.ColumnStartTime, tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime));
row.SetField(TripsDao.ColumnEndTime, tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime));
row.SetField(TripsDao.ColumnStartLatitude, tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLatitude));
row.SetField(TripsDao.ColumnStartLongitude, tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLongitude));
row.SetField(TripsDao.ColumnEndLatitude, tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude));
row.SetField(TripsDao.ColumnEndLongitude, tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude));
row.SetField(TripsDao.ColumnTripDirection, "other");
TimeSpan span = tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime)
- tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime);
if (span.TotalHours > 12)
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "別々のトリップを結合する可能性があるので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
break;
}
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching && !TripsSpeedLPF005MMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching && !TripsMMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.Normal && !TripsDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "既にこのトリップは挿入されているので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
}
tripChangeFlag = true;
}
//その他登録地点 ⇒ 自宅
else if (IsHome(tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude),
tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude),
tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime),
datum))
{
var row = tripsTable.NewRow();
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching)
{
row.SetField(TripsDao.ColumnTripId, TripsSpeedLPF005MMDao.GetMaxTripId() + 1);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching)
{
row.SetField(TripsDao.ColumnTripId, TripsMMDao.GetMaxTripId() + 1);
}
else
{
row.SetField(TripsDao.ColumnTripId, TripsDao.GetMaxTripId() + 1);
}
row.SetField(TripsDao.ColumnDriverId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnDriverId));
row.SetField(TripsDao.ColumnCarId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnCarId));
row.SetField(TripsDao.ColumnSensorId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnSensorId));
row.SetField(TripsDao.ColumnStartTime, tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime));
row.SetField(TripsDao.ColumnEndTime, tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime));
row.SetField(TripsDao.ColumnStartLatitude, tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLatitude));
row.SetField(TripsDao.ColumnStartLongitude, tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLongitude));
row.SetField(TripsDao.ColumnEndLatitude, tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude));
row.SetField(TripsDao.ColumnEndLongitude, tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude));
row.SetField(TripsDao.ColumnTripDirection, "other");
TimeSpan span = tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime)
- tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime);
if (span.TotalHours > 12)
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "別々のトリップを結合する可能性があるので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
break;
}
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching && !TripsSpeedLPF005MMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching && !TripsMMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.Normal && !TripsDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "既にこのトリップは挿入されているので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
}
tripChangeFlag = true;
}
j++;
}
}
public static DataTable getResultMapMatchingDoppler(DataTable gpsRawTable, InsertDatum datum)
{
if (gpsRawTable.Rows.Count == 0)
{
return(new DataTable());
}
List <DataTable> dt = new List <DataTable>();
if (datum.DriverId == 1)//被験者１用のマップマッチング道路リンクを取得
{
int[] id = new int[] { 220 };
DataTable tempTable = LinkDao.GetLinkTableforMM(id);
tempTable.TableName = "復路,代官山下ルート";
dt.Add(tempTable);
dt[0].DefaultView.Sort = "START_LAT, START_LONG";
id = new int[] { 224 };
tempTable = LinkDao.GetLinkTableforMM(id);
tempTable.TableName = "復路,代官山上ルート";
dt.Add(tempTable);
dt[1].DefaultView.Sort = "START_LAT, START_LONG";
id = new int[] { 221 };
tempTable = LinkDao.GetLinkTableforMM(id);
tempTable.TableName = "往路,小学校上ルート";
dt.Add(tempTable);
dt[2].DefaultView.Sort = "START_LAT, START_LONG";
id = new int[] { 225 };
tempTable = LinkDao.GetLinkTableforMM(id);
tempTable.TableName = "往路,小学校下ルート";
dt.Add(tempTable);
dt[3].DefaultView.Sort = "START_LAT, " +
"" +
"" +
"" +
"" +
"" +
"" +
"" +
"" +
"START_LONG";
}
else if (datum.DriverId == 17)//マップマッチング道路リンクを取得
{
int[] id = new int[] { 328 };
DataTable tempTable = LinkDao.GetLinkTableforMM(id);
tempTable.TableName = "被験者17復路ルート１";
dt.Add(tempTable);
dt[0].DefaultView.Sort = "START_LAT, START_LONG";
id = new int[] { 329 };
tempTable = LinkDao.GetLinkTableforMM(id);
tempTable.TableName = "被験者17往路ルート1";
dt.Add(tempTable);
dt[1].DefaultView.Sort = "START_LAT, START_LONG";
id = new int[] { 330 };
tempTable = LinkDao.GetLinkTableforMM(id);
tempTable.TableName = "被験者17往路ルート2";
dt.Add(tempTable);
dt[2].DefaultView.Sort = "START_LAT, START_LONG";
id = new int[] { 331 };
tempTable = LinkDao.GetLinkTableforMM(id);
tempTable.TableName = "被験者17往路ルート2";
dt.Add(tempTable);
dt[2].DefaultView.Sort = "START_LAT, START_LONG";
id = new int[] { 340 };
tempTable = LinkDao.GetLinkTableforMM(id);
tempTable.TableName = "被験者17往路ルート3";
dt.Add(tempTable);
dt[2].DefaultView.Sort = "START_LAT, START_LONG";
id = new int[] { 341 };
tempTable = LinkDao.GetLinkTableforMM(id);
tempTable.TableName = "被験者17復路ルート3";
dt.Add(tempTable);
dt[2].DefaultView.Sort = "START_LAT, START_LONG";
}
else if (datum.DriverId == 28)
{
int[] id = new int[] { 365 };
DataTable tempTable = LinkDao.GetLinkTableforMM(id);
tempTable = LinkDao.GetLinkTableforMM(id);
tempTable.TableName = "被験者28 往路ルート1";
dt.Add(tempTable);
dt[0].DefaultView.Sort = "START_LAT, START_LONG";
id = new int[] { 366 };
tempTable = LinkDao.GetLinkTableforMM(id);
tempTable.TableName = "被験者28 復路ルート1";
dt.Add(tempTable);
dt[1].DefaultView.Sort = "START_LAT, START_LONG";
id = new int[] { 369 };
tempTable = LinkDao.GetLinkTableforMM(id);
tempTable.TableName = "被験者28 往路ルート2";
dt.Add(tempTable);
dt[2].DefaultView.Sort = "START_LAT, START_LONG";
id = new int[] { 370 };
tempTable = LinkDao.GetLinkTableforMM(id);
tempTable.TableName = "被験者28 復路ルート2";
dt.Add(tempTable);
dt[3].DefaultView.Sort = "START_LAT, START_LONG";
}
//TODO マップマッチング処理
double[] sumDist = new double[dt.Count]; //GPS点をマッチングさせるのに移動させた距離の総和
double[] maxDist = new double[dt.Count]; //GPS点をマッチングさせるのに移動させた距離の最大値
DataTable[] mapMatchedGpsTable = DataTableUtil.GetAndroidGpsRawDopplerTableArray(dt.Count);
for (int i = 0; i < gpsRawTable.Rows.Count; i++)
{
for (int n = 0; n < dt.Count; n++)
{
double tempDist = searchNearestLinkDoppler(dt[n], gpsRawTable.Rows[i], ref mapMatchedGpsTable[n]);
sumDist[n] += tempDist;
if (tempDist > maxDist[n])
{
maxDist[n] = tempDist;
}
}
}
int element = getMinElement(sumDist);
if (sumDist.Length == 0)
{
SlackUtil.noMapMatching(datum, gpsRawTable.Rows[0]);
return(new DataTable());
}
if (sumDist[element] > 0.5 || maxDist[element] > 0.003)
{
SlackUtil.noMapMatching(datum, gpsRawTable.Rows[0]);
return(new DataTable());
}
return(mapMatchedGpsTable[element]);
}
private static void InsertHomewardTrip(DataTable tripsRawTable, DataTable tripsTable, InsertDatum datum, int i, InsertConfig.GpsCorrection correction)
{
int j = i;
bool tripChangeFlag = false;
// TripsRaw を結合して Trips を生成するループ
while (j < tripsRawTable.Rows.Count && tripChangeFlag == false)
{
// YNU ⇒ 自宅
if (IsHome(tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude),
tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude),
tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime),
datum))
{
var row = tripsTable.NewRow();
row.SetField(TripsDao.ColumnTripId, GetMaxTripId(correction));
row.SetField(TripsDao.ColumnDriverId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnDriverId));
row.SetField(TripsDao.ColumnCarId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnCarId));
row.SetField(TripsDao.ColumnSensorId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnSensorId));
row.SetField(TripsDao.ColumnStartTime, tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime));
row.SetField(TripsDao.ColumnEndTime, tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime));
row.SetField(TripsDao.ColumnStartLatitude, tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLatitude));
row.SetField(TripsDao.ColumnStartLongitude, tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLongitude));
row.SetField(TripsDao.ColumnEndLatitude, tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude));
row.SetField(TripsDao.ColumnEndLongitude, tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude));
row.SetField(TripsDao.ColumnTripDirection, "homeward");
TimeSpan span = tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime)
- tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime);
if (span.TotalHours > 12)
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "別々のトリップを結合する可能性があるので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
break;
}
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching && !TripsSpeedLPF005MMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching && !TripsMMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.Normal && !TripsDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.DopplerSpeed && !TripsDopplerDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.DopplerNotMM && !TripsDopplerNotMMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "既にこのトリップは挿入されているので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
}
tripChangeFlag = true;
}
// YNU ⇒ YNU
else if (IsYnu(tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude),
tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude)))
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "YNU⇒YNUトリップなので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
// Trip の挿入は行わない
// ループの初期化
tripChangeFlag = true;
}
j++;
// YNU ⇒ ？
if (j < tripsRawTable.Rows.Count && tripChangeFlag != true)
{
// 自宅にも、学校にも到着しないまま、開始地点が自宅か学校になった場合
if (IsHome(tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnStartLongitude),
tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnStartTime), datum) ||
IsYnu(tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnStartLatitude), tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnStartLongitude)))
{
tripChangeFlag = true;
LogWritter.WriteLog(LogWritter.LogMode.Trip, "YNU⇒？トリップなので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
}
}
}
}
public static DataTable CorrectAcc(DateTime startTime, DateTime endTime, InsertDatum datum, DataRow tripRow)
{
/*** LocationListenerのtimestampとSystem.currentTimeMillis()の差分を計算している
* LocationListenerのtimestampはおそらく、システム時間を参照しているからこの処理は意味ないかも ***/
int millisecTimeDiff = AndroidGpsRawDao.GetMilliSencodTimeDiffBetweenJstAndAndroidTime(
tripRow.Field <DateTime>(TripsDao.ColumnStartTime),
tripRow.Field <DateTime>(TripsDao.ColumnEndTime), datum);
var accRawTable = AndroidAccRawDao.Get(tripRow.Field <DateTime>(TripsDao.ColumnStartTime),
tripRow.Field <DateTime>(TripsDao.ColumnEndTime), millisecTimeDiff, datum);
accRawTable = DataTableUtil.GetTempCorrectedAccTable(accRawTable);
if (accRawTable.Rows.Count == 0)
{
// TODO ログ書き込み
//エラー期間をログファイルに記録
// WriteLog("DRIVER_ID:" + userID.DriverID + "SENSOR_ID:" + userID.SensorID + " " + tripStart + "～" + tripEnd + "加速度データなし", LogMode.acc);
return(null);
}
else if (false) // TODO 挿入済みチェックをすべきか？
{
// WriteLog("DRIVER_ID:" + userID.DriverID + "SENSOR_ID:" + userID.SensorID + " " + tripStart.ToString() + "～" + tripEnd.ToString() + "はすでに挿入済みです\r\n", LogMode.acc);
return(null);
}
// TODO ファイルに記録
//WriteLog("X方向平均(補正前)＝" + dtACC.Compute("AVG(LONGITUDINAL_ACC)", "JST > #" + tripStart + "# AND JST < #" + tripEnd + "#").ToString(), LogMode.acc);
//WriteLog("Y方向平均(補正前)＝" + dtACC.Compute("AVG(LATERAL_ACC)", "JST > #" + tripStart + "# AND JST < #" + tripEnd + "#").ToString(), LogMode.acc);
//WriteLog("Z方向平均(補正前)＝" + dtACC.Compute("AVG(VERTICAL_ACC)", "JST > #" + tripStart + "# AND JST < #" + tripEnd + "#").ToString(), LogMode.acc);
var accurateStoppingAccRawTable = CorrectedAccDao.GetAccurateStoppingAccRaw(millisecTimeDiff, datum);
if (accurateStoppingAccRawTable.Rows.Count == 0)
{
// TODO ログ出力
//WriteLog("DRIVER_ID:" + userID.DriverID + "SENSOR_ID:" + userID.SensorID + " " + tripStart.ToString() + "～" + tripEnd.ToString() + "停止時の加速度データなし or タイムアウトエラーが発生 + \r\n", LogMode.acc);
return(null);
}
// TODO テーブル関数定義の場合、staticフィールドで参照するがよろし
double avgX = double.Parse(accurateStoppingAccRawTable.Rows[0]["ACC_X"].ToString());
double avgY = double.Parse(accurateStoppingAccRawTable.Rows[0]["ACC_Y"].ToString());
double avgZ = double.Parse(accurateStoppingAccRawTable.Rows[0]["ACC_Z"].ToString());
var vector1 = new ThreeDimensionalVector(0, 0, 9.8);
var vector2 = new ThreeDimensionalVector(avgX, avgY, avgZ);
var vectorProduct = new ThreeDimensionalVector(vector1.Y * vector2.Z - vector1.Z * vector2.Y, vector1.Z * vector2.X - vector1.X * vector2.Z, vector1.X * vector2.Y - vector1.Y * vector2.X);
var vasisVP = new ThreeDimensionalVector(vectorProduct.X / MathUtil.CalcVectorAbsoluteValue(vectorProduct),
vectorProduct.Y / MathUtil.CalcVectorAbsoluteValue(vectorProduct),
vectorProduct.Z / MathUtil.CalcVectorAbsoluteValue(vectorProduct));
var angle = Math.Asin(MathUtil.CalcVectorAbsoluteValue(vectorProduct) / (MathUtil.CalcVectorAbsoluteValue(vector1) * MathUtil.CalcVectorAbsoluteValue(vector2)));
// TODO ログ出力
//WriteLog("角度(Z軸補正)=" + angle * 360 / (2 * Math.PI), LogMode.acc);
//WriteLog("回転軸 = (" + vasisVP.X + ", " + vasisVP.Y + ", " + vasisVP.Z + ")", LogMode.acc);
Quaternion p = new Quaternion(0, vector2.X, vector2.Y, vector2.Z);
Quaternion q = new Quaternion(Math.Cos(angle / 2), vasisVP.X * Math.Sin(angle / 2), vasisVP.Y * Math.Sin(angle / 2), vasisVP.Z * Math.Sin(angle / 2));
Quaternion r = new Quaternion(Math.Cos(angle / 2), -vasisVP.X * Math.Sin(angle / 2), -vasisVP.Y * Math.Sin(angle / 2), -vasisVP.Z * Math.Sin(angle / 2));
Quaternion rp = MathUtil.MultiplyQuaternion(r, p);
Quaternion rpq = MathUtil.MultiplyQuaternion(rp, q);
for (int i = 0; i < accRawTable.Rows.Count; i++)
{
Quaternion p1 = new Quaternion(0,
accRawTable.Rows[i].Field <Single>("LONGITUDINAL_ACC"),
accRawTable.Rows[i].Field <Single>("LATERAL_ACC"),
accRawTable.Rows[i].Field <Single>("VERTICAL_ACC"));
Quaternion rp1 = MathUtil.MultiplyQuaternion(r, p1);
Quaternion rpq1 = MathUtil.MultiplyQuaternion(rp1, q);
// TODO SetFieldに書き直し、する価値ないか
accRawTable.Rows[i]["LONGITUDINAL_ACC"] = Single.Parse(rpq1.X.ToString());
accRawTable.Rows[i]["LATERAL_ACC"] = Single.Parse(rpq1.Y.ToString());
accRawTable.Rows[i]["VERTICAL_ACC"] = Single.Parse(rpq1.Z.ToString());
accRawTable.Rows[i]["ALPHA"] = (Single)(angle * 360 / (2 * Math.PI));
accRawTable.Rows[i]["VECTOR_X"] = (Single)vasisVP.X;
accRawTable.Rows[i]["VECTOR_Y"] = (Single)vasisVP.Y;
accRawTable.Rows[i]["ROLL"] = DBNull.Value;
accRawTable.Rows[i]["PITCH"] = DBNull.Value;
accRawTable.Rows[i]["YAW"] = DBNull.Value;
}
// TODO ログ出力
//WriteLog("X方向平均(補正後)＝" + dtACC.Compute("AVG(LONGITUDINAL_ACC)", "JST > #" + tripStart + "# AND JST < #" + tripEnd + "#").ToString(), LogMode.acc);
//WriteLog("Y方向平均(補正後)＝" + dtACC.Compute("AVG(LATERAL_ACC)", "JST > #" + tripStart + "# AND JST < #" + tripEnd + "#").ToString(), LogMode.acc);
//WriteLog("Z方向平均(補正後)＝" + dtACC.Compute("AVG(VERTICAL_ACC)", "JST > #" + tripStart + "# AND JST < #" + tripEnd + "#").ToString() + "\r\n", LogMode.acc);
var tableBreaking = CorrectedAccDao.GetAccurateAccBreakingRaw(tripRow.Field <DateTime>(TripsDao.ColumnStartTime),
tripRow.Field <DateTime>(TripsDao.ColumnEndTime),
datum);
double xsum = 0;
double ysum = 0;
int count = 0;
for (int i = 0; i < tableBreaking.Rows.Count; i++)
{
DateTime end = DateTime.Parse(tableBreaking.Rows[i]["JST"].ToString());
DateTime start = end.AddSeconds(-10);
DataRow[] rows = accRawTable.Select("JST >= #" + start + "# AND JST <= #" + end + "#");
foreach (DataRow row in rows)
{
xsum = xsum + double.Parse(row["LONGITUDINAL_ACC"].ToString());
ysum = ysum + double.Parse(row["LATERAL_ACC"].ToString());
count++;
}
}
if (count == 0)
{
// TODO ログ出力
//WriteLog("DRIVER_ID:" + userID.DriverID + "SENSOR_ID:" + userID.SensorID + " " + "減速データがありません\r\n", LogMode.acc);
return(null);
}
double bx = xsum / count;
double by = ysum / count;
var v3 = new ThreeDimensionalVector(-1, 0, 0);
var v4 = new ThreeDimensionalVector(bx, by, 0);
double innerProduct = v3.X * v4.X + v3.Y * v4.Y;
double cos = innerProduct / (MathUtil.CalcVectorAbsoluteValue(v3) * MathUtil.CalcVectorAbsoluteValue(v4));
double anglexy = Math.Acos(cos);//ラジアン(0～180)
for (int j = 0; j < accRawTable.Rows.Count; j++)
{
double x = double.Parse(accRawTable.Rows[j]["LONGITUDINAL_ACC"].ToString());
double y = double.Parse(accRawTable.Rows[j]["LATERAL_ACC"].ToString());
double z = double.Parse(accRawTable.Rows[j]["VERTICAL_ACC"].ToString());
double x2;
double y2;
double z2;
if (by >= 0)
{
x2 = Math.Cos(anglexy) * x - Math.Sin(anglexy) * y;
y2 = Math.Sin(anglexy) * x + Math.Cos(anglexy) * y;
z2 = z;
}
else
{
x2 = Math.Cos(-anglexy) * x - Math.Sin(-anglexy) * y;
y2 = Math.Sin(-anglexy) * x + Math.Cos(-anglexy) * y;
z2 = z;
}
accRawTable.Rows[j]["LONGITUDINAL_ACC"] = Single.Parse(x2.ToString());
accRawTable.Rows[j]["LATERAL_ACC"] = Single.Parse(y2.ToString());
accRawTable.Rows[j]["VERTICAL_ACC"] = Single.Parse(z2.ToString());
accRawTable.Rows[j]["BETA"] = (Single)(anglexy * 360 / (2 * Math.PI));
}
avgX = double.Parse(accRawTable.Compute("AVG(LONGITUDINAL_ACC)", "JST > #" + tripRow.Field <DateTime>(TripsDao.ColumnStartTime) + "# AND JST < #" + tripRow.Field <DateTime>(TripsDao.ColumnEndTime) + "#").ToString());
avgZ = double.Parse(accRawTable.Compute("AVG(VERTICAL_ACC)", "JST > #" + tripRow.Field <DateTime>(TripsDao.ColumnStartTime) + "# AND JST < #" + tripRow.Field <DateTime>(TripsDao.ColumnEndTime) + "#").ToString());
//WriteLog("平均値 x = " + v4.X + ", y = " + v4.Y, LogMode.acc);
//WriteLog("XY回転角度＝" + "xy = " + anglexy * 360 / (2 * Math.PI), LogMode.acc);
//WriteLog("X方向平均(補正後)＝" + averagex.ToString(), LogMode.acc);
//WriteLog("Y方向平均(補正後)＝" + (double.Parse(dtACC.Compute("AVG(LATERAL_ACC)", "JST > #" + tripStart + "# AND JST < #" + tripEnd + "#").ToString())).ToString(), LogMode.acc);
//WriteLog("Z方向平均(補正後)＝" + averagez.ToString() + "\r\n", LogMode.acc);
avgX = double.Parse(accRawTable.Compute("AVG(LONGITUDINAL_ACC)", "JST > #" + tripRow.Field <DateTime>(TripsDao.ColumnStartTime) + "# AND JST < #" + tripRow.Field <DateTime>(TripsDao.ColumnEndTime) + "#").ToString());
var v5 = new ThreeDimensionalVector(0, 0, 9.8);
var v6 = new ThreeDimensionalVector(avgX, 0, avgZ);
innerProduct = v5.X * v6.X + v5.Z * v6.Z;
cos = innerProduct / (MathUtil.CalcVectorAbsoluteValue(v5) * MathUtil.CalcVectorAbsoluteValue(v6));
anglexy = Math.Acos(cos);//ラジアン(0～180)
for (int j = 0; j < accRawTable.Rows.Count; j++)
{
double x = double.Parse(accRawTable.Rows[j]["LONGITUDINAL_ACC"].ToString());
double y = double.Parse(accRawTable.Rows[j]["LATERAL_ACC"].ToString());
double z = double.Parse(accRawTable.Rows[j]["VERTICAL_ACC"].ToString());
double x2;
double y2;
double z2;
if (avgX >= 0)
{
x2 = Math.Cos(anglexy) * x - Math.Sin(anglexy) * z;
y2 = y;
z2 = Math.Sin(anglexy) * x + Math.Cos(anglexy) * z;
}
else
{
x2 = Math.Cos(-anglexy) * x - Math.Sin(-anglexy) * z;
y2 = y;
z2 = Math.Sin(-anglexy) * x + Math.Cos(-anglexy) * z;
}
accRawTable.Rows[j]["LONGITUDINAL_ACC"] = Single.Parse(x2.ToString());
accRawTable.Rows[j]["LATERAL_ACC"] = Single.Parse(y2.ToString());
accRawTable.Rows[j]["VERTICAL_ACC"] = Single.Parse(z2.ToString());
accRawTable.Rows[j]["GAMMA"] = (Single)(anglexy * 360 / (2 * Math.PI));
}
// TODO ログ出力
//WriteLog("平均値 x = " + v6.X + ", z = " + v6.Z, LogMode.acc);
//WriteLog("XZ回転角度＝" + "xz = " + anglexy * 360 / (2 * Math.PI), LogMode.acc);
//WriteLog("X方向平均(補正後)＝" + double.Parse(dtACC.Compute("AVG(LONGITUDINAL_ACC)", "JST > #" + tripStart + "# AND JST < #" + tripEnd + "#").ToString()).ToString(), LogMode.acc);
//WriteLog("Y方向平均(補正後)＝" + double.Parse(dtACC.Compute("AVG(LATERAL_ACC)", "JST > #" + tripStart + "# AND JST < #" + tripEnd + "#").ToString()).ToString(), LogMode.acc);
//WriteLog("Z方向平均(補正後)＝" + double.Parse(dtACC.Compute("AVG(VERTICAL_ACC)", "JST > #" + tripStart + "# AND JST < #" + tripEnd + "#").ToString()).ToString() + "\r\n", LogMode.acc);
return(accRawTable);
}
public static DataTable Get(DateTime startTime, DateTime endTime, int timeDiff, InsertDatum datum)
{
var query = new StringBuilder();
query.AppendLine("SELECT");
query.AppendLine($" {ColumnDriverId},");
query.AppendLine($" {ColumnCarId},");
query.AppendLine($" {ColumnSensorId},");
query.AppendLine($" CONVERT(varchar,DATEADD(MILLISECOND, {timeDiff} ,{ColumnDateTime}),121) AS jst,");
query.AppendLine($" {ColumnAccX} AS {CorrectedAccDao.ColumnLongitudinalAcc},");
query.AppendLine($" {ColumnAccY} AS {CorrectedAccDao.ColumnLateralAcc},");
query.AppendLine($" {ColumnAccZ} AS {CorrectedAccDao.ColumnVerticalAcc}");
query.AppendLine($"FROM {TableName}");
query.AppendLine($"WHERE {ColumnDateTime} >= '{startTime}'");
query.AppendLine($" AND {ColumnDateTime} <= '{endTime}'");
query.AppendLine($" AND {ColumnDriverId} = {datum.DriverId}");
query.AppendLine($" AND {ColumnSensorId} = {datum.SensorId}");
query.AppendLine($"ORDER BY {ColumnDateTime}");
return(DatabaseAccesser.GetResult(query.ToString()));
}
