Matching Algorithm

[Merriënboer]

Hello all,

This is my first post. Please inform me if I've missed something from the posting guidelines.

I need a sorting code to sort corrosion spots on a oil or gas pipeline. The location and geometry of those spots are determined bij an Intelligent PIG inspection.

I have an IP inspection from 2007 and from 2012. Lets say that on one pipeline segment (~12m), there are 12 corrosion spots in 2007, and an additional 8 in 2012 (12grown spots and 8 new ones). I need to match the 12 spots in 2007 to the 12 spots in 2012, so as to determine the corrosion growth in mm/year (which does not have to be discussed here.

Corrosion spots' locations are determined by the up weld distance (distance from the weld in meters, or the x) and the clock positions (y position of the spot on the pipeline).

To match those corrosion spots, both the x and y must be matched. Since we are using measurement equipment here, those positions are likeley not to match exactly. Therefore, margins are applied.

Here is the data as I receive it. As you can see, the up weld distance and clock position are unmatched

2007																	2012
Log distance (m)	Joint	up weld dist. (m)	Joint length (m)	Feature type	Feature identification	Anomaly dimension class	Clock position (h:min)	Nominal t (mm)	Reference t (mm)	Length (mm)	Width (mm)	Remaining t (mm)	d peak (mm)	d mean (mm)	Surface location	Log distance (m)	Joint	Up weld distance (m)	Joint length (m)	Feature type	Feature identification	Anomaly dimension class	Clock position (h:min)	Nominal t (mm)	Reference t (mm)	Length (mm)	Width (mm)	Remaining t (mm)	d peak (%)	d mean (%)	Surface location
148,93	280	-	11,77	Weld	-	-	-	10,8	-	-	-	-	-	-	-		148,66	280	-	11,68	Weld	-	-	-	10,8	-	-	-	-	-	-	-
149,09	280	0,15	11,77	Anomaly	Corrosion	Pitting	0,42	10,8	10,9	37	30	7	3,9	n.a.	Internal	148,81	280	0,16	11,68	Anomaly	Corrosion	General	0,29	10,8	10,9	81	47	8	26,9	n.a.	Internal
149,17	280	0,23	11,77	Anomaly	Corrosion	General	0,33	10,8	11,5	60	43	5,4	6,1	0,4	Internal	148,81	280	0,16	11,68	Anomaly	Corrosion	General	0,41	10,8	11,1	73	34	7	36,9	1,1	Internal
149,38	280	0,45	11,77	Anomaly	Corrosion	Pitting	0,46	10,8	10,7	45	26	5,1	5,6	0,6	Internal	148,86	280	0,2	11,68	Anomaly	Corrosion cluster	General	0,34	10,8	11,1	175	191	5,2	53,2	1,3	Internal
152,02	280	3,08	11,77	Anomaly	Corrosion	General	0,12	10,8	10,8	58	39	8,4	2,4	0,5	Internal	148,88	280	0,22	11,68	Anomaly	Corrosion	Axial grooving	0,38	10,8	11,1	52	26	9,4	15,1	2,3	Internal
153,66	280	4,73	11,77	Anomaly	Corrosion	Axial grooving	0,44	10,8	10,8	64	26	6,2	4,6	0,7	Internal	148,88	280	0,22	11,68	Anomaly	Corrosion	General	0,33	10,8	11,2	131	51	5,2	53,5	1	Internal
154,6	280	5,66	11,77	Anomaly	Corrosion	Pitting	0,44	10,8	10,8	45	30	7,2	3,6	0,5	Internal	149,12	280	0,46	11,68	Anomaly	Corrosion	General	0,47	10,8	10,9	175	51	4,2	61,4	0,5	Internal
154,99	280	6,05	11,77	Anomaly	Corrosion	Axial grooving	0,07	10,8	10,4	45	21	7,4	3	1,1	Internal	150,74	280	2,09	11,68	Anomaly	Corrosion	General	0,12	10,8	10,9	115	34	9,4	14,2	1,4	Internal
156,12	280	7,19	11,77	Anomaly	Corrosion	Pitting	0,48	10,8	10,7	37	26	8,7	2	1,2	Internal	151,37	280	2,71	11,68	Anomaly	Corrosion	General	0,14	10,8	10,8	108	34	9,7	10,7	1,2	Internal
156,65	280	7,72	11,77	Anomaly	Corrosion	General	0,06	10,8	10,6	53	43	8,8	1,8	0,5	Internal	151,72	280	3,06	11,68	Anomaly	Corrosion	General	0,11	10,8	10,9	175	47	8,5	22,3	3,2	Internal
																	153,39	280	4,73	11,68	Anomaly	Corrosion	Axial grooving	0,43	10,8	11	113	26	6	45,6	3,6	Internal
																	153,66	280	5	11,68	Anomaly	Corrosion	Axial grooving	0,28	10,8	10,7	62	21	9	15,6	6,3	Internal
																	153,77	280	5,12	11,68	Anomaly	Corrosion	Pitting	0,23	10,8	10,8	42	21	9,2	14,5	3,1	Internal
																	154,33	280	5,67	11,68	Anomaly	Corrosion	General	0,44	10,8	10,9	55	34	6,7	38,4	1,7	Internal
																	154,77	280	6,12	11,68	Anomaly	Corrosion	General	0,1	10,8	10,7	258	145	7,6	29,2	2,6	Internal
																	154,87	280	6,21	11,68	Anomaly	Corrosion	General	0,39	10,8	11,2	58	34	9,5	14,9	1,4	Internal
																	155,57	280	6,92	11,68	Anomaly	Area with corrosion	General	0,12	10,8	10,8	858	145	9,4	12,7	1,4	Internal
																	155,84	280	7,19	11,68	Anomaly	Corrosion	General	0,48	10,8	10,8	95	34	8,3	23	6,9	Internal
																	156,38	280	7,72	11,68	Anomaly	Corrosion	General	0,08	10,8	10,7	83	85	9,1	15,2	2,2	Internal
																	157,13	280	8,48	11,68	Anomaly	Corrosion	Axial grooving	0,43	10,8	10,5	93	17	9,5	9	0,7	Internal
																	158,55	280	9,9	11,68	Anomaly	Corrosion	General	0,07	10,8	10,9	150	60	9,5	12,3	1	Internal
																	158,79	280	10,13	11,68	Anomaly	Corrosion	General	0,35	10,8	10,6	317	132	9,7	8,7	2	Internal
																	159,2	280	10,55	11,68	Anomaly	Corrosion	Axial grooving	0,13	10,8	10,8	217	30	9,5	11,6	0,3	Internal
																	159,54	280	10,89	11,68	Anomaly	Corrosion	General	0,14	10,8	10,6	128	43	8,9	15,8	2,2	Internal
																	160,14	280	11,49	11,68	Anomaly	Area with corrosion	General	0,12	10,8	10,6	208	149	9,4	11	2,3	Internal

<tbody>
</tbody>

Following is a manually matched pipeline segment:

2007																	2012
Log distance (m)	Joint	up weld dist. (m)	Joint length (m)	Feature type	Feature identification	Anomaly dimension class	Clock position (h:min)	Nominal t (mm)	Reference t (mm)	Length (mm)	Width (mm)	Remaining t (mm)	d peak (mm)	d mean (mm)	Surface location	Log distance (m)	Joint	Up weld distance (m)	Joint length (m)	Feature type	Feature identification	Anomaly dimension class	Clock position (h:min)	Nominal t (mm)	Reference t (mm)	Length (mm)	Width (mm)	Remaining t (mm)	d peak (%)	d mean (%)	Surface location
148,93	280	-	11,77	Weld	-	-	-	10,8	-	-	-	-	-	-	-		148,66	280	-	11,68	Weld	-	-	-	10,8	-	-	-	-	-	-	-
																	148,81	280	0,16	11,68	Anomaly	Corrosion	General	0,29	10,8	10,9	81	47	8	26,9	n.a.	Internal
149,09	280	0,15	11,77	Anomaly	Corrosion	Pitting	0,42	10,8	10,9	37	30	7	3,9	n.a.	Internal		148,81	280	0,16	11,68	Anomaly	Corrosion	General	0,41	10,8	11,1	73	34	7	36,9	1,1	Internal
																	148,86	280	0,2	11,68	Anomaly	Corrosion cluster	General	0,34	10,8	11,1	175	191	5,2	53,2	1,3	Internal
																	148,88	280	0,22	11,68	Anomaly	Corrosion	Axial grooving	0,38	10,8	11,1	52	26	9,4	15,1	2,3	Internal
149,17	280	0,23	11,77	Anomaly	Corrosion	General	0,33	10,8	11,5	60	43	5,4	6,1	0,4	Internal		148,88	280	0,22	11,68	Anomaly	Corrosion	General	0,33	10,8	11,2	131	51	5,2	53,5	1	Internal
149,38	280	0,45	11,77	Anomaly	Corrosion	Pitting	0,46	10,8	10,7	45	26	5,1	5,6	0,6	Internal		149,12	280	0,46	11,68	Anomaly	Corrosion	General	0,47	10,8	10,9	175	51	4,2	61,4	0,5	Internal
																	150,74	280	2,09	11,68	Anomaly	Corrosion	General	0,12	10,8	10,9	115	34	9,4	14,2	1,4	Internal
																	151,37	280	2,71	11,68	Anomaly	Corrosion	General	0,14	10,8	10,8	108	34	9,7	10,7	1,2	Internal
152,02	280	3,08	11,77	Anomaly	Corrosion	General	0,12	10,8	10,8	58	39	8,4	2,4	0,5	Internal		151,72	280	3,06	11,68	Anomaly	Corrosion	General	0,11	10,8	10,9	175	47	8,5	22,3	3,2	Internal
153,66	280	4,73	11,77	Anomaly	Corrosion	Axial grooving	0,44	10,8	10,8	64	26	6,2	4,6	0,7	Internal		153,39	280	4,73	11,68	Anomaly	Corrosion	Axial grooving	0,43	10,8	11	113	26	6	45,6	3,6	Internal
																	153,66	280	5	11,68	Anomaly	Corrosion	Axial grooving	0,28	10,8	10,7	62	21	9	15,6	6,3	Internal
																	153,77	280	5,12	11,68	Anomaly	Corrosion	Pitting	0,23	10,8	10,8	42	21	9,2	14,5	3,1	Internal
154,6	280	5,66	11,77	Anomaly	Corrosion	Pitting	0,44	10,8	10,8	45	30	7,2	3,6	0,5	Internal		154,33	280	5,67	11,68	Anomaly	Corrosion	General	0,44	10,8	10,9	55	34	6,7	38,4	1,7	Internal
154,99	280	6,05	11,77	Anomaly	Corrosion	Axial grooving	0,07	10,8	10,4	45	21	7,4	3	1,1	Internal		154,77	280	6,12	11,68	Anomaly	Corrosion	General	0,1	10,8	10,7	258	145	7,6	29,2	2,6	Internal
																	154,87	280	6,21	11,68	Anomaly	Corrosion	General	0,39	10,8	11,2	58	34	9,5	14,9	1,4	Internal
																	155,57	280	6,92	11,68	Anomaly	Area with corrosion	General	0,12	10,8	10,8	858	145	9,4	12,7	1,4	Internal
156,12	280	7,19	11,77	Anomaly	Corrosion	Pitting	0,48	10,8	10,7	37	26	8,7	2	1,2	Internal		155,84	280	7,19	11,68	Anomaly	Corrosion	General	0,48	10,8	10,8	95	34	8,3	23	6,9	Internal
156,65	280	7,72	11,77	Anomaly	Corrosion	General	0,06	10,8	10,6	53	43	8,8	1,8	0,5	Internal		156,38	280	7,72	11,68	Anomaly	Corrosion	General	0,08	10,8	10,7	83	85	9,1	15,2	2,2	Internal
																	157,13	280	8,48	11,68	Anomaly	Corrosion	Axial grooving	0,43	10,8	10,5	93	17	9,5	9	0,7	Internal
																	158,55	280	9,9	11,68	Anomaly	Corrosion	General	0,07	10,8	10,9	150	60	9,5	12,3	1	Internal
																	158,79	280	10,13	11,68	Anomaly	Corrosion	General	0,35	10,8	10,6	317	132	9,7	8,7	2	Internal
																	159,2	280	10,55	11,68	Anomaly	Corrosion	Axial grooving	0,13	10,8	10,8	217	30	9,5	11,6	0,3	Internal
																	159,54	280	10,89	11,68	Anomaly	Corrosion	General	0,14	10,8	10,6	128	43	8,9	15,8	2,2	Internal
																	160,14	280	11,49	11,68	Anomaly	Area with corrosion	General	0,12	10,8	10,6	208	149	9,4	11	2,3	Internal

<tbody>
</tbody>

As you can see, the corrosion spots are matched by up weld distance first, then by clock position, since there can be multiple corrosion spots on the same up weld distance, but with different cossorion spots.

As you can see, there is one corrosion spot in 2007 that has a up weld difference of 0,15 and a clock position of 0,42
In 2012 there are two corrosion spots which match the up weld distance with 0,16, but have different clock positions: 0,41 and 0,29. The one with the clock position of 0,41 matches!

Here is my pseudo code:

Macro plan

Sorting (up weld distance)

1. Select cell in up weld distance column of the 2007 range.
2. Compare that value to the 2012 up weld distance in that row.
   • If they match (within a certain margin):
     1. Change nothing.
     2. Select next cell
   • If they do not match:
     1. Select entire data row of 2007
     2. Shift cells down (made a macro for that)
3. Repeat step 2.

In most cases, there will be several corrosion spots with roughly the same up weld distance. In order to separate those, additional sorting based on clock position is needed. This will have to be done after sorting based on up weld distance, without changing the rest of the data.

Sorting (clock position)

1. Select cell in up wed distance column of 2007 range.
2. Compare value to all up weld distances in 2012 range.

• If number of matches is 1:

1. Change nothing.
2. Select next cell with data in it.

• If number of matches is greater than 1:

1. Select clock position of 2007 in the same row as the up weld distance.
2. Compare to clock position of 2012.
   • If they match (within a certain margin):

1. Do nothing.
   • If they do not match:
     • Select entire 2007 row.
     • Shift cells down.

3. Repeat.

This double sorting will ensure an easy and secure way to match the corrosion spots found on the pipeline segments.

I hope I have posted this correctly (probably not). I hope you are informed correctly and sufficiently in order to help me, since I've been stuck on this for a long time now.

Thanks in advance,

Merriënboer.

EDIT: I'm using excel 2003 and Windows XP

 

[oldbrewer]

if upweld distance is 3.4 and clock position is .23 that is a unique location. Make a table so that clock 0 = A clock 0.1 = B etc so clock .23 = X.

look up .23 in that table and X is returned, concatenate 3.4 and X which is 3.4X

if there is a 3.4X in 2007 results there will be 3.4X in 2012 results
if there is a 1.9F in 2012 but not in 2007 that is a new corrosion point

adjust the precision of the lookup table to suit, it will return the next highest (or lowest) match.

 

[Merriënboer]

if upweld distance is 3.4 and clock position is .23 that is a unique location. Make a table so that clock 0 = A clock 0.1 = B etc so clock .23 = X.

look up .23 in that table and X is returned, concatenate 3.4 and X which is 3.4X

if there is a 3.4X in 2007 results there will be 3.4X in 2012 results
if there is a 1.9F in 2012 but not in 2007 that is a new corrosion point

adjust the precision of the lookup table to suit, it will return the next highest (or lowest) match.

Thanks. How would one accomplish this? There are several hundreds of these pipeline sections in one pipeline, so it has to be automated.

 

[oldbrewer]

I assume each pipeline section has its own unique identity - so building on my example pipe7 would be pipe7_3.4x

pipe11_3.4x would not be confused with this.

when your new data comes in, concatenate pipe length identity, distance and clock position then sort by this parameter so all pipe 1 results would occur first. compare with previous results as desired.