I started the evening ready to tutorialize myself in Google APIs and mapping examples. I read two paragraphs and realized that I’d better do some prep work on my own data before I continue.

I also realized that I can organize myself better if I set some goals for my coding work. I have one goal this week: Successfully map all the executions in the database with fancy little popup boxes showing the details for each state. That’s it.

Once I get that to work, for my tutorial in two weeks, I’ll drill down into Virginia, Maryland and DC to show more detailed local information. But, don’t let me get ahead of myself.

On the prepping side, every tutorial I looked at pleaded with the reader NOT to geocode the latitude and longitude information on the server, but to do it in the source database whenever possible.  Since I only have 51 states to deal with and the information is static, adding in lat/long for each execution in the database wasn’t going to require that much brain damage. I created a new table of state lat/long information and populated the appropriate general state lat/long information. [Like everything I do in phpMyAdmin, I did one state by hand and looked at the SQL commands to replicate for the other 50 states. (After 8 weeks, I am beginning to realize that I learn by trial and error–useful, but incredibly frustrating.)] Per Google’s request, I created my lat and long fields in the new table as FLOATS with the size of (10,6), which will allow me to store 6 digits after the decimal, plus up to 4 digits before the decimal, e.g. -123.456789 degrees. I then joined this new table to my main executions table with a new foreign key between my original executions table and the id from the statelatlong table.

I then needed to make sure I actually understood how to JOIN these tables, so I walked through a tutorial (on Wikipedia nobles), replaced their fields with my own and TA DA! it worked. For reference, all the information in the results, save lat and long, are located in my original table. The lat long coordinates are stored in the new table. (The code I used is here on my github.)

Now I feel like I’m ready to actually start trying to get my maps to work… the results of which, I’ll describe in a new post. Once I get there.