R : Copyright 2005, The R Foundation for Statistical Computing
Version 2.2.1  (2005-12-20 r36812)
ISBN 3-900051-07-0

R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.

  Natural language support but running in an English locale

R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.

Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.

[Previously saved workspace restored]

> # plot9.r
> # R file to draw various plots
> 
> # wayne.smith@cgu.edu
> # created: September 30, 2005
> # updated: October 12, 2005
> 
> 
> # we need the ODBC extension -- '> install.packages( "RODBC" )'
> library( "RODBC" )
> 
> # we need the RSvgDevice extension -- '> install.packages( "RSvgDevice" )'
> library( "RSvgDevice" )
> 
> 
> # for each plot, we would like to have several different formats
> drawPlots <- function( data.tsStateCA, data.tsCountiesSoCal, data.tsCountyLA, data.tsCityLA, fileRoot, main, ylab, xlab ) {
+ 
+   # add the current date to the main title
+   main <- paste( main, paste( "source: FCC ULS data -- run date: ", format(Sys.Date(), "%d %b %Y"), sep = ""), sep = "" )
+ 
+   # set the graphics device to file-based output in .png format
+   png( file = paste( fileRoot, ".png", sep = "" ), width = 800, height = 600 )
+ 
+   # draw the graphic
+   #boxplot( data.table )
+   #plot( frequency_assigned, CountOfFrequency )
+   ts.plot( data.tsStateCA, data.tsCountiesSoCal, data.tsCountyLA, data.tsCityLA, gpars = list( main = main, ylab = ylab, xlab = xlab, lty = c( 1:4 ), las = 1, cex.lab = 1.2 ))
+   legend("topleft", c( "StateCA", "CountiesSoCal", "CountyLA", "CityLA" ), lty=c( 1:4 ))
+ 
+ # use this for testing only
+ #  ts.plot( data.tsStateCA )
+ #  legend("topleft", c( "StateCA" ), lty=1 )
+ #  ts.plot( data.tsCountyLA )
+ #  legend("topleft", c( "CountyLA" ), lty=1 )
+ #  ts.plot( data.tsCityLA )
+ #  legend("topleft", c( "CityLA" ), lty=1 )
+ 
+   # disable the current graphics device
+   dev.off()
+ 
+   # set the graphics device to file-based output in .pdf format
+   pdf( file = paste( fileRoot, ".pdf", sep = "" ), title = "R Graphics Output", paper = "letter" )
+ 
+   # draw the graphic
+   #boxplot( data.table )
+   #plot( frequency_assigned, CountOfFrequency )
+   ts.plot( data.tsStateCA, data.tsCountiesSoCal, data.tsCountyLA, data.tsCityLA, gpars = list( main = main, ylab = ylab, xlab = xlab, lty = c( 1:4 ), las = 1, cex.lab = 1.2 ))
+   legend("topleft", c( "StateCA", "CountiesSoCal", "CountyLA", "CityLA" ), lty=c( 1:4 ))
+ 
+   # disable the current graphics device
+   dev.off()
+ 
+   # set the graphics device to file-based output in .svg format
+   devSVG( file = paste( fileRoot, ".svg", sep = "" ))
+ 
+   # draw the graphic
+   #boxplot( data.table )
+   #plot( frequency_assigned, CountOfFrequency )
+   ts.plot( data.tsStateCA, data.tsCountiesSoCal, data.tsCountyLA, data.tsCityLA, gpars = list( main = main, ylab = ylab, xlab = xlab, lty = c( 1:4 ), las = 1, cex.lab = 1.2 ))
+   legend("topleft", c( "StateCA", "CountiesSoCal", "CountyLA", "CityLA" ), lty=c( 1:4 ))
+ 
+   # disable the current graphics device
+   dev.off()
+ }
> 
> 
> # populate a data frame from an ODBC SQL query
> dataQueryStateCA <- function( code, scaled ) {
+ 
+   channel <- odbcConnect( "fcculs" )
+ 
+   queryStringStateCA <- paste( "CREATE TABLE tblTempPlot1 SELECT YearText, count( DISTINCT call_sign ) AS Count FROM tblTimeSeries3 WHERE location_state = 'CA' AND code = '", paste( code, "' GROUP BY YearText", sep = "" ), sep = "" )
+   data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot1" )
+   data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot2" )
+   #data.actionquery <- sqlQuery( channel, "CREATE TABLE tblTempPlot1 SELECT YearText, count( DISTINCT call_sign ) AS Count FROM tblTimeSeries3 WHERE location_state = 'CA' AND code = 'LIASS' GROUP BY YearText" )
+   data.actionquery <- sqlQuery( channel, queryStringStateCA )
+   data.actionquery <- sqlQuery( channel, "CREATE TABLE tblTempPlot2 SELECT Year, Count FROM tblPlotYear LEFT JOIN tblTempPlot1 ON tblPlotYear.Year = tblTempPlot1.YearText" )
+   data.actionaquery <- sqlQuery( channel, "UPDATE tblTempPlot2 SET Count = 0 WHERE Count IS NULL" )
+ 
+   if ( scaled == 'FALSE' )
+     data.StateCA <- sqlQuery( channel, "SELECT Year, Count FROM tblTempPlot2 WHERE Year IN ( '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004' ) ORDER BY Year" )
+   else
+     data.StateCA <- sqlQuery( channel, "SELECT tblTempPlot2.Year AS Year, ( tblTempPlot2.Count / tblPlotPopulationSum.PopulationSumMM ) AS Count FROM tblTempPlot2 JOIN tblPlotPopulationSum ON tblTempPlot2.Year = tblPlotPopulationSum.Year WHERE tblTempPlot2.Year IN ( '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004' ) AND tblPlotPopulationSum.Region = 'StateCA' ORDER BY tblTempPlot2.Year" )    
+ 
+   data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot2" )
+   data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot1" )
+ 
+   odbcClose( channel )
+ 
+   return( data.StateCA )
+ 
+ }
> 
> # populate a data frame from an ODBC SQL query
> dataQueryCountiesSoCal <- function( code, scaled ) {
+ 
+   channel <- odbcConnect( "fcculs" )
+ 
+   queryStringCountiesSoCal <- paste( "CREATE TABLE tblTempPlot1 SELECT YearText, count( DISTINCT call_sign ) AS Count FROM tblTimeSeries3 WHERE location_county IN ( 'IMPERIAL', 'KERN', 'LOS ANGELES', 'ORANGE', 'RIVERSIDE', 'SAN BERNARDINO', 'SAN DIEGO', 'VENTURA' ) AND code = '", paste( code, "' GROUP BY YearText", sep = "" ), sep = "" )
+   data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot1" )
+   data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot2" )
+   #data.actionquery <- sqlQuery( channel, "CREATE TABLE tblTempPlot1 SELECT YearText, count( DISTINCT call_sign ) AS Count FROM tblTimeSeries3 WHERE location_county IN ( 'IMPERIAL', 'KERN', 'LOS ANGELES', 'ORANGE', 'RIVERSIDE', 'SAN BERNARDINO', 'SAN DIEGO', 'VENTURA' ) AND code = 'LIASS' GROUP BY YearText" )
+   data.actionquery <- sqlQuery( channel, queryStringCountiesSoCal )
+   data.actionquery <- sqlQuery( channel, "CREATE TABLE tblTempPlot2 SELECT Year, Count FROM tblPlotYear LEFT JOIN tblTempPlot1 ON tblPlotYear.Year = tblTempPlot1.YearText" )
+   data.actionaquery <- sqlQuery( channel, "UPDATE tblTempPlot2 SET Count = 0 WHERE Count IS NULL" )
+ 
+   if ( scaled == 'FALSE' )
+     data.CountiesSoCal <- sqlQuery( channel, "SELECT Year, Count FROM tblTempPlot2 WHERE Year IN ( '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004' ) ORDER BY Year" )
+   else
+     data.CountiesSoCal <- sqlQuery( channel, "SELECT tblTempPlot2.Year AS Year, ( tblTempPlot2.Count / tblPlotPopulationSum.PopulationSumMM ) AS Count FROM tblTempPlot2 JOIN tblPlotPopulationSum ON tblTempPlot2.Year = tblPlotPopulationSum.Year WHERE tblTempPlot2.Year IN ( '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004' ) AND tblPlotPopulationSum.Region = 'CountiesSoCal' ORDER BY tblTempPlot2.Year" )    
+ 
+   data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot2" )
+   data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot1" )
+ 
+   odbcClose( channel )
+ 
+   return( data.CountiesSoCal )
+ 
+ }
> 
> # populate a data frame from an ODBC SQL query
> dataQueryCountyLA <- function( code, scaled ) {
+ 
+   channel <- odbcConnect( "fcculs" )
+ 
+   queryStringCountyLA <- paste( "CREATE TABLE tblTempPlot1 SELECT YearText, count( DISTINCT call_sign ) AS Count FROM tblTimeSeries3 WHERE location_county = 'LOS ANGELES' AND code = '", paste( code, "' GROUP BY YearText", sep = "" ), sep = "" )
+   data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot1" )
+   data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot2" )
+   #data.actionquery <- sqlQuery( channel, "CREATE TABLE tblTempPlot1 SELECT YearText, count( DISTINCT call_sign ) AS Count FROM tblTimeSeries3 WHERE location_county = 'LOS ANGELES' AND code = 'LIASS' GROUP BY YearText" )
+   data.actionquery <- sqlQuery( channel, queryStringCountyLA )
+   data.actionquery <- sqlQuery( channel, "CREATE TABLE tblTempPlot2 SELECT Year, Count FROM tblPlotYear LEFT JOIN tblTempPlot1 ON tblPlotYear.Year = tblTempPlot1.YearText" )
+   data.actionaquery <- sqlQuery( channel, "UPDATE tblTempPlot2 SET Count = 0 WHERE Count IS NULL" )
+ 
+   if ( scaled == 'FALSE' )
+     data.CountyLA <- sqlQuery( channel, "SELECT Year, Count FROM tblTempPlot2 WHERE Year IN ( '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004' ) ORDER BY Year" )
+   else
+     data.CountyLA <- sqlQuery( channel, "SELECT tblTempPlot2.Year AS Year, ( tblTempPlot2.Count / tblPlotPopulationSum.PopulationSumMM ) AS Count FROM tblTempPlot2 JOIN tblPlotPopulationSum ON tblTempPlot2.Year = tblPlotPopulationSum.Year WHERE tblTempPlot2.Year IN ( '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004' ) AND tblPlotPopulationSum.Region = 'CountyLA' ORDER BY tblTempPlot2.Year" )    
+ 
+   data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot2" )
+   data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot1" )
+ 
+ odbcClose( channel )
+ 
+ return( data.CountyLA )
+ 
+ }
> 
> # populate a data frame from an ODBC SQL query
> dataQueryCityLA <- function( code, scaled ) {
+ 
+   channel <- odbcConnect( "fcculs" )
+ 
+   queryStringCityLA <- paste( "CREATE TABLE tblTempPlot1 SELECT YearText, count( DISTINCT call_sign ) AS Count FROM tblTimeSeries3 WHERE location_city = 'LOS ANGELES' AND code = '", paste( code, "' GROUP BY YearText", sep = "" ), sep = "" )
+   data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot1" )
+   data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot2" )
+   #data.actionquery <- sqlQuery( channel, "CREATE TABLE tblTempPlot1 SELECT YearText, count( DISTINCT call_sign ) AS Count FROM tblTimeSeries3 WHERE location_city = 'LOS ANGELES' AND code = 'LIASS' GROUP BY YearText" )
+   data.actionquery <- sqlQuery( channel, queryStringCityLA )
+   data.actionquery <- sqlQuery( channel, "CREATE TABLE tblTempPlot2 SELECT Year, Count FROM tblPlotYear LEFT JOIN tblTempPlot1 ON tblPlotYear.Year = tblTempPlot1.YearText" )
+   data.actionaquery <- sqlQuery( channel, "UPDATE tblTempPlot2 SET Count = 0 WHERE Count IS NULL" )
+ 
+   if ( scaled == 'FALSE' )
+     data.CityLA <- sqlQuery( channel, "SELECT Year, Count FROM tblTempPlot2 WHERE Year IN ( '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004' ) ORDER BY Year" )
+   else
+     data.CityLA <- sqlQuery( channel, "SELECT tblTempPlot2.Year AS Year, ( tblTempPlot2.Count / tblPlotPopulationSum.PopulationSumMM ) AS Count FROM tblTempPlot2 JOIN tblPlotPopulationSum ON tblTempPlot2.Year = tblPlotPopulationSum.Year WHERE tblTempPlot2.Year IN ( '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004' ) AND tblPlotPopulationSum.Region = 'CityLA' ORDER BY tblTempPlot2.Year" )    
+ 
+   data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot2" )
+   data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot1" )
+ 
+   odbcClose( channel )
+ 
+   return( data.CityLA )
+ 
+ }
> 
> 
> # populate a code description from an ODBC SQL query
> queryCodeDescription <- function( code ) {
+   channel <- odbcConnect( "fcculs" )
+ 
+   queryStringCode <- paste( "SELECT CodeDescription FROM tblPlotHSCode WHERE code = '", paste( code, "'", sep = "" ), sep = "" )
+   data.resultset <- sqlQuery( channel, queryStringCode )
+ 
+   odbcClose( channel )
+ 
+   return( data.resultset )
+ 
+ }
> 
> 
> # design and develop a complete plot
> generatePlot <- function( fileRoot, countField, code ) {
+ 
+   scaled = 'FALSE'
+ 
+   # get the data from an ODBC query and define the time series
+   data.StateCA <- dataQueryStateCA( code, scaled )
+     data.tsStateCA <- ts( data = data.StateCA$Count, start = 1994, frequency = 1 )
+   data.CountiesSoCal <- dataQueryCountiesSoCal( code, scaled )
+     data.tsCountiesSoCal <- ts( data = data.CountiesSoCal$Count, start = 1994, frequency = 1 )
+   data.CountyLA <- dataQueryCountyLA( code, scaled )
+     data.tsCountyLA <- ts( data = data.CountyLA$Count, start = 1994, frequency = 1 )
+   data.CityLA <- dataQueryCityLA( code, scaled )
+     data.tsCityLA <- ts( data = data.CityLA$Count, start = 1994, frequency = 1 )
+ 
+   # get the code description from an ODBC query
+   code.resultset <- queryCodeDescription( code )
+   code.description = code.resultset$CodeDescription
+   code.descriptionString = paste( "[", paste( code.description, "]", sep = "" ), sep = "" )
+ 
+   # draw the plot (in three different file formats)
+   drawPlots( data.tsStateCA, data.tsCountiesSoCal, data.tsCountyLA, data.tsCityLA, fileRoot, paste( "Number of Unique Callsigns by Year\n", paste( code.descriptionString, "\n", sep = "" ), sep = "" ), "Callsigns", "Year" )
+ 
+ }
> 
> 
> # design and develop a complete plot (scaled by regional population)
> generateScaledPlot <- function( fileRoot, countField, code ) {
+ 
+   scaled = 'TRUE'
+ 
+   # get the data from an ODBC query and define the time series
+   data.StateCA <- dataQueryStateCA( code, scaled )
+     data.tsStateCA <- ts( data = data.StateCA$Count, start = 1994, frequency = 1 )
+   data.CountiesSoCal <- dataQueryCountiesSoCal( code, scaled )
+     data.tsCountiesSoCal <- ts( data = data.CountiesSoCal$Count, start = 1994, frequency = 1 )
+   data.CountyLA <- dataQueryCountyLA( code, scaled )
+     data.tsCountyLA <- ts( data = data.CountyLA$Count, start = 1994, frequency = 1 )
+   data.CityLA <- dataQueryCityLA( code, scaled )
+     data.tsCityLA <- ts( data = data.CityLA$Count, start = 1994, frequency = 1 )
+ 
+   # get the code description from an ODBC query
+   code.resultset <- queryCodeDescription( code )
+   code.description = code.resultset$CodeDescription
+   code.descriptionString = paste( "[", paste( code.description, "]", sep = "" ), sep = "" )
+ 
+   # draw the plot (in three different file formats)
+   drawPlots( data.tsStateCA, data.tsCountiesSoCal, data.tsCountyLA, data.tsCityLA, fileRoot, paste( "Number of Unique Callsigns (per 1MM population) by Year\n", paste( code.descriptionString, "\n", sep = "" ), sep = "" ), "Callsigns (per 1MM pop.)", "Year" )
+ 
+ }
> 
> 
> # draw all the Plots
> generatePlot(
+   "liass-call_sign",                              # fileRoot
+   "call_sign",                                    # which field to count
+    "LIASS"                                        # which event code to use
+ )
null device 
          1 
> 
> generatePlot( "liren-call_sign", "call_sign", "LIREN" )
null device 
          1 
> generateScaledPlot( "liass-call_sign-scaled", "call_sign", "LIASS" )
null device 
          1 
> generateScaledPlot( "liren-call_sign-scaled", "call_sign", "LIREN" )
null device 
          1 
> 
> 
> # exit R
> #q()
> 
>