maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_ro_moytttracto(interval)
Parameters
Name
Type
Mode
threshold_pos
interval
IN
Definition
DECLARE r record; t record; BEGIN /* Get each position from ttguardians*/ FOR r IN (select d.serialnumber,e.senddate,e.receivedate,e.ignition_on,e.speed,e.longitude,e.latitude from device d JOIN tmp.moy e ON e.iddevice = d.iddevice WHERE d.serialnumber NOT IN (SELECT serialnumber FROM tmp.tractos) ) LOOP /* Get the closest position to every other device on the fleet within 50km*/ SELECT r.serialnumber,r.senddate,r.receivedate,r.ignition_on,r.speed,r.longitude,r.latitude,d.serialnumber serialnumber_t,e.senddate senddate_t,e.receivedate receivedate_t,e.ignition_on ignition_on_t,e.speed,e.longitude,e.latitude,st_distance(st_setsrid(st_makepoint(e.longitude,e.latitude),4326),st_setsrid(st_makepoint(r.longitude,r.latitude),4326)) INTO t FROM device d JOIN tmp.moy e ON e.iddevice = d.iddevice WHERE d.serialnumber <> r.serialnumber /* is not the ttguardian itself */ AND ((e.senddate BETWEEN (r.senddate - threshold_pos) AND r.senddate) OR (e.senddate BETWEEN r.senddate AND r.senddate + threshold_pos)) /* has positioned recently before or after x minutes*/ AND st_dwithin(st_setsrid(st_makepoint(e.longitude,e.latitude),4326),st_setsrid(st_makepoint(r.longitude,r.latitude),4326),1) /* is within x degrees 1 degree = 110km */ AND e.speed > 0 AND e.ignition_on = true; RETURN NEXT t; END LOOP; END;