Hi @wyat.appel et al.,
I’ve installed AMET v1.4 and using it now, and having some questions about it. Just wonder can you help me to find the potential reasons.
-
Inserting metadata for station 01-001-0003 for toar network
Error in .local(conn, statement, ...) :
could not run statement: Unknown column 'co_network' in 'field list'
[1] "Failed to add sites to database with the error: Error in .local(conn, statement, ...) : \n could not run statement: Unknown column 'co_network' in 'field list'\n."
is this related to https://github.com/USEPA/AMET/issues/78
does this impact the data structure on database?
- I am testing the tool using example data downloading from UNC google drive,
batch plots and most plots went OK. However, I found some glitches in soccerplot,
If I only assign one AMET_AQSPECIES, it works fine. I’ve tried most species they are OK, eg
setenv AMET_AQSPECIES O3
However, if I assign multiple species
setenv AMET_AQSPECIES O3, EC, PM_TOT
it returns
Error in lsfit(data.df$ob_val, data.df$mod_val) :
only 0 cases, but 2 variables
Calls: DomainStats -> lsfit
In addition: Warning message:
In lsfit(data.df$ob_val, data.df$mod_val) : 315825 missing values deleted
I modified R and output
stats_all.df
data_all.df
but there are some issues when matching up obs and mod data, I double check the mod data are not organized correctly.
any suggestion will be very helpful
Thanks
Joey
Hi Joey,
Let me quickly answer your first question, and then I’ll get back to you on your second question. You are correct. The error you are getting regarding co_network is the same as described in the thread you linked. So, you can use the solution described in that thread to fix the issue you’re having. Let me know if you still have trouble after implementing that fix.
I’ll get back to you on your second issue once I have a chance to sort through it. Hopefully something simple.
Wyat
Wyat,
Thanks for the quick response, but I do have this correction in my script.
co_network varchar(200)
I compared the data_all.df (v1.3 and v1.4), I noticed there is a column shift between v1.3 and v1.4.
Joey
Is this data.all.df specifically from the soccergoal plot script? It sounds like most things were working for you, so I assume this is related specifically to the soccergoal plot.
Wyat
I changed a line in AQ_Soccerplot.R “l” starting from 9 instead of 10, and this fixed the issue
# l <- 10
l <- 9 #by JH
for (i in 1:length(species)) { # For each species, calculate several statistics
data_all.df <- data.frame(network=I(aqdat_query.df$network),stat_id=I(aqdat_query.df$stat_id),lat=aqdat_query.df$lat,lon=aqdat_query.df$lon,ob_val=aqdat_query.df[,l],mod_val=aqdat_query.df[,(l+1)]) # Create properly formatted dataframe to use with DomainStats function
good_count <- sum(!is.na(data_all.df$ob_val)) # Count the number of non-missing values
Does this mean my data structure in database is corrupted ?
this is possible due to the co_network column issue ?
Joey
Here’s, I believe, a fix to the issue. In the AQ_Soccerplot.R script (found in AMETBASE/R_analysis_code), please make the following minor change to line 76:
l ← 10
to
l ← 9
I believe there may be an indexing issue I corrected in my most recent (yet to be released) code. Hopefully that corrects the problem you’re seeing in the 1.4 code.
Let me know if that works or not. Thanks!
Wyat
Yes, that is what I did, thanks!!!
How about the CO network column issue?
Thanks
So are you still receiving the same error you originally reported relating to the co_network field?
Unknown column ‘co_network’ in ‘field list’
Yes, I have the correction in my AQ_setup_dbase.R, but still showing the error.
Gotcha. So, adding the co_network varchar(200) to the script will fix the problem for any future databases you create, but does not fix the problem with the existing database, specifically in the site_metadata table. To fix the existing table (without starting from scratch), you can do the following, which I pasted for the thread discussion you linked discussing this issue. If you’re comfortable working with MySQL, I suggest the third option I present below.
Note that if you’ve already run the AMET setup, you’ll need to either manually delete the “site_metadata” table from your AMET database so that it can be recreated without the column missing, or delete your entire AMET setup and start fresh using the script provided with the AMET code. I would recommend that if you’ve already done analysis and loaded data into the database that you attempt to manually delete “site_metadata” table and have it recreated automatically when you run the aqExample_post_only.csh script.
A third option would be to simply edit your existing “site_metadata” table in the MySQL database to add the missing column manually. This can be accomplished by logging into MySQL with your username and password, switching to your AMET database, and then editing the existing table. An example is given below.
> mysql -h mysql_host_name -u user_name -p
> enter password
> use AMET_DATABASE;
> alter table site_metadata add column co_network varchar(200) after network;
> exit
You should then re-run your aqExample_post_only.csh script with the reload_metadata flag set to T. That should then properly load your site metadata.
1 Like
Does this impact AMET v1.3 data upload?
I have v1.3 and v1.4 on the same machine, and I will still use v1.3 for model evaluation, before I am 100% sure v1.4 is working properly. I am worrying this might mess up the v1.3 settings?
Thanks
Sorry for the delay getting back to you. So, do you have v1.3 and 1.4 data in the same AMET database? Or do you have separate databases for your v1.3 and v1.4 work?
I don’t think it should actually matter. The change I’m proposing simply adds an additional column to the site_metadata table (each database has it’s own site_metadata table). The error you note above occurs because AMET is trying to populate the site_metadata table with co_network data, but the co_network column does not exist. By adding the co_network column to the site_metadata table, the table will be able to properly populate.
I think you should be fine making this update to the site_metadata table. It will not directly affect any of the data you have stored in project tables in the database. The site_metadata table simply houses the information for all the sites (state, lat, lon, elevation, etc.). So, effectively you’re just updating the site information available to AMET, which should primarily be new sites that have come online, or in this case, additional site information I’ve added to the site metadata table.
Hopefully that’s clear. If not, I’m happy to discuss further.
Wyat
2 Likes
