Calculating distance

[cmrhema]

Hello
I am having a table where the speed of the vehicle at the respective time are stored. Now I should calculate distance
the eg details of vehicle No(VNo), speed,datetime is given below
vno speed datetime
2117 0 9/12/07 1:00
2117 0 9/12/07 2:00
2117 0 9/12/07 3:00
2117 1 9/12/07 4:00
2117 2 9/12/07 5:00
2117 3 9/12/07 6:00
2117 4 9/12/07 7:00
2117 5 9/12/07 8:00
2117 6 9/12/07 9:00
2117 0 9/12/07 10:00
2117 0 9/12/07 11:00
2117 0 9/12/07 12:00
2117 0 9/12/07 13:00
2117 9 9/12/07 14:00
2117 6 9/12/07 15:00
2117 5 9/12/07 16:00

To calculate the distance you should take all the non zero speed values and if the value is
2117 9 9/12/07 14:00
2117 6 9/12/07 15:00
2117 5 9/12/07 16:00

then the start time is 9/12/07 14:00
and the endtime is 9/12/07 16:00
now calculate the time span take the sum of speed and calculate the distance



Now I have calculated the distance as below

protected void Button1_Click(object sender, EventArgs e)
    {
        MyCon.Open();
        SqlDataAdapter MyDa1 = new SqlDataAdapter("select speed,gps_datetime from gpsdata_history where registrationno='" + DropDownList1.SelectedValue + "'and (gps_datetime >='" + TextBox1.Text + "' and gps_datetime<='" + TextBox2.Text + "' )order by gps_datetime", MyCon);
        DataTable MyDt1 = new DataTable();
        MyDa1.Fill(MyDt1);
        MyCon.Close();
        int Speed = 0;
        int sumSpeed = 0;
        TimeSpan timediff = TimeSpan.Zero;
        int i = 1;
        DateTime Startdate;
        DateTime Enddate;
        DateTime chkend;
        chkend = DateTime.Now;
        double Totaltime = 0;
        int count = MyDt1.Rows.Count;

        for (i = 0; i < count; i++)
        {
            Speed = Convert.ToInt32(MyDt1.Rows[i][0].ToString());

            while (Speed == 0 && i < (count-1))
            {
                Speed = Convert.ToInt32(MyDt1.Rows[i]["speed"].ToString());
                i++;
            }
            Startdate = Convert.ToDateTime(MyDt1.Rows[i]["gps_datetime"].ToString());
            while (Speed > 0 && i < count)
            {
                Speed = Convert.ToInt32(MyDt1.Rows[i]["speed"].ToString());
                if (Speed != 0)
                {
                    sumSpeed = sumSpeed + Convert.ToInt32(MyDt1.Rows[i]["speed"].ToString()); ;
                    i++;
                }
            }
            i--;
            Enddate = Convert.ToDateTime(MyDt1.Rows[i]["gps_datetime"].ToString());
            timediff = timediff + (Enddate - Startdate);

        }



        Totaltime = timediff.TotalHours;
        double distance = Totaltime * sumSpeed;
        TextBox3.Text = Totaltime.ToString();
        TextBox4.Text = distance.ToString();
        TextBox5.Text = sumSpeed.ToString();
    }

But this is not giving me the exact distance
regards
cmrhema

[Shashi Sadasivan]

Quite a bit of code happening there.
A few modification before you test it.
In your sql statement could you put a where speed > 0
and also put in order by time
this way your first record is the start time, and last record is the end time.

Then iterate throug the foreach to calc total speed, etc
cheers

-------
ok,,,,,guess i found the problemo
Apparantly you are increasin i within the for loop in the first while, so at the end of it, you have exhausted quite a bit of rows.
Then of what is left you try to find the time from that. (you might have passed the last time before.)
Try changin your approach similar to what I sent above.
cheers

[cmrhema]

Quite a bit of code happening there.
A few modification before you test it.
In your sql statement could you put a where speed > 0
and also put in order by time
this way your first record is the start time, and last record is the end time.

Then iterate throug the foreach to calc total speed, etc
cheers

-------
ok,,,,,guess i found the problemo
Apparantly you are increasin i within the for loop in the first while, so at the end of it, you have exhausted quite a bit of rows.
Then of what is left you try to find the time from that. (you might have passed the last time before.)
Try changin your approach similar to what I sent above.
cheers

thank you shashi for the reply

I have used order by gps_datetime
If I do not increment i in the while loop how will I go to the second loop
But still there is a problem
Of course i did modify or rather included the speed and calculated the average speed.
There is still the problem in calculating total time and speed.
My latest code looks as below

MyCon.Open();
        SqlDataAdapter MyDa1 = new SqlDataAdapter("select speed,gps_datetime from gpsdata_history where registrationno='" + DropDownList1.SelectedValue + "'and (gps_datetime >='" + TextBox1.Text + "' and gps_datetime<='" + TextBox2.Text + "' )order by gps_datetime", MyCon);
        DataTable MyDt1 = new DataTable();
        MyDa1.Fill(MyDt1);
        MyCon.Close();
        int Speed = 0;
        int sumSpeed = 0;
        TimeSpan timediff = TimeSpan.Zero;
        int i = 1;
        DateTime Startdate;
        DateTime Enddate;
        DateTime chkend;
        chkend = DateTime.Now;
        double Totaltime = 0;
        int count = MyDt1.Rows.Count;
        int avg=0;

        for (i = 0; i < count; i++)
        {
            Speed = Convert.ToInt32(MyDt1.Rows[i][0].ToString());

            while (Speed == 0 && i < (count-1))
            {
                Speed = Convert.ToInt32(MyDt1.Rows[i]["speed"].ToString());
                i++;
            }
            Startdate = Convert.ToDateTime(MyDt1.Rows[i]["gps_datetime"].ToString());
            while (Speed > 0 && i < count)
            {
              avg++;
                Speed = Convert.ToInt32(MyDt1.Rows[i]["speed"].ToString());
                if (Speed != 0)
                {
                    sumSpeed = sumSpeed + Convert.ToInt32(MyDt1.Rows[i]["speed"].ToString()); ;
                    i++;
                }
            }
            i--;
            Enddate = Convert.ToDateTime(MyDt1.Rows[i]["gps_datetime"].ToString());
            timediff = timediff + (Enddate - Startdate);

        }



        Totaltime = timediff.TotalHours;
        double distance = Totaltime * sumSpeed/avg;
        TextBox3.Text = Totaltime.ToString();
        TextBox4.Text = distance.ToString();
        TextBox5.Text = sumSpeed.ToString();
    }
}

[Shashi Sadasivan]

I got your code to what I was expecting it to be
Please note the change in the sql statement.

MyCon.Open();
        SqlDataAdapter MyDa1 = new SqlDataAdapter("select speed,gps_datetime from gpsdata_history where speed > 0 registrationno='" + DropDownList1.SelectedValue + "'and (gps_datetime >='" + TextBox1.Text + "' and gps_datetime<='" + TextBox2.Text + "' )order by gps_datetime", MyCon);
        DataTable MyDt1 = new DataTable();
        MyDa1.Fill(MyDt1);
        MyCon.Close();
        int Speed = 0;
        int sumSpeed = 0;
        TimeSpan timediff = TimeSpan.Zero;
        int i = 1;
        DateTime Startdate;
        DateTime Enddate;
        DateTime chkend;
        chkend = DateTime.Now;
        double Totaltime = 0;
        int count = MyDt1.Rows.Count;
        int avg=0;
 
        Startdate = Convert.ToDateTime(MyDt1.Rows[0]["gps_datetime"].ToString());
        Enddate = Convert.ToDateTime(MyDt1.Rows[MyDt1.Rows.Count-1]["gps_datetime"].ToString());
        timediff = (Enddate - Startdate);

        for (i = 0; i < count; i++)
        {
            avg++;
            sumSpeed += Convert.ToInt32(MyDt1.Rows[i]["speed"].ToString());
        }
 
 
        Totaltime = timediff.TotalHours;
        double distance = Totaltime * sumSpeed/avg;
        TextBox3.Text = Totaltime.ToString();
        TextBox4.Text = distance.ToString();
        TextBox5.Text = sumSpeed.ToString();

Hope this helps
Cheers

[cmrhema]

I got your code to what I was expecting it to be
Please note the change in the sql statement.

MyCon.Open();
        SqlDataAdapter MyDa1 = new SqlDataAdapter("select speed,gps_datetime from gpsdata_history where speed > 0 registrationno='" + DropDownList1.SelectedValue + "'and (gps_datetime >='" + TextBox1.Text + "' and gps_datetime<='" + TextBox2.Text + "' )order by gps_datetime", MyCon);
        DataTable MyDt1 = new DataTable();
        MyDa1.Fill(MyDt1);
        MyCon.Close();
        int Speed = 0;
        int sumSpeed = 0;
        TimeSpan timediff = TimeSpan.Zero;
        int i = 1;
        DateTime Startdate;
        DateTime Enddate;
        DateTime chkend;
        chkend = DateTime.Now;
        double Totaltime = 0;
        int count = MyDt1.Rows.Count;
        int avg=0;
 
        Startdate = Convert.ToDateTime(MyDt1.Rows[0]["gps_datetime"].ToString());
        Enddate = Convert.ToDateTime(MyDt1.Rows[MyDt1.Rows.Count-1]["gps_datetime"].ToString());
        timediff = (Enddate - Startdate);

        for (i = 0; i < count; i++)
        {
            avg++;
            sumSpeed += Convert.ToInt32(MyDt1.Rows[i]["speed"].ToString());
        }
 
 
        Totaltime = timediff.TotalHours;
        double distance = Totaltime * sumSpeed/avg;
        TextBox3.Text = Totaltime.ToString();
        TextBox4.Text = distance.ToString();
        TextBox5.Text = sumSpeed.ToString();

Hope this helps
Cheers

Thank you shashi for your help
I have solved it as below

protected void Button1_Click(o bject sender, EventArgs e)
{
MyCon.Open();
SqlDataAdapter MyDa1 = new SqlDataAdapter( "select speed,gps_datet ime from gpsdata_history where registrationno= '" + DropDownList1.S electedValue + "'and (gps_datetime >='" + TextBox1.Text + "' and gps_datetime<=' " + TextBox2.Text + "' )order by gps_datetime", MyCon);
DataTable MyDt1 = new DataTable();
MyDa1.Fill(MyDt 1);
MyCon.Close();
int Speed = 0;
int sumSpeed = 0;
TimeSpan timediff = TimeSpan.Zero;
int i = 1;
DateTime Startdate;
DateTime Enddate;
DateTime chkend;
chkend = DateTime.Now;
double Totaltime = 0;
int count = MyDt1.Rows.Coun t;
int avg=0;
int flag = 0;

for (i = 0; i < count; i++)
{
Speed = Convert.ToInt32 (MyDt1.Rows[i][0].ToString());

while (Speed == 0 && i < (count-1))
{
Speed = Convert.ToInt32 (MyDt1.Rows[i]["speed"].ToString());
if (Speed == 0)
{
Speed = Convert.ToInt32 (MyDt1.Rows[i]["speed"].ToString());
i++;
}
}
Startdate = Convert.ToDateT ime(MyDt1.Rows[i]["gps_dateti me"].ToString());
while (Speed > 0 && i < count)
{

Speed = Convert.ToInt32 (MyDt1.Rows[i]["speed"].ToString());
if (Speed != 0)
{
avg++;
sumSpeed = sumSpeed + Convert.ToInt32 (MyDt1.Rows[i]["speed"].ToString()); ;
i++;
}
flag = 1;
}
if (flag > 0)
{
flag = 0;
i--;
//Enddate = Convert.ToDateT ime(MyDt1.Rows[i]["gps_dateti me"].ToString());
//timediff = timediff + (Enddate - Startdate);

}

Enddate = Convert.ToDateT ime(MyDt1.Rows[i]["gps_dateti me"].ToString());
timediff = timediff + (Enddate - Startdate);

}



Totaltime = timediff.TotalH ours;
double distance = Totaltime * sumSpeed/avg;
TextBox3.Text = Totaltime.ToStr ing();
TextBox4.Text = distance.ToStri ng();
TextBox5.Text = sumSpeed.ToStri ng();
}