locked
Need help with query RRS feed

  • Question

  • I have next events source

    Func<int, int, DateTime> t = (h, m) => new DateTime(2012, 10, 7, 0, 0, 0).AddHours(h).AddMinutes(m);
    
    var data = new []
    {
        new { Imei = "A", Speed = 0, Latitude = 58.356216, Longitude = 26.680831, Time = t(4, 1) },
        new { Imei = "A", Speed = 5, Latitude = 58.35554, Longitude = 26.680019, Time = t(4, 2) },
        new { Imei = "A", Speed = 16, Latitude = 58.355135, Longitude = 26.6795, Time = t(4, 3) },
        new { Imei = "A", Speed = 40, Latitude = 58.353581, Longitude = 26.677548, Time = t(4, 7) },
        new { Imei = "A", Speed = 21, Latitude = 58.353266, Longitude = 26.677119, Time = t(4, 8) },
        new { Imei = "A", Speed = 0, Latitude = 58.352928, Longitude = 26.676604, Time = t(4, 9) },
        new { Imei = "A", Speed = 0, Latitude = 58.352928, Longitude = 26.676604, Time = t(4, 10) },
        new { Imei = "A", Speed = 0, Latitude = 58.352928, Longitude = 26.676604, Time = t(4, 11) },
        new { Imei = "A", Speed = 0, Latitude = 58.352928, Longitude = 26.676604, Time = t(4, 12) },
        new { Imei = "A", Speed = 8, Latitude = 58.351645, Longitude = 26.674673, Time = t(4, 13) },
        new { Imei = "A", Speed = 25, Latitude = 58.351375, Longitude = 26.674286, Time = t(4, 14) },
        new { Imei = "A", Speed = 50, Latitude = 58.350755, Longitude = 26.673278, Time = t(4, 16) },
        new { Imei = "A", Speed = 21, Latitude = 58.349877, Longitude = 26.671904, Time = t(4, 18) },
        new { Imei = "A", Speed = 7, Latitude = 58.349517, Longitude = 26.671368, Time = t(4, 19) },
        new { Imei = "A", Speed = 0, Latitude = 58.349168, Longitude = 26.670789, Time = t(4, 20) }
    };
    
    var atgs = new AdvanceTimeGenerationSettings(1, TimeSpan.FromSeconds(0), true);
    var ats = new AdvanceTimeSettings(atgs, null, AdvanceTimePolicy.Adjust);
    
    var source = data.ToPointStream(app, e => PointEvent.CreateInsert(e.Time, e), ats);

    Now I need select all points where distance is > 0

    var movement = from s in source
                        .AlterEventDuration(x => TimeSpan.MaxValue)
                        .ClipEventDuration(source, (x1, x2) => x1.Imei == x2.Imei)
                        .ShiftEventTime(c => TimeSpan.FromTicks(1))
                    join e in source on s.Imei equals e.Imei
                    where GetDistance(e.Latitude, e.Longitude, s.Latitude, s.Longitude) > 0
                    select e;

    This query returns

    Imei=A  Speed=5  Latitude=58.35554  Longitude=26.680019  Time=10/7/2012 4:02:00 AM
    Imei=A  Speed=16  Latitude=58.355135  Longitude=26.6795  Time=10/7/2012 4:03:00 AM
    Imei=A  Speed=40  Latitude=58.353581  Longitude=26.677548  Time=10/7/2012 4:07:00 AM
    Imei=A  Speed=21  Latitude=58.353266  Longitude=26.677119  Time=10/7/2012 4:08:00 AM
    Imei=A  Speed=0  Latitude=58.352928  Longitude=26.676604  Time=10/7/2012 4:09:00 AM
    Imei=A  Speed=8  Latitude=58.351645  Longitude=26.674673  Time=10/7/2012 4:13:00 AM
    Imei=A  Speed=25  Latitude=58.351375  Longitude=26.674286  Time=10/7/2012 4:14:00 AM
    Imei=A  Speed=50  Latitude=58.350755  Longitude=26.673278  Time=10/7/2012 4:16:00 AM
    Imei=A  Speed=21  Latitude=58.349877  Longitude=26.671904  Time=10/7/2012 4:18:00 AM
    Imei=A  Speed=7  Latitude=58.349517  Longitude=26.671368  Time=10/7/2012 4:19:00 AM
    Imei=A  Speed=0  Latitude=58.349168  Longitude=26.670789  Time=10/7/2012 4:20:00 AM

    As you can see this query does not include conjugated points where vehicle began movement. Missing points are:

    Imei=A  Speed=0  Latitude = 58.356216 Longitude = 26.680831  Time=10/7/2012 4:01:00 AM
    Imei=A  Speed=0  Latitude = 58.352928, Longitude = 26.676604  Time=10/7/2012 4:12:00 AM

    I think I need to use left anti semi-join but can't figure out how.
    Thanks in advance.

    Thursday, November 8, 2012 9:28 AM

Answers

  • Will this work for you:

    var movement = from s in source
    					.AlterEventDuration(x => TimeSpan.MaxValue)
    					.ClipEventDuration(source, (x1, x2) => x1.Imei == x2.Imei)
    					.ShiftEventTime(c => TimeSpan.FromTicks(1))
    				join e in source on s.Imei equals e.Imei
    				where GetDistance(e.Latitude, e.Longitude, s.Latitude, s.Longitude) > 0
    				select new {
    					Start = s, 
    					End = e};


    DevBiker (aka J Sawyer)
    Microsoft MVP - Sql Server (StreamInsight)


    Ruminations of J.net


    If I answered your question, please mark as answer.
    If my post was helpful, please mark as helpful.

    Thursday, November 8, 2012 4:17 PM
    Moderator