====== Ajustes Dados ====== --- //[[adalardo@usp.br|Alexandre Adalardo]] 2012/12/23 08:22// Consulta e ajuste dos dados para criação dos arquivos de [[dados_uso|]]. A consulta foi feita usando SQL e os ajustes feitos em R. Para usar ambos juntos deve instalar o pacote RMySQL que conecta a sessão do R a um banco de dados remoto ou local. No caso usei o acesso remoto ao servidor labtrop (143.107.246.248) logado como usuário peic_user e senha peicplotctfs255. A consulta SQL é similar a documentada em [[bancodados_exporta # Consulta SQL|]] Não testei no ruWindows, mas imagino que se o pacote RMySQL for instalado sem problema, o código abaixo deve funcionar tb. ############ ## RMySQL ## ################################################################### ############ Baixando dados do Banco de Dados Direto para o R ##### #### e Ajustando os Dados para uso ############################### ## Alexandre 22 dezembro de 2012 ##### ## um dia apos o mundo acabar ## ################################ ## INSTALAR PACOTE RMySQL no R # ## confira se o pacote do UBUNTU ## .dev abaixo esta instalado ## em ruWindows não testei #sudo apt-get install libmysqlclient-dev #install.packages("RMySQL") library(RMySQL) ######################################### ### conexao remota com o Servidor Labtrop ######################################### con1 <- dbConnect(MySQL(), user="peic_user", password="peicplotctfs255", dbname="peic", host="143.107.246.248") dbListTables(con1) #dbDisconnect(con1) ######################################### ## Conexao local no Sony VAIO ALE ######################################### user<-readline("Digite o usuário do banco de dados: ") pw<-readline(cat("Digite a senha do usuário", user, ": ")) con2 <- dbConnect(MySQL(), user=user, password=pw, dbname="peic") dbListTables(con2) ############################# ### criando objeto censo1 ### sqlscript04<- "SELECT PlantaTag AS tag, Gazetteer.Gazetteer AS quad, X as dx, Y as dy , StartX + X AS gx ,StartY + Y AS gy, Familia AS fam, Genero AS gen, Especie AS epitsp, CONCAT(Genero,\' \', Especie) AS species, TraitVariation AS dbh, DataObs AS data04 FROM Plantas LEFT JOIN Gazetteer ON Plantas.GazetteerID=Gazetteer.GazetteerID LEFT JOIN Identidade ON Plantas.DetID=Identidade.DetID LEFT JOIN Tax_Familias ON Identidade.FamiliaID=Tax_Familias.FamiliaID LEFT JOIN Tax_Generos ON Identidade.GeneroID=Tax_Generos.GeneroID LEFT JOIN Tax_Especies ON Identidade.EspecieID=Tax_Especies.EspecieID LEFT JOIN Monitoramento ON Plantas.PlantaID=Monitoramento.PlantaID LEFT JOIN Traits ON Monitoramento.TraitID=Traits.TraitID WHERE TraitName=\'DAP\' AND DataObs < Date(\'2009-01-01\') ORDER BY StartX, StartY, X, Y ASC;" res1 <- dbSendQuery(con1, sqlscript04) censo1 <- fetch(res1, n = -1) str(censo1) ############################## #### criando objeto censo2 ### ############################## sqlscript09<- "SELECT PlantaTag AS tag, Gazetteer.Gazetteer AS quad, X as dx, Y as dy , StartX + X AS gx ,StartY + Y AS gy, Familia AS fam, Genero AS gen, Especie AS epitsp, CONCAT(Genero,\' \', Especie) AS species, TraitVariation AS dbh, DataObs AS data09 FROM Plantas LEFT JOIN Gazetteer ON Plantas.GazetteerID=Gazetteer.GazetteerID LEFT JOIN Identidade ON Plantas.DetID=Identidade.DetID LEFT JOIN Tax_Familias ON Identidade.FamiliaID=Tax_Familias.FamiliaID LEFT JOIN Tax_Generos ON Identidade.GeneroID=Tax_Generos.GeneroID LEFT JOIN Tax_Especies ON Identidade.EspecieID=Tax_Especies.EspecieID LEFT JOIN Monitoramento ON Plantas.PlantaID=Monitoramento.PlantaID LEFT JOIN Traits ON Monitoramento.TraitID=Traits.TraitID WHERE TraitName=\'DAP\' AND DataObs > Date(\'2007-01-01\') ORDER BY StartX, StartY, X, Y ASC;" res2 <- dbSendQuery(con1, sqlscript09) censo2 <- fetch(res2, n = -1) str(censo2) dbDisconnect(con1) #dbDisconnect(con2) ############################### ## separando fustes multiplos # ############################### ################################# # SEPARAR FUSTES MULTIPLOS ## # em linhas diferentes 2004 ## ################################# str(censo1) f2pos<-grep(";", censo1$dbh) f2tag<-censo1$tag[f2pos] censo1$dbh[censo1$tag%in% f2tag] censo1$fuste<-1 peic04<-censo1[-f2pos,] str(peic04) peic04$fuste<-1 for(i in f2tag) { dbh=censo1[censo1$tag==i, "dbh"] dado.i<-censo1[censo1$tag==i, ] dado.i$dbh<-NA dbh.split<-unlist(strsplit(dbh,"; ")) for(j in 1: length(dbh.split)) { dado.i$dbh<-dbh.split[j] dado.i$fuste<-j peic04<-rbind(peic04, dado.i) } } str(peic04) ################################# # SEPARAR FUSTES MULTIPLOS ## # em linhas diferentes 2009 ## ################################# f2pos2<-grep(";", censo2$dbh) f2tag2<-censo2$tag[f2pos2] censo2$dbh[censo2$tag%in% f2tag2] censo2$fuste<-1 peic09<-censo2[-f2pos2,] str(peic09) peic09$fuste<-1 for(i in f2tag2) { dbh=censo2[censo2$tag==i, "dbh"] dado.i<-censo2[censo2$tag==i, ] dado.i$dbh<-NA dbh.split<-unlist(strsplit(dbh,"; ")) for(j in 1: length(dbh.split)) { dado.i$dbh<-dbh.split[j] dado.i$fuste<-j peic09<-rbind(peic09, dado.i) } } str(peic09) peic09[,3:6]<-round(peic09[,3:6],1) peic09$dbh<-as.numeric(peic09$dbh) peic04[,3:6]<-round(peic04[,3:6],1) peic04$dbh<-as.numeric(peic04$dbh) ############## ## sp code ############## source("/home/ale/Documentos/Ale2010/AleRfunctions/Ale_PP/spcode2010.r") sp<-lista.spcode(peic09$species) peic09$sp<-sp sp1<-lista.spcode(peic04$species) peic04$sp<-sp1 write.table(peic04, file="/home/ale/Documentos/Ale2010/Ale_Projetos/Restinga/ParcelaPermanente_Restinga/DadosEmUso/peic04.txt", sep="\t", row.names=FALSE) write.table(peic09, file="/home/ale/Documentos/Ale2010/Ale_Projetos/Restinga/ParcelaPermanente_Restinga/DadosEmUso/peic09.txt", sep="\t", row.names=FALSE) table(is.na(peic09$dbh)) ################################## ## arquivo unico com 1 fuste e ## ## dois censos e Area Basal ## ################################## ab04<-peic04$dbh^2*pi/4 ab04f1<-tapply(ab04, peic04$tag, sum) tagf1=names(ab04f1) pos.placa=match(tagf1,peic04$tag) dados04=peic04[pos.placa,] dados04$basalArea=as.numeric(ab04f1) dados04$dbh=round(sqrt((4/pi)*dados04$basalArea)) str(dados04) ab09<-peic09$dbh^2*pi/4 ab09f1<-tapply(ab09, peic09$tag, sum) tagf1=names(ab09f1) pos.placa=match(tagf1,peic09$tag) dados09=peic09[pos.placa,] dados09$basalArea=as.numeric(ab09f1) dados09$dbh=round(sqrt((4/pi)*dados09$basalArea)) str(dados09) #################### ## juntando dados ## #################### length(unique(dados09$tag)) dim(dados09) tag<-unique(c(dados09$tag,dados04$tag)) quad<-rep(NA, length(tag)) peic.uso<-data.frame(tag,quad, stringsAsFactors =FALSE) str(peic.uso) m09<-match(dados09$tag, peic.uso$tag) peic.uso$quad[m09]<-dados09$quad peic.uso[,names(dados09)[-c(1,2)]]<-NA peic.uso[m09, -c(1,2)]<-dados09[,-c(1,2)] names(peic.uso)[11]<-"dbh09" peic.uso=peic.uso[,-15] peic.uso$status09<-"D" peic.uso$status09[m09]<-"A" table(peic.uso$status09) table(is.na(peic.uso$quad)) tag.dead<-peic.uso$tag[is.na(peic.uso$quad)] m.dead<-match(tag.dead,peic.uso$tag) m04<-match(tag.dead,dados04$tag) table(peic.uso$tag[m.dead]==dados04$tag[m04]) peic.uso$quad[m.dead]<-dados04$quad[m04] col.fix<-names(peic.uso)[c(3:10, 13,14)] peic.uso[m.dead, col.fix]<-dados04[m04, col.fix] str(dados04) m04tag<-match(dados04$tag, peic.uso$tag) str(peic.uso) peic.uso[,c("dbh04", "data04", "status04")]<-NA peic.uso[m04tag,c("dbh04", "data04")]<-dados04[,c("dbh", "data04")] peic.uso$status04[m04tag]<-"A" peic.uso$status04[- m04tag]<-"R" table(peic.uso$status04) str(peic.uso) write.table(peic.uso, file="/home/ale/Documentos/Ale2010/Ale_Projetos/Restinga/ParcelaPermanente_Restinga/DadosEmUso/peicDez2012.txt", sep="\t", row.names=FALSE) ################################################################## ##### checando com os dados exportados do banco pela interface web ################################################################## peic.web<-read.table("/home/ale/Documentos/Ale2010/Ale_Projetos/Restinga/ParcelaPermanente_Restinga/DadosEmUso/peicweb21Dez2012.txt", sep="\t", as.is=TRUE, header=TRUE) str(peic.web) dim(peic.web)[1] - dim(peic.uso)[1] # 467 nao aparecem no peic.uso length(unique(peic.web$tag)) mtag<-match(peic.web$tag, peic.uso$tag) peic.web[which(is.na(mtag)),] ## as que nao aparecem no peic.uso e' porque nao tem dados de dap em nenhum censo e portanto não há data para a coleta de DAP. O status ou é vazio ou é "D", deveria ser outro! peic.uso[1:10,] peic.web[peic.web$tag=="100",] ####################################################### ## parece que a saida web tem problema: veja tag== 100 #######################################################