UNIX for Dummies Questions & Answers

I have a large ascii file of 3+ million records.
The data type is easting northing elevation coordinates.
The file format is 3 fields, a numeric value for:
easting northing elevation

When I view this data graphically in AutoCAD or ESRI ArcMap, I can see that there a many points that are very close, not duplicated, but very very close to each other in the x and y 2D space. So therefore a lot of the data points are redundant.

Is there an awk script that I could use on the ascii file that could remove records that are close to each other within a user defined x/easting and y/northing tolerance?

I already have the command for removing duplicate records ( !x{$1$2}++ ) but I can't figure out how to get nearly dupicates removed.

Thanks in advance for your help.

Kenny.

I am assuming your x/y data looks like normal long/lat xx.xxxxxx yy.yyyyyy
Further lets define a tolerance by rounding - say 10^-5 gives you separation. So, change the 5 to suit. by the way your code has a syntax error {...}++ should be [...]++

Code:

awk '!arr[sprintf("%0.5f|%0.5f",$1,$2)]++' inputfile > outputfile

paste your sample record here, and tell us which records belong to "nearly dupicates"

Thank you.

Jim,

{$1$2} was a typo.

rdcwayx,

My files are not Latitude Longitude Degrees, but State Plane Feet.
They typically contain 3 million records.

A file looks like, PointNumber Easting Northing Elevation, as follows:

PointNumber_0000001 1000000.123456 1000000.123456 10000.123456
PointNumber_0000010 1000001.234567 1000002.234567 10345.234567
PointNumber_0000100 1000010.345678 1000020.456789 10030.987654
PointNumber_0001000 1000050.345678 1000050.456789 10030.987654
PointNumber_0010000 1000123.123456 1000456.123456 10789.123456
PointNumber_0100000 1000123.123456 1000456.123456 10789.123456
PointNumber_1000000 1000000.123456 1000000.123456 10000.123456
PointNumber_2000000 1000011.345678 1000021.456789 10030.987654
PointNumber_3000000 1000051.000678 1000049.999000 10030.987654

Where, relative to fields 2 and 3:
PointNumber_1000000 is an "exact duplicate" of PointNumber_0000001
PointNumber_0100000 is an "exact duplicate" of PointNumber_0010000
Where, relative to fields 2 and 3, and within a user defined range of + or - 2.0:
PointNumber_2000000 is a "near duplicate" of PointNumber_0000100
PointNumber_3000000 is a "near duplicate" of PointNumber_0001000

So a point/record is a "near duplicate" when the easting and northing are within a user defined range. So if I use a value of 2.75 feet for a range, then if a record has easting and northing that are within 2.5 feet of any other record then it it to considered a "near duplicate" and deleted.

If possible, it would be great if I could get two files from the input file:
1. An output file with the near duplicates removed.
2. An output file with the near duplicates that were removed.

Thank you again,
Kenny.

---------- Post updated at 01:38 PM ---------- Previous update was at 09:25 AM ----------

Jim,

When I use your code on the sample data set in my previous post, it prints the whole file.

Kenny.

Because I thought you were using lat longs (our GIS system does output data that way) not state plane coords. My bad.

---------- Post updated at 15:50 ---------- Previous update was at 15:04 ----------

Try this - the two files created are named: uniques, duplicates

Code:

 #!/bin/ksh
 > uniques
 > duplicates
 
awk '{ x=int( ($2/2.75) + 1.0000001 ) * 2.75
       y=int( ($3/2.75) + 1.0000001 ) * 2.75
        tmp=sprintf("%f|%f", x , y)
        print $0, tmp
        if(tmp in arr) {
          print $0 >> "duplicates"
        }
        else {
          print $0 >> "uniques"
          arr[tmp]++
        }
      } ' inputfile

Jim,

Thank you for your efforts in this matter.

I should add that my OS is Microsoft Windows XP.

Not sure if this will have any effect on your syntax.

I am actually using mawk.

I typically run mawk from inside a DOS batch.

Will any of this effect the behavior of your code?

Note.... that I am a complete novice (aka hacker).

Can you explain what your code is doing?

Thanks,
Kenny.

Jim's code is so perfect.

A little bit of change to easily adjust range value.

Code:

awk -v range=2.75 '{ x=int( ($2/range) + 1.0000001 ) * range
       y=int( ($3/range) + 1.0000001 ) * range
        tmp=sprintf("%.2f|%.2f", x , y)
        print $0, tmp
        if(tmp in arr) {
          print $0 >> "duplicates"
        }
        else {
          print $0 >> "uniques"
          arr[tmp]++
        }
      } ' infile

To kenneth.mcbride,

From the output, the value in last column (separated by |) tell you the secret. It simplifies the whole processes.

Code:

PointNumber_0000001 1000000.123456 1000000.123456 10000.123456 1000001.75|1000001.75
PointNumber_0000010 1000001.234567 1000002.234567 10345.234567 1000001.75|1000004.50
PointNumber_0000100 1000010.345678 1000020.456789 10030.987654 1000012.75|1000021.00
PointNumber_0001000 1000050.345678 1000050.456789 10030.987654 1000051.25|1000051.25
PointNumber_0010000 1000123.123456 1000456.123456 10789.123456 1000125.50|1000458.25
PointNumber_0100000 1000123.123456 1000456.123456 10789.123456 1000125.50|1000458.25
PointNumber_1000000 1000000.123456 1000000.123456 10000.123456 1000001.75|1000001.75
PointNumber_2000000 1000011.345678 1000021.456789 10030.987654 1000012.75|1000023.75
PointNumber_3000000 1000051.000678 1000049.999000 10030.987654 1000051.25|1000051.25

Seesm if two records are "exact duplicate", Jim's code will have wrong output.