R draw a map

library(RODBC)
#ODBC
dbhandle <- odbcDriverConnect('driver={SQL Server};server=xxx;database=xxx;trusted_connection=true')
data_hrchy <- sqlQuery(dbhandle, 'select * from dbo.BRKR_HRCHY')
data_company<-unique(data_hrchy[,"Company Name"])
summary(data_hrchy)

#get lon lat from google
place<-as.character(data_company)
positions<-geocode(place)
positions[,'new_col']<-place
positions

write.csv(positions,"C:\\NotBackedUp\\geo.csv")

#get weight of points
pos_weights <- sqlQuery(dbhandle, 'select round(app_vol_percent,4) as app_vol_percent,round(app_loan_amount_percent,4) as app_loan_amount_percent,lon,lat, Company_Name from broker_app_geo_final')
summary(pos_weights)
#as.double(as.character(pos_weights$app_vol_percent))

 

library(ggmap)

#draw map
#locationt <- geocode("Australia")

#https://rpubs.com/Japhilko82/ggmap

#mapImageData1 <- get_map(location = locationt,
# color = "color",
# source = "google",
# maptype = "satellite",
# zoom = 4)

#output_graph<-ggmap(mapImageData1,
# extent = "device",
# ylab = "Latitude",
# xlab = "Longitude")

FinalMap<-qmap("Australia",zoom=4)

for (i in 1:nrow(pos_weights)) {
point <- pos_weights[i,3:4]
weight <- as.double(as.character(pos_weights[i,1]))
place_label <- as.character(pos_weights[i,5])
FinalMap<-FinalMap + geom_point(data=point,
aes(x=lon, y=lat),
color=rgb(1,0,0),
size=40*weight+4,
alpha=0.5)
# + geom_text(data = point,
# aes(x = lon,
# y = lat,
# label = place_label),
# color = 'blue')
# +geom_text(data=point,
# aes(x=as.numeric(lon),y=as.numeric(lat), label=place_label))
}

 

 

library(ggrepel)

p+geom_text_repel(data=filter(results, padj<0.05), aes(label=Gene))

FinalMap+geom_text(data=pos_weights[,3:4],
aes(x=as.numeric(lon),y=as.numeric(lat), label=pos_weights$Company_Name))

FinalMap

geom_text(data = mydf,
aes(x = lon,
y = lat,
label = places),
color = 'blue')

 

 

 

 

#################

 

app_vol_percent app_loan_amount_percent lon lat
0.0025 0.0021 138.6137396 -34.805772
0.0821 0.0697 NA NA
0.0206 0.0174 152.9973566 -27.4660363
0.1817 0.1833 151.2098245 -33.8378532
0.0021 0.0022 151.2074944 -33.838363
0.0007 0.0004 115.8586072 -32.1075809
0.0177 0.0208 NA NA
0.0003 0.0003 144.9569975 -37.8040118
0.0013 0.0004 115.8435561 -31.949741
0.1574 0.1706 144.9556306 -37.8190734
0.0035 0.0042 151.30004 -33.731164
0.0001 0.0002 144.9704733 -37.833224
0.0206 0.022 145.0981889 -37.8262956
0.0524 0.0526 NA NA
0.0722 0.0931 -122.3978806 37.7928294
0.0036 0.0041 151.2089387 -33.8616952
0.0234 0.025 151.210985 -33.867357
0.0007 0.0005 144.960126 -37.810759
0.0001 0.0001 NA NA
0.0003 0.0002 130.839236 -12.431419
0.0754 0.064 -80.4817449 43.4767023
0.0015 0.001 144.997674 -37.843569
0.0108 0.0103 153.229054 -27.495124
0.021 0.022 144.9606843 -37.8140244
0.0003 0.0001 28.0581245 -26.2011713
0.0076 0.0117 151.1615423 -33.8560381
0.0741 0.0672 NA NA
0.0709 0.0665 NULL NULL
0.0044 0.0027 144.96902 -37.803833
0.0041 0.0035 115.807299 -31.908728
0.0338 0.0293 151.118859 -33.783487
0.0005 0.0003 NA NA
0.0001 0.0001 -73.4173917 40.7767286
0.0521 0.0522 151.768353 -32.9359814

 

 

############################