Best match location data
조회 수: 3(최근 30일)
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
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.
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
% 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);
% 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