GDELT data into ms-sql

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

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
         )


Excel: replace a character or string with CR LF

This is a technique aimed at splitting up a long cumbersome string held in a single cell using new line breaks. It requires that we replace a normal character with the special characters [CR][LF].

Why would you want to do this?
Well sometimes it looks nice and it makes life easier, or it can make the data easier to scan through. I first used it to show ICD10 Diagnosis code strings from a concatanated string into a single readable cell.

To start off generate your file so that each part of the string that you want on a seperate line has a unique character (or unique string) at the end of the subsection.
In this example I’ve used “: ”
Pic1
Now highlight the section you want to change. Ctrl + A on a section etc.
Then bring up the find and replace dialog using Ctrl + H
In this example I needed to replace “: ” (thats colon followed by a space)
pic2
Now place your cursor in the replace section:
Hold down ALT + 010 release (the ALT key) and then ALT + 013 then release again.
Press the replace all button and away it goes, It can take longer doing this than a normal find and replace.
pic3
…and that’s what you get newlines instead of “: ”

Set Grub2 (Fedora 19) default boot to…something other

grep menuentry /boot/grub2/grub.cfg

Copy the name of the OS you want as default
then access the /etc/default/grub file as root

sudo gedit /etc/default/grub 

Replace whatever is currently being given to GRUB_DEFAULT with the OS name you want as the default (enclose the OS name in “”)

Then run as root:

grub2-mkconfig -o /boot/grub2/grub.cfg

…and reboot to test

I don’t think this survives a Kernel update, but the process is not onerous, and no sacrifices to the gods are required.

GIS in R cran

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)