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).

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),...
       'Range','B2:K39');

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.

or

    %found this from another answer, and it seems to work... no error
  if all(idxLat) && all(idxLat)
    disp('this area');
    end
   % 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!

Answer 1

[dpb]

Making it harder than need be -- first, use readtable instead of readcell()...

tAIS=readtable('AIS_2018_05_18_ex.csv','Range','B:K');
tAIS.BaseDateTime=datetime(tAIS.BaseDateTime,'InputFormat',"uuuu-MM-dd'T'HH:mm:ss");

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

tAIS(iswithin(tAIS.LAT,latLim(1),latLim(2))&iswithin(tAIS.LON,lonLim(1),lonLim(2)),:);

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
% SYNTAX:
%  [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...

[Naomi Mathew]

Hey thanks for responding! It seems to be a trend- me making things harder than they need to be haha

Firstly, I need it to read all the csv files in the folder so I need to utlize my first two lines, I believe. Secondly even when I tried it just the way you have it (to test it), it gives me an error specifically on the parameter name:range..

rootdir = 'F:\shipping_data\2018';
% get info about all csv files in directories directly under 
filelist = dir(rootdir);
% do this instead if you want get info about all xlsx files in all 

% read each file, store the results:
for ii = 3:numel(filelist)
tAIS=readtable(fullfile(filelist(ii).folder,filelist(ii).name),...
    'Range','B2:K39');

The error reads:

Error using readtable (line 216)

Invalid parameter name: Range.

Error in collectship_2018_ex_2 (line 10)

tAIS=readtable('AIS_2018_05_18_ex.csv','Range','B:K');

any advice? I went through the help options and tried the different ways... to no avail.

[dpb]

Which release of MATLAB are you using? That works here w/ R2020b; I'm not sure how much earlier versions would be that didn't allow 'Range' for a .csv file with readtable and the doc doesn't provide that level of detail on changes, at least not without perusing all the change notifications by every release.

The easiest if you can would be to upgrade your version -- then it works as is.

Second easiest would be to save the file as a workbook file instead of as .csv (unless there's some other reason it must be a csv file).

Third is to remove the 'Range' parameter and then just delete the rows/columns not wanted...in general I'd not put any range argument other than a column range as I did -- to put a rectangular range in requires knowing a priori the precise number of lines which, more often than not, isn't actually known and requires fixing up the argument list in the code every time it changes. Better to read what's there and then use logic to programmatically select only the portons wanted.

tAIS=readtable('AIS_2018_05_18_ex.csv');
tAIS(1:39,ExcelCol('B'):ExcelCol('K'))'

[Naomi Mathew]

So you were right -- my version was outdated. I was using a 2019 version. I am now using R2021a.

All of this works:

rootdir = 'F:\shipping_data\2018';

filelist = dir(rootdir);


% read each file, store the results:
for ii = 3:numel(filelist)
   %I now tell matlab exactly which files to read.
   tAIS=readtable(fullfile(filelist(ii).folder,filelist(ii).name),...
    'Range','B1:K39');

tAIS.BaseDateTime=datetime(tAIS.BaseDateTime,'InputFormat',"uuuu-MM-dd'T'HH:mm:ss");

timetAIS = table2timetable(tAIS);
 %table2timetable to turn it into a timetable instead of table 
 latLim = [29.5, 26] ;
 lonLim = [-87, -90] ;
 

% combine all the data, one after the other:
all2018_data = tAIS(iswithin(tAIS.LAT,latLim(2),latLim(1))&iswithin(tAIS.LON,lonLim(2),lonLim(1)),:);

But i do get an error with the "iswithin"... it says it is an unrecognized function or variable. Same if I try "within"

I tried isbetween and it says "Check for incorrect argument data type or missing argument in call to functiion 'isbetween'.

Thank you again for your continued help.

[dpb]

Yes, as I said, that's my own utility -- that's why I pasted the text for it at the bottom...take that and put it in an m-file in your working directory "iswithin.m"

I have a permanent subfolder of my default working folder on the MATLABPATH called "UTILITIES" that holds stuff like this that is generically useful rather than project-specific.

[Naomi Mathew]

hey! ok so i had it run, I did it a few times and it ran! So thank you:) I do have a question though with your "iswithin.m" I created it too and made it in my working directory. I was wondering what you changed your (x,lo,hi) to with in it? i have attached what I did. This is what I did to check it today.

I tried it as a combination of the two the first round I ran my code and it only produced this data, which I know is not right-- the excel is attached as well. it is missing a good chunk for sure.

The end goal for this is to make a combined excel sheet with all these data points

all2018_data = timetAIS(iswithin(timetAIS.LAT,28,26.5)&iswithin(timetAIS.LON,-88.5,-89.5),:);
T = all2018_data;
 filename = 'test2018_data.xlsx';

% putting data outside of where you searched for the data
% writetable will create the excel file needed 
outputdir = 'F:\shipping_data\2018\COMBINED';
writetimetable(T, filename);

end

Is this the right way to go about it? Is this why its not working?

[dpb]

The above code looks right; I'm comfortable iswithin works as is intended.

I have other commitment at noon that am getting ready for now so may not be able to check your results until later. I don't know what the image is to represent, but

>> iswithin(-89.4,-89.5,-88.5)
ans =
  logical
   1
>> 

returns the true result as expected, so when used as a logical addressing vector into an array would return whatever locations were within that range. But, it isn't useful to call iswithin on a constant other than to use that result as a logical branch test or such; it is designed to be used as the above code uses it; the logical addressing expression into an array.

[dpb]

I had a few minutes before having to head to town -- the data file you attached can't be from the above code; it doesn't have anything that would satisfy those conditions.

Would have to have the original input file to be able to debug anything as the attached is only 7 records and the original, while about 50 records, didn't have any data in it that satisfied the particular search range.

You can test by doing it sequentially -- first extract the range for latitude from the full table, then extract the desired longitude values from that result. It should (will) match the result of the "&" operation.

I've gotta' go...

[dpb]

Got home from meeting; came to check if you'd gotten back. I see something this time I didn't notice in the very quick look before

iswithin(timetAIS.LAT,28,26.5) & iswithin(timetAIS.LON,-88.5,-89.5)

will return the same as the ~ applied to the expression

iswithin(timetAIS.LAT,26.5,28) & iswithin(timetAIS.LON,-89.5,-88.5)

because xlo > xhi above for both LAT and LON. That would explain the values being outside the expected limits. iswithin did precisely what you asked it to do -- return everything NOT within those ranges.

I didn't think to add in the error checking to test that the user input limits were, indeed in expected order inside iswithin. It's easy to confuse for negative values and consider only the absolute magnitude instead of the actual order, but you also reversed the positive values for LAT in that 28>26.5

Fix your above expression to be

all2018_data = timetAIS(iswithin(timetAIS.LAT,26.5,28)&iswithin(timetAIS.LON,-89.5,-88.5),:);

and joy will ensue.

As a general programming practice, it would be better to do as in the previous example -- put the constants in separate variables and then pass those references instead of burying "magic" numbers inside the code itself. Then it is much easier to just change the constants for multiple cases/studies.

You can prevent such errors if you were to add

function flg=iswithin(x,lo,hi)
% returns T for values within range of input
% SYNTAX:
%  [log] = iswithin(x,lo,hi)
%      returns T for x between lo and hi values, inclusive

  assert(lo<=hi,'Error iswithin: lo value not less than or equal hi value')
  flg= (x>=lo) & (x<=hi);
>> 

and you would have received an error on the above until fixed both sets of inputs.

[Naomi Mathew]

Thank you thank you thank you!!

With your first comment back yesterday, I understood where I was going wrong. I had noticed that my numbers needed to switch positions (26.6, 28) as well. I got the code running and it produced what I needed it to. (For that check, I tested the code with only three of the excel files out of the 226 i needed.

After that was a success, I tried to run it for all 226 files, it took serveral hours (granted I had a feeling it would). I came back this morning to find it only saved the data from 12/17/2018-12/31/2018... do you have any advice on why it did not save all the data from my starting point of 05/18/2018?

Thanks again for all your help!

[dpb]

Again, not without the identical code you ran and some sample file data that is sufficient to actually test, can only conjecture...

I'm surprised this code would take that long to run even over 200+ files unless every file is extremely large -- this should be pretty efficient. But without data and code can see, simply can't even guess why it wouldn't return/save something you think it should.

A first guess would be perhaps the date conversion didn't work on some of the early data files -- maybe format changes or somesuch???

Does your code have any intermediary outputs to screen to tell you what it's processing and whether there are NaT values or other diagnostics to let you know if something isn't working as expected?

[Naomi Mathew]

1. They have about 1048576 rows in each file... I can't upload an example flie, even if I zip it.. its too large. I did however get my data from: https://www.marinecadastre.gov/ais/#:~:text=Vessel%20traffic%20data%2C%20or%20Automatic,international%20waters%20in%20real%20time.
2. maybe -- I am going to try it with a larger subset, but having it be able to go through the data a bit faster.
3. It does not have intermediary outputs to screen as I do not know how to do it. Time to do some googling:)

[dpb]

<disp> and/or <fprintf> or <compose>

Or, you can leave off the trailing seemicolon of certain key lines and will echo to command window automagically -- NB here, however, updating one cell/index value of an array will cause the whole array to be dumped to the screen, so be careful about just what you do this with.

I have a bunch of other things to get done so don't know that I'd get to it any time soon, I guess if you would point out some particular file and region that didn't work as you expected, I might have time later on to take a look at it.

But, the better starting point here would be to post your complete code as you're running it now so it can be looked at by those here to see if can spot areas that could be made more efficient.

So far, we've only seen bits/pieces during development, not the entire thing in one clean post.

[Naomi Mathew]

I thank you for your help. I see what i need to do now to correct this. My data is writing over itself. Just need to correct that.

I really do appericiate you taking time to help me out.

[dpb]

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.