use ranchdb;

# map data points from cordinate to pixel
insert into speed_pixel
select c2.idcordinate, p.idpixel,sqrt(pow(c2.x-c1.x,2)+pow(c2.y-c1.y,2))/5 as speed
from cordinate_points as c1, cordinate_points as c2, pixel as p
where c2.X>p.X and c2.X<p.X+30 and c2.Y<p.Y and c2.Y>p.Y-30
and c2.idcordinate=c1.idcordinate+1 and c2.date=c1.date;

# for each cow and each pixel calculate how many days this cow visit back to this pixel
select c.cowid, p.pixel_idpixel, count(distinct(c.date)), datediff(max(c.date), min(c.date))
from cordinate_points as c, speed_pixel as p
where c.idcordinate=p.cordinate_points_idcordinate and distance>=5 and distance<=100 
group by c.cowid, p.pixel_idpixel into outfile '/tmp/result1.txt';

# for each year, each cow and each pixel calculate how many days this cow visit back to this pixel
select c.cowid as cow_id, year(c.date) as year, p.pixel_idpixel as pixel, count(distinct(c.date)) as totaldays,
datediff(max(c.date), min(c.date))/(count(distinct(c.date))-1) as period
from cordinate_points as c, speed_pixel as p
where c.idcordinate=p.cordinate_points_idcordinate and distance>=5 and distance<=100 
group by c.cowid, p.pixel_idpixel, year(c.date) into outfile '/tmp/result2.txt';

# for each year, each cow and each pixel calculate how many days this cow visit back to this pixel
select temTable.cow_id, y, count(temTable.pixel), avg(temTable.totaldays), avg(temTable.period) from
(select c.cowid as cow_id, year(c.date) as y, p.pixel_idpixel as pixel, count(distinct(c.date)) as totaldays,
    datediff(max(c.date), min(c.date))/(count(distinct(c.date))-1) as period
    from cordinate_points as c, speed_pixel as p where c.idcordinate=p.cordinate_points_idcordinate and distance>=5 and distance<=100
    group by c.cowid, p.pixel_idpixel, year(c.date) ) as temTable
where temTable.period is not null
group by temTable.cow_id, temTable.y order by temTable.y into outfile '/tmp/result3.txt';
