troubleshooting Question

AWK: Pytthagoras bp script Part deux

Avatar of Anthony Mellor
Anthony MellorFlag for United Kingdom of Great Britain and Northern Ireland asked on
Shell Scripting* AWK
22 Comments1 Solution150 ViewsLast Modified:
I have amazing results from running the unconstrained code.

Can I put an IF statement in this?

AND $WHEN > $BEFORE , $WHEN < AFTER type of thing.

Not got anywhere trying with SQL and databases, although Filemaker is helping, but it's .. Filemaker which is peculiar to itself.

I need to enter a constraint such that $WHEN in ACC.CSV falls between $BEFORE and $AFTER in SITES.CSV, if not, skip to next.

I was going to attempt to put the code in myself, but it has struck me it may involve setting variables so I haven't.

After running this I found the second file contents appended to the end of the output file, still not sure I believed it but any thoughts about that welcome.

# ACC.csv
#CODE,ACC_EC,ACC_NC,Long,Lat,POL,Severity,No_Vehicles,No_Casualties,Date,DayofWeek,Time,LA_District,LA_Highway,1st_Road_Class,1st_Road_Number,Road_Type,LIMIT,Junction_Detail,Junction_Control,2nd_Road_Class,2nd_Road_Number,Zebra-Human,Zebra_Machine,Visibility,Weather_Conditions,Surface,Special_Conditions,Road_Hazards,Urban_Rural,Police_Attended,LSOAofLocation,ACCREF,Month,Minute,ACCDAY,Hour,YEAR,WHEN,ACC_EC5,ACC_NC5
#   a = ACC_EC ($2)
#   b = ACC_NC ($3)
#
# SITES.csv
# SORT,POL,REF,CAM ETG,CAM NTG,INSTALL,MONTH,WHEN,TYPE,CAM_ETGkm,CAM_NTGkm OLD HEADS
# SORT,POL,REF,SITE_ETG,SITE_NTG,INSTALL,MONTH,WHEN,TYPE,BEFORE,AFTER  - NEW HEADS
#   r = REF ($3)
#   c = SITE_ETG ($4)
#   d = SITE_NTG ($5)

# One time logic before records are read from input file
BEGIN {
   # Change field separator from space to comma for our file
   FS = ","
   file2 ="SITES.CSV"
}

# Main processing loop handling each line of the file
{
   # Check if first line (header)
   if (NR == 1) {

      # If this is header just write it to output adding new column headers
      print $0 ",REF,SITE_ETG,SITE_NTG,Distance"

   } else {

      # skip blank lines
      if ($0 != "") {

         # Save input record from file1
         source = $0

         # Save needed fields for calcs below, remove double quotes if found DOUBLE QUOTES PREVIOUSLY STRIPPED FROM FILE
         a = $2         # ACC_EC 
         b = $3         # ACC_NC
         gsub("\"", "", a)
         gsub("\"", "", b)

         Bestx = 999999999
         # Add new columns at the end
         while ((getline < file2 ) > 0 ) {
            if ($1 != "SORT") {
               r = $3
               c = $4      # SITE_ETG
               d = $5      # SITE_NTG
               x = sqrt(((a-c)^2)+((b-d)^2))
               if (x < Bestx) {
                  Bestx = x
                  Bestr = r
                  Bestc = c
                  Bestd = d
               }
            }
         }
         printf("%s,%s,%s,%s,%f\n", source, Bestr, Bestc, Bestd, Bestx)
         close(file2)
      }
   }
}
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 22 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 22 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros