MySQL bar chart from the command line

Whilst working a fairly tricky bug involving the powerful GROUP_CONCAT function of MySQL, I also played with the CONCAT function and though a bit grass root, I managed to build a quick bar chart directly in MySQL.

Here is a quick example of what you can do.

First we need a test table:

CREATE TABLE `source_data` (
  `id` int(11) NOT NULL auto_increment,
  `name_of_variable` varchar(10) NOT NULL,
  `var_value` decimal(10,0) NOT NULL,
  `period` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Then some test data:

insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '1', '0', '2009-01-01');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '2', '0', '2009-01-07');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '12', '0', '2009-02-01');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '5', '0', '2009-02-06');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '6', '0', '2009-03-01');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '11', '0', '2009-03-10');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '3', '0', '2009-03-23');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '6', '0', '2009-04-01');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '21', '0', '2009-05-01');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '10', '0', '2009-05-21');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '1', '0', '2009-06-29');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '7', '0', '2009-06-30');

And now we make a basic bar chart, directly in MySQL console:

SELECT 
  date_format(period, '%Y-%m') as monthly_period,
  name_of_variable as 'metric',
  CONCAT(REPEAT('*', var_value)) as 'bar chart'
FROM 
  source_data
GROUP BY 
  name_of_variable , 
  date_format(period, '%Y-%m') 
ORDER BY
  period;

And the result is :

+----------------+--------+-----------------------+
| monthly_period | metric | bar chart             |
+----------------+--------+-----------------------+
| 2009-01        | sales  | *                     |
| 2009-02        | sales  | ************          |
| 2009-03        | sales  | ******                |
| 2009-04        | sales  | ******                |
| 2009-05        | sales  | ********************* |
| 2009-06        | sales  | *                     |
+----------------+--------+-----------------------+
6 rows in set (0.01 sec)

Et voila, you can have some fun with MySQL console... sort of !

KML / KMZ data import into MySQL for use with GIS functions

Free open source data is great but does not always come in the right format.
For instance, geographical data can be created in Google Earth, published on the web as an overlay or set of POIs. Usually the publication format is KML or KMZ.

• If it is KMZ (i.e. binary), it makes the data difficult to use with most (scripting) languages but... Google Earth comes to the rescue, you can make an export in KML (i.e. XML like format) and then we can have some fun.
• KML is basically an XML like syntax that obeys a distinct XSD but does not natively play nice with MySQL GIS / geospatial data types, like points or polygons. However, with some coding love, this can be quite easily sorted thus we can get our data imported from KML to MySQL.

In this post, I will show how to import polygons defined in a KML file into MySQL, using a nice elegant language called Ruby.

For demonstration purposes, the KML polygons will be inserted into a simple table storing a string and a matching polygon definition. Here is the table definition:

CREATE TABLE `tbl_demo_polygons` (
                       `polygon_id` INT(10) NOT NULL AUTO_INCREMENT,
                       `name` TEXT NOT NULL,
                       `polygon` POLYGON NOT NULL,
                       PRIMARY KEY  (`polygon_id`),
                       SPATIAL KEY `poly_spx` (`polygon`)
                     ) ENGINE=MYISAM DEFAULT CHARSET=latin1

The strategy I adopted for this demo is to use a ruby script to generate some SQL INSERT statements which you may then use whichever way suits you.

Without further delay, here is the script:

require "rexml/document"

# we open the file as an XML document
doc = Document.new File.new("datasource.kml")

# we get all placemark elements as an array.
all_pms = doc.elements.to_a("//Placemark")

# we create our SQL file will all the insert statements
f = File.open "dump.sql", "w+"

# we iterate over each Placemark element
all_pms.each do |pm|
 # in my demo, the name of the polygon come from the child element name
 pc_name = pm.elements['name'].text

 # the polygon points come in 1 string by the format is not really MySQL
 raw_polygon = pm.elements['MultiGeometry/Polygon/outerBoundaryIs/LinearRing/coordinates'].text

 # we need to remove the 3rd coord of each point as we don't need a z-axis in our demo
 # we do a bit of formatting to please Mr MySQL
 pc_polygon =  raw_polygon.strip.gsub(',0','#').gsub(',',' ').gsub('#',',').strip[0..-2].split(',').collect!{|p| p.split.reverse!.join(' ')}.join(', ')
 
 # we concat all this nicely in 1 executable INSERT statement
 f << "INSERT INTO tbl_demo_polygons (name, polygon) VALUES ('#{pc_name}',PolygonFromText('Polygon((#{pc_polygon}))'));\n"
end

# we close our stream
f.flush
f.close

# happy days!

This should normally yield a SQL file that move all these polygons into proper MySQL POLYGON geospatial type.

I hope you find this useful and somewhat elegant, thanks to Ruby REXML lib :-)

Disruptive Innovation: raison d'etre of the startup world?

A friend of mine directed me to this video. It is quite short and graphically represents a theory about disruptive innovation.

When you listen to this, it quickly feels like someone is describing the strategy used by most successful startups.

Recent great names like 37signals (with their simpler web based products) or Freshbooks (with their Accounting package) have pretty much all chosen to attack the market with a simpler , lower price, highly scalable product offering.

Right now, they seem to be at a cross-roads section, where they tend to increase the complexity of their product, in an effort to serve more users and generate stronger revenue figures.

What kind of choices will they make, moving forward ? Spin-off of smaller structures or brands? Up their market ?

Let's how far the theory applies...

Google Page Speed does not think Google Adsense javascript is optimized for caching.

Today, by chance I ran Google Page Speed Firefox on one of our sites with Google Adsense.
Strangely enough, Google seems to think Google AdSense for search is not optimized



Obviously caching of Ads would not be really useful but should not Google Page Speed analyzer know better ? Or Should Google AdSense for Search specify a proper expiration tag?

Google Maps and TouchNote mashup: Google Maps get physical!

This is a physical mashup between GoogleMaps and TouchNote.com.
Let me explain. You've certainly had to organize the following mini-events:

"We've moved! Change of Address"

"House warming party at the Johnson's!"

"Let's all meet up in Wimbledon!"

When my friends at TouchNote released their API, I cooked up this little mashup that allows you to use GoogleMaps as a starting point for a customizable printed card. It does not get more unique than this!

This is all free of course [up to the point where you want to receive this special printed card as every start-up has to make a living] and this is a really nifty product.
Your friends and relatives will be impressed!

To make it a no-brainer for you to try it, here is a promocode GOOGLE-MAPS-30 that gives 30% off the card order (excl postage) and is valid until 31st December 2009.

Give it a try now!

Enjoy!

Wicked bug in MySQL "non nullable" column

We pulled our hair off on that bug for a while until we figured what was wrong with MySQL in that instance.
In a InnoDB database, we have a table tbl_demo with a integer column "rank", non nullable and defaulting to 0. We have an after insert trigger in this column. The trigger calculates a derived value and updates the column "rank".

When we upgraded MySQL from version 5.0.51a to version 5.1.30, the UPDATE statement started to fail. After a bit of trouble shouting, we released that this is actually a bug of MySQL which in the earlier version was not enforcing the column definition. This was working whereas it should not have ever worked....
Anyway, the proper way to have this work on both MySQL 5.0.51a and 5.1.30 is to write the SQL Update statement as follows:

update tbl_demo set rank=IFNULL(new_value_for_rank,0) where id=123;

The use of the IFNULL makes the code work with the bug fix in MySQL.
A simple workaround for quite an annoying behavior....