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.

Converting College Football Data for a Simple Website, using PHP, SQL and MySQL

The pages discussed here can be found at: http://www.sixmanguru.com/ncaa13.php

The idea of this project came from a thought that there does not exist a single place where you can see NCAA college football teams’ records and schedules easily, without having to make a bunch of clicks on very ‘graphically full’ websites.

The first step was to see if the data could be found easily. This was done easily, as Peter Wolfe keeps this information updated daily. (http://prwolfe.bol.ucla.edu/cfootball/scores.htm). Wolfe is a member of the official BCS rankings and also includes a comprehensive listing of conferences as well.

I initially broke the data we collected into four tables, listed on the top of the tables list (p2-tables-2.pdf). These tables are essentially the data in its raw form with some ‘cleaning’ taking place.

Next was to build the website. Based on this blog, a template was created to host the site. The entry page is http://www.sixmanguru.com/ncaa13.php.

The thought was to have a way to separate the various divisions of college football (NCAA I, NCAA I FBS, NCAA II, NCAA III and so on). This was accomplished by having a link for each at the top of the page that will pass a variable back to the site if clicked.

Once a division variable is passed (NCAA I BCS is assumed if there is a direct click to the site), there is a drop-down menu to filter the teams by conferences. ($q = “SELECT Conf_ID,conference_names FROM Conferences WHERE Div_ID='”.$division_id.”‘ ORDER BY conference_names”;)

Once a selection is made, the website show the conference standings, including conference wins, conference losses, wins and losses. This query only access the ‘cached’ table, big_team_records, we created, but more on this later. ($q = “SELECT * FROM big_team_records JOIN Conferences ON big_team_records.conference=Conferences.conference_names WHERE Conferences.Conf_ID='”.$conf_id.”‘ ORDER BY conf_wins DESC, wins DESC”;)

All teams shown in the results are ‘clickable’. If clicked, they link to another page (teams.php), passing the team ID. The team page then shows the data from the game file, including date, team, W/L and result. ($q = “SELECT * FROM big_game_table WHERE team_a='”.$team_name.”‘ OR team_b='”.$team_name.”‘ ORDER BY game_date ASC”;)

The first big problem was to create SQL code that would take what we have and create team records, including conference records. Since you have to create separate joins for the two sides of the scores when calculating wins/losses for a team, this can put a strain on the server, especially one that is online. When we got into also trying to calculate the conference wins/losses as well, it really made no sense with the MySQL server we were running.

The solution was to create ‘leftside’ and ‘rightside’ views, then combine them in another view, whole_game. This was all done on the Oracle SQL server. The view for the team_record was also done on the Oracle SQL server. Both of the finish product views were then imported to tables. These tables were then exported to Excel files and imported to the MySQL server.

This may be a little more work than initially anticipated, but the end result creates a much faster and flexible solution. The large game table that was created can be used to create much more interactivity with website visitors in the future, since all games are coded with each team’s individual and conference ID.

One other issue we had was that the date the initial data included worked fine for Oracle SQL, but not MySQL. This is shown below, but we took care of this by added some formatting for the initial view, so this would be invisible.

TO_CHAR(game_date, ‘YYYY-MM-DD’) AS game_date
This little bit of code had to be added since the date format used by the website is was scraped form uses a format compatible in our Oracle SQL db, BUT NOT in our MySQL db.

After the main four tables are created, we create master tables to essentially be used as cache in the MySQL database for the website. Their implementation is good, but allows for expansion of searches.

THIS MAKES THE ENTIRE VISITOR SIDE WORK

CREATE VIEW leftside AS SELECT ncaascores2013.game_ID, TO_CHAR(game_date, ‘YYYY-MM-DD’) AS game_date, fbteams_2013.team_ID AS VID, FBConferences.Conf_ID AS V_CID, ncaascores2013.team_a, ncaascores2013.score_a

FROM ncaascores2013
INNER JOIN fbteams_2013
ON ncaascores2013.team_a = fbteams_2013.team_name
INNER JOIN FBConferences
ON FBConferences.conference_names = fbteams_2013.conference

THIS MAKES THE ENTIRE HOME SIDE WORK

CREATE VIEW rightside AS SELECT ncaascores2013.game_ID, fbteams_2013.team_ID AS HID, FBConferences.Conf_ID AS H_CID, ncaascores2013.team_b, ncaascores2013.score_b, ncaascores2013.site
FROM ncaascores2013
INNER JOIN fbteams_2013
ON ncaascores2013.team_b = fbteams_2013.team_name
INNER JOIN FBConferences
ON FBConferences.conference_names = fbteams_2013.conference

COMBINE THEM

CREATE VIEW whole_game AS SELECT leftside.game_ID, leftside.game_date, leftside.VID, leftside.V_CID, leftside.team_a, leftside.score_a,
rightside.HID, rightside.H_CID, rightside.team_b, rightside.score_b, rightside.site
FROM leftside
JOIN rightside
ON leftside.game_ID=rightside.game_ID

MAKE A TABLE TO THEM PUT THE VIEW DATA IN

create the new table from the view
CREATE TABLE tblWholeGame AS SELECT *
FROM whole_game;

IMPORT THE DATA TO THE TABLE

INSERT INTO tblwholegame
SELECT *
FROM whole_game;

THIS DATA IS THEN EXPORTED TO USE IN THE OTHER ONLINE MYSQL DATABASE

THIS CREATES THE INITIAL TEAM_RECORD VIEW

CREATE view team_record AS SELECT
team_name,
SUM(CASE WHEN (team_ID=VID AND score_a>score_b) OR
(team_ID=HID AND score_b>score_a) THEN 1 ELSE 0 END) AS wins,
SUM(CASE WHEN (team_ID=VID AND score_a<score_b) OR
(team_ID=HID AND score_b<score_a) THEN 1 ELSE 0 END) AS losses, SUM(CASE WHEN (team_ID=VID AND score_a>score_b AND whole_game.V_CID=whole_game.H_CID) OR
(team_ID=HID AND score_b>score_a AND whole_game.H_CID=whole_game.V_CID) THEN 1 ELSE 0 END) AS conf_wins,
SUM(CASE WHEN (team_ID=VID AND score_a<score_b AND whole_game.V_CID=whole_game.H_CID) OR
(team_ID=HID AND score_b<score_a AND whole_game.V_CID=whole_game.H_CID) THEN 1 ELSE 0 END) AS conf_losses
FROM fbteams_2013,whole_game
GROUP BY team_name
ORDER BY team_name

WE THEN ADD SOME ADDITONAL DATA TO THIS

CREATE VIEW big_team_record AS SELECT fbteams_2013.team_id, fbteams_2013.division,fbteams_2013.conference,team_record.*
FROM fbteams_2013, team_record
WHERE fbteams_2013.team_name=team_record.team_name;

CREATING THE TABLE

CREATE TABLE tblBigTeamRecord AS SELECT *
FROM big_team_record;

IMPORTING THE DATA TO THE TABLE

INSERT INTO tblbigteamrecord
SELECT *
FROM big_team_record;

AGAIN, THIS DATA MUST BE EXPORTED TO THE ONLINE MYSQL DATABASE

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.