Very useful link: How to take data from MySQL and output GeoJSON using Python.
AMP on Mac
Two sites with great instructions on how to install and enable MySQL, PHP, and Apache on Mac OS X
Unpivoting a Table
I have a pivot table with Routes going down and years going across, but I need to normalize it – have a column for the route and a column for the year and then a value for each route, column.
| Route | 2015 | 2016 | 2017 | 2018 |
|---|---|---|---|---|
| North | 12 | 14 | 18 | 24 |
| South | 23 | 21 | 19 | 17 |
| East | 19 | 24 | 12 | 10 |
| West | 45 | 58 | 57 | 59 |
| Desert | 23 | 10 | 3 | 1 |
| Plains | 23 | 23 | 23 | 23 |
| Scenic | 49 | 48 | 37 | 38 |
| Bad Lands | 12 | 12 | 8 | 8 |
| Roundabout | 3 | 3 | 3 | 6 |
Oracle and MS SQL databases have a function called UNPIVOT.
MySQL does not have a similar function yet, so you have to do a SELECT, UNION ALL type command.
In R Project, the function is called “Melt”
Doing this in R Project:
1. Install the reshape library if you don’t have it.
2. Program looks like so:
library(reshape)
rte_vol=read.csv("/users/sarod/documents/route_volume.csv")
rte_vol_melted<-melt(rte_vol, id=c("Route"))
rte_vol_melted
Line 1 runs the reshape library
Line 2 reads the data in
Line 3 "Melts" the data
Line 4 Shows the output
MySQL Working with Nulls
Some useful MySQL commands:
How to find rows with null data and delete them:
delete from corridor where wb_id is null;
Note that this WON’T work:
delete from corridor where wb_id = null;
How to update a blank field:
|
1 |
<span class="sql1-reservedword">update </span><span class="sql1-tablename">corridor </span><span class="sql1-reservedword">set </span><span class="sql1-identifier">corr</span><span class="sql1-symbol">=</span><span class="sql1-string">'MC' </span><span class="sql1-reservedword">where </span><span class="sql1-identifier">corr </span><span class="sql1-reservedword">is </span><span class="sql1-reservedword">null</span><span class="sql1-symbol">; </span> |