In our Product Showcase section of this issue, we introduced Fusion Charts which is a flash-based charting product that makes beautiful flash charts. It comes in both a free and a non-free more bells and whistles version.
In this 3-part series article we shall demonstrate using this with a PostgreSQL database, building a simple dashboard with ASP.NET and PHP. We shall demonstrate both C# and VB.NET both using the PostgreSQL NPGSQL driver.
For this first part we shall simply load the database, do a quick analysis of what we've got to report on and create some views to help us with our PHP and ASP.NET apps that will follow in parts 2 and 3.
We will be testing this on 8.3, but since the database is an old one, it should work just fine on older versions of PostgreSQL. We'll try to refrain from using new features of PostgreSQL.
For the database, we shall be using the usda (USDA The US Department of Agriculture's public domain food and nutrient database) which you can download from http://pgfoundry.org/projects/dbsamples/.
Now for the load
psql -h localhost -p 5432 -U postgres -c "CREATE DATABASE usda WITH ENCODING='UTF8';" psql -h localhost -p 5432 -f /path/to/usda.sql -U postgres -d usda psql -h localhost -p 5432 -U postgres -c "CREATE ROLE usda_app LOGIN PASSWORD 'foodie' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
Now this database turns out to be somewhat rich for a demo db. It helps to look at the relationships in a relational designer to get a breath of what metrics we can glean from here. So lets itemize what we have here.
We covered installing Open Office's PostgreSQL SDBC driver in Using OpenOffice Base 2.3.1 with PostgreSQL
Installing the driver is the same in OpenOffice 3.0
Below is a snapshot of what our loaded database relationships look like in OpenOffice 3.0
Now lets see what we have here and what interesting questions we can answer and graph from this data. Since we have no instructions to go by aside from the relationships and the data we see, we'll make some educated guesses.Tables
Looking at the data, some interesting questions come to mind. We may expand on this as we get into the app. Anyone with other thoughts are free to suggest.
For this section note we are living out with metrics like 18:0, 14:0. I have no clue what those things are.
Create a view to provide basic food stats by metric
CREATE OR REPLACE VIEW vwfdgrpstats AS SELECT g.fddrp_desc AS item_name, g.fddrp_desc AS food_group, nutr_def.nutrdesc AS metric, round(avg(nut_data.nutr_val)::numeric, 3) AS avgnutritionalvalue, nutr_def.units FROM food_des f JOIN fd_group g ON f.fdgrp_cd = g.fdgrp_cd JOIN nut_data ON nut_data.ndb_no = f.ndb_no JOIN nutr_def ON nutr_def.nutr_no = nut_data.nutr_no WHERE nutr_def.nutrdesc NOT LIKE '%:%' GROUP BY g.fddrp_desc, nutr_def.nutrdesc, nutr_def.units; GRANT SELECT ON TABLE vwfdgrpstats TO usda_app;
Create view to get average stats about food groups by unit and metric
CREATE OR REPLACE VIEW vwfoodstats AS SELECT initcap(f.shrt_desc) AS item_name, f.shrt_desc AS food_name, g.fddrp_desc AS food_group, nutr_def.nutrdesc AS metric, round(avg(nut_data.nutr_val::numeric), 3) AS avgnutritionalvalue, nutr_def.units FROM food_des f JOIN nut_data ON nut_data.ndb_no = f.ndb_no JOIN nutr_def ON nutr_def.nutr_no = nut_data.nutr_no JOIN fd_group g ON f.fdgrp_cd = g.fdgrp_cd WHERE nutr_def.nutrdesc NOT LIKE '%:%' GROUP BY f.shrt_desc, g.fddrp_desc, nutr_def.nutrdesc, nutr_def.units;