Creating Maps on the Fly For UIL Realignment

This morning the high school football season officially started with the release of the much anticipated 2014-2016 UIL Football Alignments. This usually started with the UIL servers crashing due to the high volume of traffic (it did briefly, prior to release). This year the UIL was prepared and had a back-up plan to divert traffic off their site.

So at exactly 9:00 am, the Twitterverse was alive with the ramblings of everyone who cares about Texas high school football.

I downloaded the files and immediately started sorting the teams into an Excel spreadsheet I had prepared for the occasion. Once done, I placed the data into my main database online.

I had been modifying my map code I created and showed in a previous post to handle the different divisions, sort them by division and district and color code them. By recycling this code, I easily created three maps.

Here’s the one where I sort them by division (blue for d1, red for d2)
http://sixmanfootball.com/big_alignment_map.php

Here’s a look at it

Then here are the ones where I separate Division 1 and 2 and then split up the districts.
http://sixmanfootball.com/alignment_map.php?did=1
http://sixmanfootball.com/alignment_map.php?did=2

Here’s an example shot of what they look like

Using the new google maps API, it took less than an hour to get everything up and running. All that was left was a little formatting and fine-tuning. The conversion of the data to an XML file makes all of the debugging so much easier.

Creating Maps with MySQL, PHP and Google Maps API v3

It has been awhile since I started this and never really followed up. I created this blog to be a space where I can share data, news and concepts I am working with.

I have been feverishly working on a project that will be hosted here that just doesn’t seem to want to end. In the meantime, I decided I needed to catch up with a few projects that I have actually completed.

Over the past week, Google has been sending out reminders that their support for Google Maps API v2 was ending. They were going to provide a wrapper to try and support the old API. With the six-man football season in the middle of the playoffs, I was trying to put this off for at least a few more weeks. Unfortunately that all changed when my maps went blank a few days ago.

As usual, I hit the place where I tried to find a blog or post where combining MySQL, PHP and GM v3 all went together. I also just starred at the screen for what seemed to be an eternity.

I am here to tell you, Google Maps v3 is great. Converting the data points to XML, then plotting them is really quite simple.

The example I am going to post might seem a bit long, but it is a modified version of the explanation on the google developer site, retold in terms humans without too, too much experience can decipher.

First off, you need to create the XML file.

The example I am using is taken from my own website, sixmanfootball.com. This file is used to populate two maps, including the one on the index page and the weekly maps page. It is WAY more complex than many uses, but I thought it would be good to show the details.

Let’s call this file, makeXML.php

include('your_db_connection_file.inc');
// I like to use weird names like .inc files for hidden files
// this is something I was taught by someone somewhere
//who was wiser than me

header("Content-type: text/xml");

//function to help parse everything to XML
function parseToXML($htmlStr)
{
$xmlStr=str_replace('<','<',$htmlStr); $xmlStr=str_replace('>','>',$xmlStr);
$xmlStr=str_replace('"','"',$xmlStr);
$xmlStr=str_replace("'",''',$xmlStr);
$xmlStr=str_replace("&",'&',$xmlStr);
return $xmlStr;
}

//your actual db connection
$dblink = mysql_connect('localhost', $dbuser, $dbpass);
$dblinked = mysql_select_db($dbname, $dblink);

$key=13;

// Start XML file, echo parent node
echo '';

//my SQL command to get the game info
$q2 = "SELECT team_a, team_b, game_location, game_date, game_time FROM games WHERE game_time>0 AND game_week=$key ORDER BY game_location, game_time DESC";
$r2 = mysql_query($q2);

while($a2 = mysql_fetch_array($r2))
{
$team_a = $a2[team_a];
$team_b = $a2[team_b];
$game_site = $a2[game_location];
$game_date = $a2[game_date];
$game_time = $a2[game_time];
$game_date=date('D',$game_date);
$game_time=$game_time = date('g:i A', $game_time);

$site_search = mysql_real_escape_string($game_site);
If ($site_search==$last_site)
{$previous=$game_name."
";} else
{$previous="";}

//sub SQL command to get the longitude and latitude
$q3 = "SELECT * FROM teams WHERE team_name='$site_search' AND team_lat<>0";
$r3 = mysql_query($q3);
while($a3 = mysql_fetch_array($r3))
{
$game_lat = $a3[team_lat];
$game_long = $a3[team_long];
$game_name = $previous.$team_a." vs ".$team_b." in ".$game_site." (".$game_date.", ".$game_time.")";

echo '<marker ';
echo 'name="' . parseToXML($game_name) . '" ';
echo 'lat="' . $game_lat . '" ';
echo 'lng="' . $game_long . '" ';
echo 'type="stadium"';
echo '/>';
}
//this is used to see if there is more than one game at a particular site
//that way the second node for that site is a combo node including both games
$last_site=$game_site;
}
// End XML file
echo '';

OK, now that we have XML, you can check it by calling the page via a browser and make sure it is all working.

The next step is creating the page where you make the actual map. Honestly, I was scared. I hate trying to figure out what I really did 2-3 years ago. I will share some generic code that ties in with the weekly_maps page.

In a later post, I will share how to pass a variable to the XML page for a specific query that is not predetermined.

This is all javascript, but I wrap it within a PHP template page.

So the end result looks something like this.

Here’s a photo when someone clicks a map bubble.