devights/Emergency — pull request #24 by cookieguru (2014-08-15 23:28:59+00:00)
Title: Update table schema, add bootstrap data

I'm not going to be much help since I don't speak Python, but I took a stab at some of the data. I added all the Seattle Fire Department stations and most of the apparatus and locations. Also included the address lat/lon tuples to immediately populate the map for the times when the Socrata table is lagging.

* * *

Here's the URL to call the geocoder:
http://maps.googleapis.com/maps/api/geocode/json?sensor=false&bounds=47.77777,-122.435808|47.495495,-122.245685®ion=us&address=318+2nd+Av+Et+S,+Seattle,+WA

I look at the first result (sometimes there's more than one) and look at the following values:

```
lat = geometry/location/lat
lon = geometry/location/lng
accuracy = geometry/location_type
matched_component = type
matched_string = formatted_address
```

When accuracy is `GEOMETRIC_CENTER` or `APPROXIMATE` the result should be reviewed by a human. Accuracies "Exact" and "Unknown" are ones I invented to help filter out data that has/has not been cleaned. If _matched_component_ is anything other than `intersection`, `street_address`, or `subpremise` it should be reviewed by a human. _matched_string_ is nice to keep on hand as it shows exactly what the geocoder matched.

* The SFD call log often uses a slash to delimit the street names of an intersection. Google's geocoder doesn't understand this, so it is necessary to replace `/` with `&`
* Look at the [`types`](https://developers.google.com/maps/documentation/javascript/geocoding#GeocodingResults) in the return. Often a bus stop will match, and the bus stop is usually located a half block from the center of the intersection

When manually reviewing entries, [this](http://depts.washington.edu/uwpdweb/wordpress/wp-content/uploads/2014/06/061720142.pdf) is a good resource for figuring out which building on UW campus is tied to a particular address.

* * *

Harborview MC and Headquaters do not have station numbers. I have assigned them 1 and 0 respectively to match the schema. AUTO_INCREMENT had to be turned off. Also, the Assignment table was modified to match Vehicle.
```SQL
ALTER TABLE `Station` ALTER `id` DROP DEFAULT;
ALTER TABLE `Assignment` ALTER `vehicle_id` DROP DEFAULT;
ALTER TABLE `Assignment` CHANGE COLUMN `vehicle_id` `vehicle_id` VARCHAR(8) NOT NULL FIRST;
```

I don't know what the unit numbers are in the CAD for these units.

| Location | Unit |
| ----------- -------------------------------- |
| Station 2 | Fragmentation Cache |
| Station 5 | Fireboat Alki |
| Station 10 | Safety |
| Station 10 | Unit 77 (Reserve HazMat) |
| Station 14 | Confined Space Rescue Unit |
| Station 14 | Dive Unit, Tunnel Rescue Unit |
| Station 14 | High Angle Rescue Unit |
| Station 14 | Reserve Ladder |
| Station 14 | Technical Rescue Unit |
| Station 17 | Future Medic Unit |
| Station 17 | Future Tunnel Rescue Unit |
| Station 18 | Hose 18 |
| Station 18 | Reserve Ladder |
| Station 18 | Reserve Medic Unit |
| Station 21 | MCI (Multiple Casualty Unit) |
| Station 22 | Incident Command Unit |
| Station 24 | Reserve Engine |
| Station 25 | Reserve Ladder |
| Station 27 | Mobile Decontamination Unit |
| Station 29 | Reserve Engine |
| Station 30 | Reserve Engine |
| Station 31 | Reserve Medic Unit |
| Station 32 | Emergency Fragmentation Cache |
| Station 32 | Reserve Medic Unit |
| Station 33 | Reserve Engine |
| Station 34 | Hose 34 |
| Station 35 | Reserve Engine |
| Station 36 | Tunnel Rescue Unit |
| Station 36 | Unit 99 (Marine Specialty Unit) |
| Station 37 | Reserve Engine |
| Station 38 | Reserve Engine |
| Station 39 | Reserve Engine |
| Station 40 | Reserve Engine |

Reserve units are prefixed with 8, so Station 40's reserve engine is likely E840 but I want to double check they're in your data before adding it to the bootstrap SQL (every station has at least one engine, so engine numbers always match the station numbers; not every station has a ladder so those numbers are sequential). Also, I'm curious what other units aren't listed here or in the bootstrap SQL.