First, let's open GPSvisualizer and open the "Plot data points" page: http://www.gpsvisualizer.com/map_input?form=data
Find the scroll box titled "Or paste your data here" and delete everything in it then paste the data you have copied from Excel into it. You should get a clean three column content with the headers N Latitude Longitude. Make sure you don't change the headers in any way after you paste them and don't add commas or tabs in between. Just a straight paste from Excel
You can skip this step for now or you can make changes to "Data point options" to follow my settings as shown in the figure.
: Click "Draw the map" button and watch the magic.
HOW TO READ THE MAP
A Google Map will be displayed and overlaid with the route points captured by our geo data logger. In my case, I have selected stars as the icons for the data points. The larger and more blueish the data point or the smaller the more reddish the bigger the road bump or pothole.
By clicking on a star, a balloon will pop up with the z-axis value read by the ADXL335 accelerometer.
Long road stretches of comparable greenish colors and values (typically 520 in my case) mean the road is smoother.
We can change the icon shapes, their minimum and maximum sizes, and other parameters from the "Data point options" section.
TRANSLATING SENSOR DATA INTO ROAD CONDITION INFO
I have simplified this part so almost no math will be needed to assess road conditions using the data generated by the ADXL335 sensor. So there will be no translation from raw accelerometer sensor outputs to g values.
The whole trick wrests in road condition profiling and sensor data comparison.
PROFILING ROAD DATA
Different geo data loggers may produce different readings than mine for various reasons having to do with sensor type if a different accelerometer is used, suspension system differences from one car to another, orientation of the geo data logger, etc. So we need to profile normal road conditions and abnormal road condition before we can make sense of our data using your geo data logger in your particular environment.
Profiling road conditions is simple. We record senor data generated by the ADXL335 sensor while we drive over a good road stretch then do the same with sensor data generated when we drive over a rough road stretch such a bump or pothole.
In my case, I get an average of 520 for the z-axis on a good road stretch. I can use this as a frame of reference so if I get sample data of 520 plus or minus a few notches (you decide what's the acceptable range) then this is a good road. So 520 +/- some value of your choosing is the profile of a good road condition. But if I drive over a bump or pothole, I get sensor z-axis readings that hover around 500 on the low end and 535 on the high end. This will be my profile of a rough road.
The basic assumption here is that I am using the same car, with the sensor placed in the same spot in the car, and driving at the same speed every time I profile the road with my geo data logger.
In the "Data point options" by assigning the "Min" color field my my lower z-axis number and "Max" field my high z-axis number, now I can use GPSvisualizer.com to determine visually, by color or size of marker, where to find poor road stretches, potholes, and bumps.
ANALYZING SENSOR DATA AND ROAD CONDITIONS
When it comes to analyzing the sensor data, sometimes bumps my look like potholes and vice versa. It's possible to log what seems like a pothole condition when in reality we are just dropping back to normal street level right after a road bump. It's also possible to get a sensor reading the resembles that of a bump when the car starts climbing out of the pothole.
We look for small or large z-axis readings, based on the min/max values withing the range of captured data, to identify abnormal road conditions. But classifying these road conditions might require more analysis. We can always play around with GPSvisualizer settings until we get the visual representation we need.
The important thing is to record presence of a road condition in need of attention or to avoid it next time you are on the same road.