Php generating speed graph with many points

[newbie14]

Dear All,
I have a number of data from mysql database to be populated into a graph format. The graph is speed vs time. The time will be in every minute. So far I have tried two tools one phPlot and PhpGraphLib. The first one will only plot say at most 10 points and second one give me an error of too many points. Any help on how to draw a line graph with many x-axis points? Thank you.

[diafol]

Read that question again and see if it makes sense. My crystal ball is ever so cloudy.

[newbie14]

Dear Ardav,
Ok to make it short I have many values on my x-axis so any solution for that hope I kept it short and simple? Thank you.

[diafol]

I wasn't looking for short and simple, I was looking for more info so that I could suggest something...
How many points are you looking at?
Have you got a script other than the 2 mentioned in mind?
What's the DB structure? Is it relevant?
You mention speed v time and that time is in minutes - should this be a factor in deciding on a script or a roll-your-own?

[newbie14]

Dear Ardav,
Ok let me give you more details. It is actually a gps data and the amount of points varies depends on the journey. If a short journey say between 2 to 3 hours might have few hundreds points. On the other hand if long journey might even have thousand points. The db structure is as below. The script I run is based on the associateID which equivalent to a journey. Thank you.

CREATE TABLE IF NOT EXISTS `tblData` (
`dataID` int(11) NOT NULL auto_increment,
`header` varchar(3) NOT NULL,
`deviceType` varchar(3) NOT NULL,
`eventID` varchar(2) NOT NULL,
`masterSerialNumber` varchar(20) NOT NULL,
`simStatus` smallint(6) NOT NULL,
`csq` smallint(6) NOT NULL,
`mcc` smallint(6) NOT NULL,
`mccc` smallint(6) NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`speed` float NOT NULL,
`course` int(3) NOT NULL,
`dateTimer` datetime NOT NULL,
`gpsDateTime` datetime NOT NULL,
`gpsStat` tinyint(4) NOT NULL,
`unitStat` varchar(12) NOT NULL,
`serialKey` varchar(8) NOT NULL,
`digitalInOut` varchar(4) NOT NULL,
`fuelLevel` varchar(3) NOT NULL,
`battVolt` float NOT NULL,
`deviceVolt` float NOT NULL,
`odoMeter` float NOT NULL default '0',
`chkSum` varchar(8) NOT NULL,
`driverID` int(5) NOT NULL,
`groupID` varchar(8) NOT NULL,
`associateID` int(5) NOT NULL default '0',
`geoFenceInID` int(5) NOT NULL default '0',
`geoFenceOutID` int(11) NOT NULL default '0',
`geoFenceAlertIDIn` int(11) NOT NULL default '0',
`geoFenceAlertIDOut` int(11) NOT NULL default '0',
`eventAlertID` int(11) NOT NULL default '0',
`scvDataInsertDateTime` datetime NOT NULL,
`gpsString` varchar(450) NOT NULL,
PRIMARY KEY (`dataID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=26238 ;

[cwarn23]

Isn't a speed graph just distance over time which makes x=y making a perfectly diagonal line?

[newbie14]

Dear Cwarn,
I just take this two fields from my table speed and dateTimer. The speed is X-axis and dateTimer is Y-axis?

[diafol]

OK speed (y axis) and dateTimer (time on x-axis)
If you find 1000 values you could cut them down by choosing to sample 1 data pt in every 10, to give you 100 pts. Or something similar. If you're producing, say a 600 x 300 pixel graph, there's no way you need a 1000 points.
Is this line of thinking acceptable? If so, how many datapoints do you need as a max?

[cwarn23]

I think you would want dateTimer on the X-axis and speed on the Y-axis. Would make more sense that way. But the way you have dateTimer in the mysql database as a date type rather than an int will make it harder to plot onto the graph. I might let ardav sort that one out. But I will help out with the theoretical stuff.

[newbie14]

Dear Ardav and Cwarn,
Good input will look into the sampling. About the date time being not int I think is difficult cause else where I will use as date time type. So what are your best suggestion based on experiences.

[diafol]

The easiest way to deal with time (IMO) is with int (unix timestamp), based on unix epoch. The arithmetic req'd for datetime conversions, differences, additions, etc is then very much simplified.

You can of course use datetimes, and use either MySQL or php functions to convert the format to something usable and there are always the date functions in both too.

Again the beauty of using timestamp is that you can get your starting point, e.g. 1284076800. If you substract this from every timestamp, you have a natural increment (seconds). You could divide by 60 to convert to mins, etc etc. This should suit your graph abscissa (usu. numeric for line graphs) as opposed to plotting date|time, which can be cumbersome and may clutter up your abscissa tick labels.

[diafol]

HWat about google charts? OK it's js, but v. versatile:

http://code.google.com/apis/ajax/playground/?type=visualization#line_chart

[cwarn23]

HWat about google charts? OK it's js, but v. versatile:

http://code.google.com/apis/ajax/playground/?type=visualization#line_chart

I think google also has an api that isn't javascript but you can link to a url where you dump all of your variables in the url and google will generate the chart for you. If however you do not wish to use the google api then there is always the gd library and I'm sure there is a few good tutorials out there. Hint google "making line graph in gd with php"