Code:
awk -F, 'FNR>1 {
gsub(/"/, "")
line=substr($10,1,5)
point=substr($11,1,5)
ffid=$2
sw=$5
tape=$6;
x=$49;
y=$50;
tb=$46
printf("%4d %5d %5d %4d %4d %10.1f %10.1f %s\n",
tape,line,point,ffid,sw,x,y,tb)}' 25.csv |
sort -k8n | awk '{v=substr($8,16,1); gsub(/2/,"0",v);print substr($0,1,64) v}' > toto
awk '
BEGIN {
split("analysis.csv bad_records.csv", OUT)
for(j=1;j<3;j++) {
printf "Tape1,LineName1,PointNumber1,FFID1,X1,Y1,JD1,HHMMSS1," > OUT[j]
printf "Tape2,LineName2,PointNumber2,FFID2,X2,Y2,JD2,HHMMSS2," > OUT[j]
printf "Offset,SlipTime,Allowed_SlipTime\n" > OUT[j]
}
}
function gpstojdtime(gpstime) {
gpss = (gpstime/1000000)+10800;
jtmp = (gpss-((2017-1980)*365+10-6)*86400-18)/86400;
jday = int(jtmp);
htmp = 24*(jtmp - jday);
hour = int(htmp);
mtmp = 60*((htmp - hour));
minu = int(mtmp);
stmp = 60*(mtmp - minu);
secd = (stmp);
return hour*10000+minu*100+secd;
}
function allowedsliptime(distance) {
if(distance < 2000) return 18
if(distance > 12000) return 0
if(distance < 6000) return 24 - distance / 333.333
return 12 - distance / 1000
}
function calcoffset(x,y) {
return sqrt((x[1]-x[2])^2+(y[1]-y[2])^2)
}
FNR==NR { L[FNR]=$0 ; next}
{
tape[1]=$1
splin[1]=$2
spnum[1]=$3
sprec[1]=$4
sw[1]=$5
x[1]=$6
y[1]=$7
spgps[1]=$8
dtime[1]=gpstojdtime(spgps[1])
day[1]=jday
# Look up to 10 records ahead
for(i=1; i< 10 && (i+FNR in L); i++) {
split(L[i+FNR],V)
tape[2]=V[1]
splin[2]=V[2]
spnum[2]=V[3]
sprec[2]=V[4]
sw[2]=V[5]
x[2]=V[6]
y[2]=V[7]
spgps[2]=V[8]
dtime[2]=gpstojdtime(spgps[2])
day[2]=jday
# Skip lookahead when diff is greater than 25sec
if(dtime[2] - dtime[1] > 25) next
offset = calcoffset(x, y)
stime = dtime[2] - dtime[1]
allow = allowedsliptime(offset)
# decide on output file based on splittime vrs allowedsplit
if(stime < allow) of=OUT[2]
else of=OUT[1]
for(j=1;j<3;j++)
printf"%d,%d,%d,%d,%.1f,%.1f,%d,%.2f,",
tape[j],splin[j],spnum[j],sprec[j],
x[j],y[j],day[j], dtime[j] > of
printf "%.4f,%.3f,%5.4f\n", offset, stime, allow > of
}
}' toto toto