Best match location data

10 views (last 30 days)
AHMED FAKHRI
AHMED FAKHRI on 9 Jun 2021
Commented: AHMED FAKHRI on 10 Jun 2021
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.
  4 Comments
AHMED FAKHRI
AHMED FAKHRI on 9 Jun 2021
Here is the excel file if it is helpful
dpb
dpb on 9 Jun 2021
See
doc pdist2

Sign in to comment.

Accepted Answer

Amy Haskins
Amy Haskins on 9 Jun 2021
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
  2 Comments
AHMED FAKHRI
AHMED FAKHRI on 10 Jun 2021
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
AHMED FAKHRI on 10 Jun 2021
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

Sign in to comment.

More Answers (0)

Products


Release

R2020b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!