# Finding nearest tables based on latitude and longitude columns

3 views (last 30 days)
Behzad Navidi on 13 Feb 2020
Commented: Behzad Navidi on 13 Feb 2020
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.

Star Strider on 13 Feb 2020
For accurate geographical distance, use great circle distances, not rhumb line distances.
Behzad Navidi on 13 Feb 2020
You are right. But I have only latitude and longitude and the elevation does not exist for my data (for A).
Thank you

Adam Danz on 13 Feb 2020
Edited: Adam Danz on 13 Feb 2020
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 ;)

Behzad Navidi on 13 Feb 2020
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 on 13 Feb 2020
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 -------------------------------------------------^
Behzad Navidi on 13 Feb 2020
Thank you so much ?