Visualizing results from SQL queries

2012-11-23

Most statistical or dedicated software can process data stored in SQL databases and plot results from specific queries. There are also custom applications that allow to display query results, like DbVisualizer.

I recently installed a full-featured binary package of PostgreSQL, Postgres.app, which I find particularly convenient to work with database on my Mac since it generally comes with an up to date Postgres system, as well as PostGis and PLV8 (which I haven't explored yet). From the same author, there is also a nice GUI for querying and visualizing results as basic charts (actually, points or lines scatterplot, and donuts, i.e. much less features compared to DbVisualizer): Induction. The list of upcoming features looks really impressive although I doubt all tasks will be completed tomorrow. Anyway, that's an interesting project because there are several connectors (PostgreSQL, Redis, sqlite and MongoDB) and it is just free.

To connect to a running Postgres, just enter the following information

postgres://chl@localhost:5432

that is: your login name, @, host and port where the server is running.

A toy example

I will used the auto dataset which I saved as a plain text file on my hard drive. Here is a brief sketch of the SQL statements: (This assumes there is a database named datasets.)

chl=# \c datasets
datasets=# CREATE TABLE auto (MAKE char(20), PRICE real, MPG real, REP78 real, HEADROOM real, 
                              TRUNK real, WEIGHT real, LENGTH real, TURN real, DISPLACEMENT real, 
                              GEAR_RATIO real, FOREIGNER real);
datasets=# COPY auto FROM '/Users/chl/Documents/data/auto.txt' DELIMITERS ',' CSV HEADER;
datasets=# SELECT make FROM auto WHERE rep78 = -999;
datasets=# UPDATE auto SET rep78 = NULL WHERE rep78 = -999;
datasets=# SELECT * FROM auto LIMIT 5;

Here is the table viewed from Induction:

induction1

And here is a little query (with syntax highlighting and auto-complete):

induction2

Finally, a simple scatterplot (nothing fancy, of course):

induction3

The plotting framework is held on a separate Github repository: LiesDamnedLies and I noticed it included a D3 backend, but for the moment data visualization remains static. I hope this open source project will go beyond the alpha stage because it might well be a handy and free alternative to existing software (e.g. DbVisualizer at $179/year).

---

Articles with the same tag(s):

Data cleaning techniques
El Capitan
Why I am still using Emacs
Tmux and OS X
OS X Yosemite
Some useful Mac Apps for data scientists
Collecting email usage statistics from mu
From Beamer to Deckset
Fixing some critical keyboard shortcuts in OS X terminal
A modular configuration for Emacs

---