Best match location data

Hi

I have location data (longitude and latitude) for certain sites and I want to compare these two with a national databse to know the local authority of these sites + their postcodes.

I have two tables:

Sitename	                Latitude	       Longitude
14182-Pembroke Refinery	    51.686081	       -5.0271217
8059-New Rugby	            52.376283	       -1.2860374
8037-Ketton	                52.636537	      -0.54737666

And

Postcode	Local authority	  Longitude	   Latitude
CV21 2RY	Rugby	          -1.288555	52.376856
TR26 2JQ	Cornwall	      -5.490944	50.207428
SY10 7RN	Shropshire	     -3.067703	52.917641
SA71 5SJ	Pembrokeshire	  -5.02713	51.686093
PE9 3SX	    Rutland          -0.5462	52.636828

By best matching both the latitude and longitude data from the first table with the second, the postcode and local authority will be known.

Output: Sitename, Postcode, Local authority,

I appreciate your help with this.

[Joel Lynch]

Without knowing how this data is stored in MATLAB's workspace, or how it is stored in external data files, it is impossible to provide a clear answer.

Also, are you intending to locate the closest coordinates? Or are you expecting exact lat/long matches?

[AHMED FAKHRI]

Hi Many thanks for your reply. The data can be stored in Excel and imported as two Tables to Matlab workspace as in the above tables.

Not exact, the nearest lat/long to the first table should be located from the second table.

So if you see Table 1 above , for the first site will take the fourth row in the second table as the lat-long are closest

[AHMED FAKHRI]

Here is the excel file if it is helpful

[dpb]

See

doc pdist2

Answer 1

[Amy Haskins]

The following appoach might not scale well if you have a very large number of sites / postcodes to deal with, but might help you get started in the right direction.

% I copied your sample data exactly as shown into flat text files.  
% readtable can also handle Excel files, but it will be easier if you split
% the tables into separate sheets.
sites = readtable("sites.txt")
postcodes = readtable("postcodes.txt")

% Initialize variables
numSites = height(sites);
minDist = nan([numSites,1]);
authorityIndex = nan([numSites,1]);

for ii = 1:numSites
    % Find the distance in meters from the current site to each of 
    % the post code lat/lons using a Mapping Toolbox function.  
    % This is important since at high latitudes, 2 points a degree apart in
    % Longitude are much closer together than 2 points a degree apart in
    % Latitude.  
    % D will be of size numSites x 1.
    D = distance(sites.Latitude(ii),sites.Longitude(ii),codes.Latitude,codes.Longitude,wgs84Ellipsoid,'degrees');
    
    % Call the min function with the optional output arg to give you the
    % index of the authority with the smallest distance from the site.  
    % Store for each site in the loop.
    [minDist(ii),authorityIndex(ii)] = min(D);
end

% I added the authority info back to the original sites table, but you could make
% a new table with just the desired fields instead.
sites.Authority = codes.authority(authorityIndex);
sites.PostCode = codes.Postcode(authorityIndex);
sites.Local = codes.Local(authorityIndex);
sites.Distance_meters = minDist

[AHMED FAKHRI]

Many thanks @Amy Haskins for your answer. Actually I have around 600 sites ( Table 1 ) to be matched to around a large database ( 1.7 million row- Table 2) to know the local authority of these sites.

[AHMED FAKHRI]

Many thanks @Amy Haskins

I slightly edited your code with the full tables that I have and it initially worked. In terms of accuracy, I will check