Finding nearest tables based on latitude and longitude columns

Hey all

I have two cells; cell A and cell B. Cell A is 11 x 1, and cell B is 200 x 1. These cells include tables.

Each table in both cells has a column named lat (latitude) and lon (longitude). Latitude and longitude are constant in each table but different in A and B.

I mean, I have some points (longitude and latitude) in cell A and some other points with different latitude and longitude in cell B.

I want to find which table in cell B is the nearest to the tables in cell A, based on latitude and longitude columns (Euclidean distance).

I want to store every two tables that are nearest to each other.

So, in the end, for every 11 tables in A I would have 11 other tables from B that are nearest to A tables.

% I know it maybe something like this but I don't know rest of it%Finding nearest station one-by-one
dist = sqrt((A.lon - A.lon').^2 + (B.lat - B.lat').^2);
dist(dist == 0) = Inf;
[~,closest_id] = min(dist);

I attached A and B here.

Thank you all in advance.

[Star Strider]

For accurate geographical distance, use great circle distances, not rhumb line distances.

[BN]

You are right. But I have only latitude and longitude and the elevation does not exist for my data (for A).

Thank you

Answer 1

[Adam Danz]

Inputs

• A is a 1xn or n1x cell array of tables; each table contains the headers 'lat' and 'lon' which are constants.
• B is a 1xm or mx1 cell array of tables with the same descrtiption as A.

Output

• minIdx is a nx1 vector of index values (positive integers) that identify the tabels listed in B that are closest to A (Euclidean distance). So, the table B(minIdx(i)) is closest to table A(i).

% Function that gets the [lan lon] coordinates for all elements of a cell array.
% input: c is a cell array (ie, A or B), each element is a table with variables "lat" and "lon". 
% Those two columns contain constant values.  
% output: nx2 matrix of [lat,lon] values. 
getLatLon = @(c)cell2mat(cellfun(@(a){[unique(a.lat), unique(a.lon)]},c(:))); 

% Create grid of distances between all stations. 
% distGrid is nxm matrix where distGrid(i,j) is the distance 
% between station i in A and station j in B. 
distGrid = pdist2(getLatLon(A), getLatLon(B)); 

% For each station in A, find the closest station in B.
% minIdx(i) is the index of B closest to station i in A.
[~, minIdx] = min(distGrid,[],2)

You can finish it from here ;)

[BN]

Dear Adam Danz,

Thank you for your answer. Actually, in the first step, I ran this code and it gave me the following error:

Error using pdist2 (line 158)
X and Y must have the same number of columns. 

Then I use B = B(1:10) to make it work and see the results (Although the B is 1 x 100 I did this temporary to see what is the result).

After that code runs successfully. For see what is the output I used this:

A(minIdx(10));

To see what station in B is near to 10th station of A (for example), and it gave me this error:

Index exceeds the number of array elements (1). 

I want to tell that latitude and longitude are different in each table, But inside each table, they are constant in each row (each table belongs to one point).

Is this any way to have every 2 tables (one table and one nearest table) together in something like a cell? So I can use this cell for future analysis.

I really thank you for your precious help ?

[Adam Danz]

I just opened Matlab release 2018b (the only you're using, apparently) and it works fine with the data you provided.

Please copy the code from my answer again and apply it to the data you provided.

[BN]

Thank you. I downloaded the data that I attached. But unfortunately, I don't know why this error happens. Here is the screenshot:

[Adam Danz]

I can't see it because the text is cut off but I think you're missing this

getLatLon = @(c)cell2mat(cellfun(@(a){[unique(a.lat), unique(a.lon)]},c(:))); 
% Here----------------------------------------------------------------^^^^

The c(:)

Or, perhaps there's a transpose at the end which shouldn't be there (it was in my first answer but I corrected it soon after).

getLatLon = @(c)cell2mat(cellfun(@(a){[unique(a.lat), unique(a.lon)]},c(:))'); 
% This shouldn't be there -------------------------------------------------^

[BN]

Thank you so much ?