Best match location data

1 次查看（过去 30 天）
AHMED FAKHRI
AHMED FAKHRI 2021-6-9
评论： AHMED FAKHRI 2021-6-10
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 个评论
显示 2更早的评论
AHMED FAKHRI
AHMED FAKHRI 2021-6-9
Here is the excel file if it is helpful

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Amy Haskins
Amy Haskins 2021-6-9
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 个评论
AHMED FAKHRI
AHMED FAKHRI 2021-6-10
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 2021-6-10
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

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Bar Plots 的更多信息

标签

产品


版本

R2020b

Community Treasure Hunt

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

Start Hunting!

Translated by