I have found respective latitude & longitude points from an excel file-- I want to export the data that satisfies both restrictions on the lat & long (matching rows).

조회 수: 4(최근 30일)
Naomi Mathew
Naomi Mathew 2022년 5월 16일
댓글: dpb 2022년 5월 24일
I have aquired this "historic" shipping data, where each day is saved as a single excel file. I have attached an example of the files to this inquiry. I cannot attach the orginal- it is too large. I have combined all the excels for the whole year into a single folder. Here is the part of the code that works the way I want it:
rootdir = 'F:\shipping_data\2018';
% get info about all xlsx files in directories directly under
% rootdir (this matches the description of your situation):
filelist = dir(rootdir);
% do this instead if you want get info about all xlsx files in all
% Extraction condition I want when it comes to the latitude/longitude max
% and min points, respectively.
latLim = [29.5, 26] ;
lonLim = [-87, -90] ;
% read each file, store the results:
for ii = 3:numel(filelist)
%I now tell matlab exactly which files to read.
%I start at 2 as i don't want it to read the header to mitigate confusion
data = readcell(fullfile(filelist(ii).folder,filelist(ii).name),...
Essentially, as I understand it, I have shorted the actual range length, so amount of rows, as I want to make sure the code works before i wait for it to run it all. I have set the latitude and longitude limits I wish for it to cut to.
I have gotten to the point where I can ask matlab to let me know which rows satatisfy this in my data:
%Here I tell matlab exactly where I want it to look for these limits
%I ultize cell2mat as my data can only be read as a cell structure and it doesn't work with the command below
%for my excel sheets lat. is found in column 2
idxLat = find(cell2mat(data(:,2)) >= latLim(2) & cell2mat(data(:,2)) <= latLim(1))
%for my excel sheets long. is found in column 3
idxLong = find(cell2mat(data(:,3)) >= lonLim(2) & cell2mat(data(:,3)) <= lonLim(1));
What I need to do is have it combine all the data that is satisfied by both idxLat and idxLong in order to get the ship data points found in this specific area in the Gulf of Mexico.
I tried codes like:
% I used this to try and see I could just combine both aggressively...
idxMatch = (find(cell2mat(data(:,2)) >= latLim(2) & cell2mat(data(:,2)) <= latLim(1))) & (find(cell2mat(data(:,3)) >= lonLim(2) & cell2mat(data(:,3)) <= lonLim(1)));
% error reads: Error using & Matrix dimensions must agree.
%found this from another answer, and it seems to work... no error
if all(idxLat) && all(idxLat)
disp('this area');
% however I do not know if is giving me the answer I want- it does only display 'this area'as I asked it to
Thank you!


dpb 2022년 5월 16일
Making it harder than need be -- first, use readtable instead of readcell()...
for your sample file results in
>> head(tAIS)
ans =
8×10 table
BaseDateTime LAT LON SOG COG Heading VesselName IMO CallSign VesselType
____________________ ______ _______ ___ ______ _______ ____________________ ______________ ___________ __________
18-May-2018 00:00:00 60.086 -149.35 0 -187.1 511 {'LOIS ANDERSON' } {'IMO7203015'} {'WDD9476'} 30
18-May-2018 00:00:01 29.815 -93.959 0 126 511 {'BELLAGIO' } {0×0 char } {'WDA9457'} 60
18-May-2018 00:00:01 33.206 -117.39 0 -49.6 511 {'OCEANSIDE 95' } {0×0 char } {'WDJ6255'} 60
18-May-2018 00:00:02 29.913 -81.275 0.1 -144 511 {'SAVANNAH' } {0×0 char } {0×0 char } 90
18-May-2018 00:00:03 39.892 -75.192 0 -128.1 127 {'SUN COAST' } {'IMO8994453'} {'WTM3609'} 31
18-May-2018 00:00:05 40.705 -73.973 0.2 0 511 {'WILLIAM M FEEHAN'} {0×0 char } {'WDH9474'} NaN
18-May-2018 00:00:05 48.511 -122.64 0 -65.2 511 {'OCEAN MAID' } {'IMO7307550'} {'WDE8215'} 30
18-May-2018 00:00:05 46.732 -92.072 0 -129.6 511 {'ALPENA' } {'IMO5206362'} {'WAV4647'} 70
At this point I'd probably go ahead and use table2timetable to turn it into a timetable instead of table because you'll undoubtedly be wanting to search on time as well and the timetable has builtin features to facilitate that.
Then, to return what you want in terms of LAT, LON values, simply
where iswithin is my little utility function that makes writing the conditional expression more legible at user level --
function flg=iswithin(x,lo,hi)
% returns T for values within range of input
% [log] = iswithin(x,lo,hi)
% returns T for x between lo and hi values, inclusive
flg= (x>=lo) & (x<=hi);
I wrote it expecting three inputs; never did get around to fixing it up for an array; that would be a fairly easy enhancement.
The sample file is empty for that particular set of coordinates; but
>> tAIS(iswithin(tAIS.LAT,27.5,32.5)&iswithin(tAIS.LON,-105,-95),:)
ans =
4×10 table
BaseDateTime LAT LON SOG COG Heading VesselName IMO CallSign VesselType
____________________ ______ _______ ___ ______ _______ __________ __________ __________ __________
18-May-2018 00:00:00 29.732 -95.277 5.8 168.1 511 {0×0 char} {0×0 char} {0×0 char} NaN
18-May-2018 00:00:00 27.816 -97.456 0 -175.4 150 {0×0 char} {0×0 char} {0×0 char} NaN
18-May-2018 00:00:04 29.759 -95.118 1.9 -132.3 511 {0×0 char} {0×0 char} {0×0 char} NaN
18-May-2018 00:00:01 27.838 -97.052 0 -156.9 511 {0×0 char} {0×0 char} {0×0 char} NaN
shows it works for a set of values that are in the data set...
  댓글 수: 14
dpb 2022년 5월 24일
No problem, glad to help/try to teach along the way...that's a pretty common newbie mistake/oversight so don't feel bad there. Glad you were able to spot the issue.

댓글을 달려면 로그인하십시오.

Community Treasure Hunt

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

Start Hunting!

Translated by