ed data docs

Converting to SQLite

Importing the spatially-explicit tables (TMsch.csv and TMlea.csv, in the form of simple lat/long coordinates) into QGIS via the'Add Delimited Text Layer' plugin, allows for a first look at the data. Selecting 'Save layer as vector file..' from the Layers menu. In the dialog box, select the following parameters:

Format: SQLite
Encoding: UTF-8
CRS: Google Mercator EPSG:900913

Import the non-spatial .csv tables into SQLite database files using a database inspector like Base or from the command line using SQL for SQLite. Be sure to import all six tables into one database.

Further information on converting CSV files into sqlite databases can be found at http://mapbox.com/tilemill/docs/tutorials/sqlite-work/

Cleaning the Data

Non-spatial tables

Once the tables have been imported, you will need to process and clean the datable to make it both useable and consumable. First you will need to execute a query to join each year-specific table into one table.

Schools query
District query

Using combination of sub-select and left joins accomplishes this without losing records (individual schools in this case) that don't exist in all years. Note that we are selecting only 4th, 8th and 12th grades. Additionally, we are changing raw percentages into more legible numbers, and casting them as strings in order to concatenate a percentage sign. Lastly an alias is used to distinguish that this is the processed column.

A side-effect of the above query is that all NULL values are shown as '0.0%'. To correct this run the these updates, iterated for every affected column.

###Spatial tables

In order to display similar formatting in tmsch.sqlite, use the following set of SQL statements:

ALTER TABLE tmsch ADD COLUMN pct_gradrate07;  
ALTER TABLE tmsch ADD COLUMN pct_gradrate08;  
ALTER TABLE tmsch ADD COLUMN pct_gradrate09;  

UPDATE tmsch SET pct_gradrate07 = cast(round((all_gradrate07 * 100), 1) as text);  
UPDATE tmsch SET pct_gradrate08 = cast(round((all_gradrate08 * 100), 1) as text);  
UPDATE tmsch SET pct_gradrate09 = cast(round((all_gradrate09 * 100), 1) as text);  

UPDATE tmsch SET pct_gradrate07 = 'n/a' where pct_gradrate07 = '0.0%';   
UPDATE tmsch SET pct_gradrate08 = 'n/a' where pct_gradrate08 = '0.0%';  
UPDATE tmsch SET pct_gradrate09 = 'n/a' where pct_gradrate09 = '0.0%';

Making an dynamic, interactive map.

Conditional grade levels

In order to ensure that only elementary schools on the map display only 4th grade reading and math scores and not 8th or 10th or 12th scores (which are presumably be null), we need to create columns that flag if either reading or math exist for each grade level for the 09-10 school year. In ed_data.sqlite:

alter table clean_sch add column e04 text;
alter table clean_sch add column e08 text;
alter table clean_sch add column e10 text;
alter table clean_sch add column e12 text;
update clean_sch set e04 = (all_read04_0910||all_math04_0910);
update clean_sch set e08 = (all_read08_0910||all_math08_0910);
update clean_sch set e10 = (all_read10_0910||all_math10_0910);
update clean_sch set e12 = (all_read12_0910||all_math12_0910);
update clean_sch set e04 = '' where e04 = 'n/an/a';
update clean_sch set e08 = '' where e08 = 'n/an/a';
update clean_sch set e10 = '' where e10 = 'n/an/a';
update clean_sch set e12 = '' where e12 = 'n/an/a';

This should create a flag where either reading or math scores exist that will instruct the tooltip to display information for that grade level.

###Classes based on achievement.

In order for the color in the interactivity to match the color of the dot selected, create a column tmsch:

alter table tmsch add column grade text;  
update tmsch set grade = 'a' where (all_gradrate09 >= .9);  
update tmsch set grade = 'b' where (all_gradrate09 >= .8) & (all_gradrate09 < .9);  
update tmsch set grade = 'c' where (all_gradrate09 >= .7) & (all_gradrate09 < .8);  
update tmsch set grade = 'd' where (all_gradrate09 >= .6) & (all_gradrate09 < .7);  
update tmsch set grade = 'f' where (all_gradrate09 < .6) & (all_gradrate09 > 0);

Change from 08-09 to 09-10

To make the arrows in the tooltip dynamic several columns need to be created:

in tmsch:


alter table tmsch add column change;
update tmsch set change = 'up' where (all_gradrate09 > all_gradrate08);
update tmsch set change = 'down' where (all_gradrate09 < all_gradrate08);  
update tmsch set change = 'same' where (all_gradrate09 = all_gradrate08);

in clean_sch run these updates.

Joining in Tilemill

In order to join spatial data to the tabular data, copy the following joins into their respective TileMill projects.

For details about attaching database in Tilemill, refer back to the online documentation

ian29/ed-docs.md