— 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 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
#######################################################