Category Archives: Database

ggplot2/qplot basics

Posted on by

Install and load the ggplot2 and Cairo libraries

install.packages(c("ggplot2","Cairo")
library(c(ggplot2,Cairo))

set up some data (or use some real data)

x1<-rnorm(150,mean = rep(1:3, each =50),sd = 0.7)
x2<-rnorm(150,mean = rep(c(1,2,1.5), each = 50),sd = 0.2)
x3<-rnorm(150,mean = rep(c(20,30,3),each = 50)), sd = 0.5)
n3<-rep(c("GRP 01","GRP 02","GRP 03"),each=50)

Here is the command to generate the PNG file, with anti-aliasing:

CairoPNG(filename = "Plot1.png", antialias="subpixel", width = 1000, height=800, units = "px")
{
  qplot(x1,x2, ,color = n3, size = x3)
}
dev.off()

Plot1

or you can split the 3 sections up using:

 qplot(x1,x2, color = n3, facets = .~n3)

Plot2

...and now something similar using GGPLOT2

First thing we need to do is create a dataframe from the four identical length vectors.

df <- data.frame(x1,x2,x3,n3)
colnames(df) <- c("x1","x2","x3","n3")

Some Charting:

g1 <- ggplot(df,aes(x1,x2))
p <- g1 + geom_point(aes(colour=n3), size =3.5) + 
          geom_smooth(method = "lm") +
          theme_bw() 
print(p)

..and a slightly better looking version:

g1 <- ggplot(df,aes(x1,x2))
p  <- g1 + geom_point(aes(colour=n3, size =x3)) + 
           geom_smooth(method = "lm") +
           theme_bw() 
print(p)

Plot3

There you go all good stuff.
Other things to check out: facet_wrap
Some more pretty graphics

GDELT data into ms-sql

Posted on by

Go…here
download the historical backfiles

Use the following script to create a database in the required location:

CREATE TABLE GDELT_HISTORICAL (
 GLOBALEVENTID bigint , --1
 SQLDATE int, 
 MonthYear char(6) , 
 [Year] char(4) , 
 FractionDate decimal , --5
 Actor1Code char(55) , 
 Actor1Name char(255) , 
 Actor1CountryCode char(55) , 
 Actor1KnownGroupCode char(55) , 
 Actor1EthnicCode char(55) , --10
 Actor1Religion1Code char(55) , 
 Actor1Religion2Code char(55) , 
 Actor1Type1Code char(55) , 
 Actor1Type2Code char(55) , 
 Actor1Type3Code char(55) , 
 Actor2Code char(55) , --16
 Actor2Name char(255) , 
 Actor2CountryCode char(55) , 
 Actor2KnownGroupCode char(55) , 
 Actor2EthnicCode char(55) , 
 Actor2Religion1Code char(55) , 
 Actor2Religion2Code char(55) , 
 Actor2Type1Code char(55) , 
 Actor2Type2Code char(55) , 
 Actor2Type3Code char(55) , 
 IsRootEvent int , 
 EventCode char(4) , 
 EventBaseCode char(4) , 
 EventRootCode char(4) , 
 QuadClass int , 
 GoldsteinScale decimal , 
 NumMentions int , 
 NumSources int , 
 NumArticles int , 
 AvgTone decimal , 
 Actor1Geo_Type int  , 
 Actor1Geo_FullName char(255) , 
 Actor1Geo_CountryCode char(2) , 
 Actor1Geo_ADM1Code char(4) , 
 Actor1Geo_Lat float , 
 Actor1Geo_Long float , 
 Actor1Geo_FeatureID int  , 
 Actor2Geo_Type int  , 
 Actor2Geo_FullName char(255) , 
 Actor2Geo_CountryCode char(2) , 
 Actor2Geo_ADM1Code char(4) , 
 Actor2Geo_Lat float , 
 Actor2Geo_Long float , 
 Actor2Geo_FeatureID int  , 
 ActionGeo_Type int  , 
 ActionGeo_FullName char(255) , 
 ActionGeo_CountryCode char(2) , 
 ActionGeo_ADM1Code char(4) , 
 ActionGeo_Lat float , 
 ActionGeo_Long float , 
 ActionGeo_FeatureID float  , 
 DATEADDED int
);

Unzip all your history files into one location and then run this script for each file:

  BULK INSERT GDELT_HISTORICAL
    FROM 'C:\Users\MONKEYMIKE\Desktop\201302.csv'
    WITH
        (
		FIELDTERMINATOR = '\t'
		, ROWTERMINATOR = '0x0a'--'\n'
		 )

treemap in R

Posted on by 
library(RODBC)
library(lattice)
library(treemap)

ch<-odbcConnect("mike_db",uid="mike")
c<-sqlQuery(ch, paste("select" 
,"ward,year(end_Dttm) as [year]"
,",sum(datediff(mi,start_Dttm,end_Dttm)/1440.0) as LOS"
,"from [wardstays_examples]"
,"GROUP BY ward ,year(end_Dttm)" 
))
str(c)

treemap (c
         ,index=c("year","ward") # the different levels
         ,vSize = "LOS" # the value on which to scale the squares
         )

GIS in R cran

Posted on by 
library(maptools)
library(Cairo)
walesCoast<-readShapeSpatial("Z:/MAPPING DATA/Meridian 2 Shape/data/coast_ln_polyline.shp", proj4string=CRS("+init=epsg:27700"))
walesUA<-readShapeSpatial("Z:/MAPPING DATA/Meridian 2 Shape/data/district_region.shp", proj4string=CRS("+init=epsg:27700"))
x1x2<-c(221000,346594)
y1y2<-c(269406,395000)

plot(walesUA,xaxs="i",yaxs="i",xlim=x1x2,ylim=y1y2,lwd=1)
plot(walesCoast,xaxs="i",yaxs="i",xlim=x1x2,ylim=y1y2,lwd=3,col="red", add=TRUE)

mtext("upvar",side=2,line=2,col=1)
mtext("Bottom",side=1,line=2,col=2)
mtext("Top",side=3,line=2,col=3)
mtext("Right",side=4,line=1,col=4)