how to average data points in column 3 based on values in columns 1 and 2

I have a bunch of single beam data points, about 50k points. I want to interpolate between them in a different bathymetric processing software. The data is a combination of two datasets (different surveys), meaning they were collected at different times, so the problem is the depth is not accurate. One survey is deeper than the other likely because of the tides, however the surveys cover the exact same area but at different approach angles (see picture). So when I plot them I have two sets of track lines that dont match up, when looking at my CSV file I have a lot of points that are very similar but not exact. Ive tried filtering unique values in excel and deleting duplicates, only 14/50000 were exactly the same. My question is how do I average these two surveys? Im honestly not sure where to start and there are probably numerous ways to do this so im getting lost in my research.

My goal is to bring these two surfaces to the same depth, and eventually smooth the lines enough to remove the bumpy surface before exporting the points to my other bathymetric program and finally export a geotiff to GIS. Thanks agian, any help is really appreciated!

I forgot to add an example of the data points:

As you can tell some of these points are extremely close together, what I want to do is find ordered pairs in columns 1 and 2 that are very similar and replace all the corresponding values in column 3 with their average. Hopefully any of this makes sense.

Overview of tracklines:

Zoomed in section showing the difference in depth:

Heres my code to visualize the data:

[D,S] = xlsread("BathyScriptTest.xlsx");
lat = D(:,1);
lon = D(:,2);
dep = D(:,3);

depS = smooth(dep);

figure
plot3(lat, lon, depS, '.')
grid on 
view(35,60)
title('Bathy Lines')

[Image Analyst]

You forgot to attach "BathyScriptTest.xlsx" so I didn't pay much attention after that. I'll check back later.

If you have any more questions, then attach your data and code to read it in with the paperclip icon after you read this:

TUTORIAL: How to ask a question (on Answers) and get a fast answer

[Bradley]

Attached. Thanks for the tips and taking a look at my data. The only code I have so far is included in the post. Thanks again.

Answer 1

[Mathieu NOE]

hello

as your data seemed to lie in a plane , why not make a plane fit and assume from now the data should belong to that plane ?

use the attached function to run the code

no smoothing required here (I haven't found any added value here - my personnal choice)

code :

[D,S] = xlsread("BathyScriptTest.xlsx");
lat = D(:,1);
lon = D(:,2);
dep = D(:,3);

% remove some outliers 
mdep = mean(dep);
ind = abs(dep-mdep)<1;
lat = lat(ind);
lon = lon(ind);
dep = dep(ind);


figure
plot3(lat, lon, dep, '.')
hold on
grid on 
view(35,60)
title('Bathy Lines')

%compute the normal to the plane and a point that belongs to the plane
ind = (1:50:numel(lat));
XYZ = [lat(ind) lon(ind) dep(ind)];
[n_1,~,p_1] = affine_fit(XYZ);

%plot the two adjusted planes
[Xf,Yf] = meshgrid(lat(ind),lon(ind));

%fitted plane
Zf =  - (n_1(1)/n_1(3)*Xf+n_1(2)/n_1(3)*Yf-dot(n_1,p_1)/n_1(3));
surf(Xf,Yf,Zf,'edgecolor','none','facecolor','red','facealpha',0.25);
zlim([mdep-5 mdep+5]);

figure
depf =  - (n_1(1)/n_1(3)*lat+n_1(2)/n_1(3)*lon-dot(n_1,p_1)/n_1(3));
plot3(lat, lon, depf, '.')
hold on
grid on 
view(35,60)
title('Bathy Lines')
zlim([mdep-5 mdep+5]);

[Bradley]

Awesome, thanks! For some reason Im getting an error on line 20:

Unrecognized function or variable 'affine_fit'.

What does this function do? Thanks again!

[Mathieu NOE]

hello

that function was in attachment in my answer , you need to download it and put it in your path or working directory

again here below :

function [n,V,p] = affine_fit(X)
    %Computes the plane that fits best (lest square of the normal distance
    %to the plane) a set of sample points.
    %INPUTS:
    %
    %X: a N by 3 matrix where each line is a sample point
    %
    %OUTPUTS:
    %
    %n : a unit (column) vector normal to the plane
    %V : a 3 by 2 matrix. The columns of V form an orthonormal basis of the
    %plane
    %p : a point belonging to the plane
    %
    %NB: this code actually works in any dimension (2,3,4,...)
    %Author: Adrien Leygue
    %Date: August 30 2013
    
    %the mean of the samples belongs to the plane
    p = mean(X,1);
    
    %The samples are reduced:
    R = bsxfun(@minus,X,p);
    %Computation of the principal directions if the samples cloud
    [V,D] = eig(R'*R);
    %Extract the output from the eigenvectors
    n = V(:,1);
    V = V(:,2:end);
end

[Mathieu NOE]

I changed a bit my mind this morning, so I reintroduced the smoothing as you did in first place

I opted for smoothdata with movmedian window

this way I don't need to specify the limits above / below which I want to remove outliers. Simpler !

Final results looks better IMO :

also replaced the obsolete xlsread with readmatrix

code updated :

D = readmatrix("BathyScriptTest.xlsx");
lat = D(:,1);
lon = D(:,2);
dep = D(:,3);
depS = smoothdata(dep,'movmedian',500);

figure
plot(dep)
hold on
plot(depS)
hold off


figure
plot3(lat, lon, depS, '.')
hold on
grid on 
view(35,60)
title('Bathy Lines')

%compute the normal to the plane and a point that belongs to the plane
downsampling = 100; % to reduce risk of out of memory" in affine_fit
ind = (1:downsampling:numel(lat));
XYZ = [lat(ind) lon(ind) depS(ind)];
[n_1,~,p_1] = affine_fit(XYZ);

%plot the two adjusted planes
[Xf,Yf] = meshgrid(lat(ind),lon(ind));

%fitted plane
Zf =  - (n_1(1)/n_1(3)*Xf+n_1(2)/n_1(3)*Yf-dot(n_1,p_1)/n_1(3));
surf(Xf,Yf,Zf,'edgecolor','none','facecolor','red','facealpha',0.25);

figure
depf =  - (n_1(1)/n_1(3)*lat+n_1(2)/n_1(3)*lon-dot(n_1,p_1)/n_1(3));
plot3(lat, lon, depf, '.')
hold on
grid on 
view(35,60)
title('Bathy Lines')

[Bradley]

Awesome! Thank you so much this worked perfectly, I exported depf as depth and imported that with lat and long to my bathymetry program (EIVA) and the result is below. It looks great! Were not interested in fine detail of the seafloor, just the depth gradient so this worked great. Thanks again!

[Mathieu NOE]

Glad it worked !!

all the best