forAnna_R

install.packages("RPostgreSQL")
require("RPostgreSQL")
library("RPostgreSQL")
#db <-dbDriver("RPostgreSQL")
drv <- RPostgreSQL::PostgreSQL()
con <- dbConnect(drv, dbname = "projectH",host="localhost",
                 port=5432,user="postgres",password="0789")
query <- 'SELECT * FROM company'
table <- dbGetQuery(con,query)

library(RgoogleMaps)

queryMap <- "SELECT com.companyName, com.country, sal.salonName, sal.lat, sal.lon
            FROM company as com
            INNER JOIN salon as sal
            on com.companyID = sal.companyID
            WHERE com.country = 'Germany';"
tableForMap <- dbGetQuery(con,queryMap)

map <- GetMap(center = c(50,10),size = c(640,480),zoom = 5,maptype = "mobile")
PlotOnStaticMap(map)
PlotOnStaticMap(map, lat = tableForMap$lat, lon = tableForMap$lon,
               lwd = 15, cex = 0.1, pch = 4, col = "red")

#попробуем разбить модели по цветам на карте
queryMapAudi <- "SELECT com.companyName, com.country, sal.salonName, sal.lat, sal.lon FROM company as com INNER JOIN salon as sal on com.companyID = sal.companyID WHERE com.country = 'Germany' and com.companyName = 'Audi';"
queryMapBMW <- "SELECT com.companyName, com.country, sal.salonName, sal.lat, sal.lon FROM company as com INNER JOIN salon as sal on com.companyID = sal.companyID WHERE com.country = 'Germany' and com.companyName = 'BMW';"
queryMapMercedesBens <- "SELECT com.companyName, com.country, sal.salonName, sal.lat, sal.lon FROM company as com INNER JOIN salon as sal on com.companyID = sal.companyID WHERE com.country = 'Germany' and com.companyName = 'Mercedes ';"
queryMapPorsche <- "SELECT com.companyName, com.country, sal.salonName, sal.lat, sal.lon FROM company as com INNER JOIN salon as sal on com.companyID = sal.companyID WHERE com.country = 'Germany' and com.companyName = 'Porsche ';"
queryMapVolkswagen <- "SELECT com.companyName, com.country, sal.salonName, sal.lat, sal.lon FROM company as com INNER JOIN salon as sal on com.companyID = sal.companyID WHERE com.country = 'Germany' and com.companyName = 'Volkswagen ';"

tableMapAudi <- dbGetQuery(con,queryMapAudi)
tableMapBMW <- dbGetQuery(con,queryMapBMW)
tableMapMercedesBens <- dbGetQuery(con,queryMapMercedesBens)
tableMapPorsche <- dbGetQuery(con,queryMapPorsche)
tableMapVolkswagen <- dbGetQuery(con,queryMapVolkswagen)

mapGermany <- GetMap(center = c(51,10),size = c(640,480),zoom = 6,maptype = "mobile")
PlotOnStaticMap(mapGermany)
PlotOnStaticMap(mapGermany, lat = tableMapAudi$lat, lon = tableMapAudi$lon,
                lwd = 15, cex = 0.1, pch = 4, col = "blue1", add=TRUE)
PlotOnStaticMap(mapGermany, lat = tableMapBMW$lat, lon = tableMapBMW$lon,
                lwd = 15, cex = 0.1, pch = 4, col = "purple1", add=TRUE)
PlotOnStaticMap(mapGermany, lat = tableMapMercedesBens$lat, lon = tableMapMercedesBens$lon,
                lwd = 15, cex = 0.1, pch = 4, col = "brown1", add=TRUE)
PlotOnStaticMap(mapGermany, lat = tableMapPorsche$lat, lon = tableMapPorsche$lon,
                lwd = 15, cex = 0.1, pch = 4, col = "gold1", add=TRUE)
PlotOnStaticMap(mapGermany, lat = tableMapVolkswagen$lat, lon = tableMapVolkswagen$lon,
                lwd = 15, cex = 0.1, pch = 4, col = "springgreen", add=TRUE)

#Посмотрим на графике соотношение кол-ва салонов на каждуу марку
querySalonCount <- "SELECT com.companyname, count(sal.country) FROM company as com INNER JOIN salon as sal on com.companyID = sal.companyID WHERE com.country = 'Germany' group by com.companyName, sal.country ORDER by count(sal.country) DESC"
tableSalonCount <- dbGetQuery(con,querySalonCount)
cols = c("Black", "Blue", "brown",
         "green", "Red")
library(plotly)
Company <- unlist(tableSalonCount[1])
plot_ly(
  x = ~Company,
  y = unlist(tableSalonCount[2]),
  type = "bar",
  color = cols,
  name = unlist(tableSalonCount[1])
)