Ako vytváram grafy vývoja km ciest


v tabuľke fm_WaysStats máme uložené dáta. Stĺpec dátum je dátum ku ktorému je záznam, ostatné sú počty kilometrov.

 CREATE TABLE `fm_WaysStats` (
  `datum` DATE NOT NULL,
  `abandoned` INT(15) DEFAULT NULL,
  `bridleway` INT(15) DEFAULT NULL,
  `bus_guideway` INT(15) DEFAULT NULL,
  `canal` INT(15) DEFAULT NULL,
  `construction` INT(15) DEFAULT NULL,
  `crossing` INT(15) DEFAULT NULL,
  `cycleway` INT(15) DEFAULT NULL,
  `dam` INT(15) DEFAULT NULL,
  `disused` INT(15) DEFAULT NULL,
  `ditch` INT(15) DEFAULT NULL,
  `drain` INT(15) DEFAULT NULL,
  `FIXME` INT(15) DEFAULT NULL,
  `footwalk` INT(15) DEFAULT NULL,
  `footway` INT(15) DEFAULT NULL,
  `ford` INT(15) DEFAULT NULL,
  `fpath` INT(15) DEFAULT NULL,
  `highway` INT(15) DEFAULT NULL,
  `junction` INT(15) DEFAULT NULL,
  `living_street` INT(15) DEFAULT NULL,
  `mini_roundabout` INT(15) DEFAULT NULL,
  `monorail` INT(15) DEFAULT NULL,
  `motorway` INT(15) DEFAULT NULL,
  `motorway_link` INT(15) DEFAULT NULL,
  `narrow_gauge` INT(15) DEFAULT NULL,
  `path` INT(15) DEFAULT NULL,
  `pedestrian` INT(15) DEFAULT NULL,
  `platform` INT(15) DEFAULT NULL,
  `Pod` INT(15) DEFAULT NULL,
  `preserved` INT(15) DEFAULT NULL,
  `primary` INT(15) DEFAULT NULL,
  `primary_link` INT(15) DEFAULT NULL,
  `proposed` INT(15) DEFAULT NULL,
  `raceway` INT(15) DEFAULT NULL,
  `rail` INT(15) DEFAULT NULL,
  `residential` INT(15) DEFAULT NULL,
  `resil` INT(15) DEFAULT NULL,
  `river` INT(15) DEFAULT NULL,
  `riverbank` INT(15) DEFAULT NULL,
  `road` INT(15) DEFAULT NULL,
  `secondary` INT(15) DEFAULT NULL,
  `secondary_link` INT(15) DEFAULT NULL,
  `seri` INT(15) DEFAULT NULL,
  `service` INT(15) DEFAULT NULL,
  `station` INT(15) DEFAULT NULL,
  `steps` INT(15) DEFAULT NULL,
  `stream` INT(15) DEFAULT NULL,
  `subvay` INT(15) DEFAULT NULL,
  `tertiary` INT(15) DEFAULT NULL,
  `TOTAL` INT(15) DEFAULT NULL,
  `tr` INT(15) DEFAULT NULL,
  `track` INT(15) DEFAULT NULL,
  `tram` INT(15) DEFAULT NULL,
  `trunk` INT(15) DEFAULT NULL,
  `trunk_link` INT(15) DEFAULT NULL,
  `unclassified` INT(15) DEFAULT NULL,
  `undefined` INT(15) DEFAULT NULL,
  `unknown` INT(15) DEFAULT NULL,
  `unspecified` INT(15) DEFAULT NULL,
  `unsurfaced` INT(15) DEFAULT NULL,
  `weir` INT(15) DEFAULT NULL,
  PRIMARY KEY  (`datum`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_slovak_ci



Samotné vytváranie grafov je pomocou programu R

library(DBI); library(RMySQL); con<-dbConnect(MySQL(),dbname='skwiki');

dd<-dbGetQuery(con, 'select datum from fm_WaysStats');
dates<-as.Date('2010-12-12')
for(i in 1:length(dd[,1])) dates[i] = as.Date(dd[i,]);

plotonly<-function(data,name='cesty', name2='cesty') {
	png(file=paste("images/stats-cesty-",name2,".png",sep=""), width=600, height=320);
	colnames<-names(data);
	if(max(data) > 4000000) { mult=1000000; jednotka='tisíce';}
	else {mult=1000; jednotka='';}
	par(mar=c(3,4,2,0));
	plot(dates,data[,colnames[1]], type='n', axes=FALSE,ylim=c(0,max(data)), ylab=paste('dĺžka ciest,',jednotka,'km'), main='Freemap.sk štatistiky Slovenska');
	colors<-rainbow(length(colnames));
	for(i in 1:length(colnames)) {
		lines(dates, data[,colnames[i]], col=colors[i]);
	}
	axis.Date(1,x=dates,at=seq(dates[1],max(dates), "months"), format="%b %y")
	ynum<-0;
	yat<-0;
	pocet=5;
	horny=round(max(data)/(pocet*mult))*pocet*mult;
	for(i in 1:pocet) {
		tt=round(horny*i/pocet);
		yat[i+1]<-tt;
		ynum[i+1]<-tt/mult;
	}
	axis(2, at=yat,labels=ynum )
	legend('topleft',colnames,col=colors,title=name,lwd=1);
	dev.off();
}

plottypes<-function(name, types, name2) {
	q<-paste('select `',paste(types, collapse='`,`'), '` from fm_WaysStats', sep='');
	data<-dbGetQuery(con, q);
	plotonly(data,name, name2);
	return(paste('`', paste(types,collapse='`+`'), '` as `',name, '`', sep=''));
}

plotsummary<-function(all) {
	q<-paste('select ',paste(all, collapse=','), ' from fm_WaysStats', sep='');
	data<-dbGetQuery(con,q);
	plotonly(data);
	return();
}
	
all<-c(
plottypes('lesné cesty',c('track','footway','path'),'lesne'),
plottypes('rýchlostné kom',c('motorway','motorway_link','trunk','trunk_link'),'rychlostne'),
plottypes('autá',c('primary','secondary','tertiary'), 'auta'),
plottypes('pešie',c('footway','pedestrian','path','steps'), 'pesie'),
plottypes('mesto',c('residential','service','unclassified','living_street'),'mesto')
);

plotsummary(all);

a<-plottypes('voda',c('river','stream','canal','dam','drain','weir','riverbank'),'voda');
a<-plottypes('železnice',c('tram','rail'),'zeleznice');

CategoryStats
There are no comments on this page.
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki