Creating a Google Maps/Amazon.com/Banned Books Mashup

Part One of God Only Knows How Many Steps...

Dec 27, 2007
0 comments

Submitted By: Justin Stanley
Filed Under:

• AJAX
• General Advice
• Google
• JavaScript
• MySQL
• PHP

And now for something completely different…

Sorry to disappoint all the little nerdlings who come around here only for the Drupal goods, but today’s post won’t be at all related to our favorite CMS.  We’ve talked about different Drupaly topics for weeks, now it’s time to shake things up a little.  Today, kids, we’re going to make a basic Google Maps mashup.

Yeah, I know.  The world needs another Google Maps mashup like I need a hole in my head, right?  Who cares.  They’re fun, and we’ll try to make our sample a little more interesting by throwing in a little Amazon.com action and some AJAX-y goodness.

So here’s what we’re shooting for:

http://www.nerdliness.com/ajaxdemo/

First of all, don’t laugh.  Jeremy’s the form guy, I only do function.  Sure, it ain’t much to look at, but it works and it’s kinda cool.  And if we wanted it to look nice, we’d pay Jeremy his surprisingly reasonable freelance rate to pretty it up for us.

Ok, so what’s the big deal?  What are we doing on that page?   In a nutshell, we’re plotting, on the fly, the locations of every public school in Texas that, according to the ACLU of Texas, banned a book during 2006.  I took the data from a PDF they release annually (http://www.aclutx.org/projects/bannedbookspg.php?pid=45) and used it to populate a MySQL table with the various data you see by clicking on the assorted links. 

The data is sent over to the fine people at Google to plot the little book icons on the map when you click on various links.  When you click on the icon on the map itself, we hit the database again to display the location’s info (address, etc).  At the same time, we send a request to Amazon.com to get the info we need to dynamically create links to their product info pages for the various book titles. 

Finally, we’re using some AJAX to narrow down your search results as you type.  For instance, if you click Book Info and start typing the word “The,” you’ll see that the list is updated with every press of the key to show just the titles that contain what you’ve typed so far.  You can then click on the title of the book in question to plot its location on the map.

Now, a couple of disclaimers.  First, this sample is far from perfect.  Not only is it about as pretty as the Sam, the three-time World’s Ugliest Dog champ (http://www.snopes.com/photos/animals/uglydog.asp), but it doesn’t function quite the way you’d expect.  Information you might think would appear in one area doesn’t show up unless you click on a book icon, for instance.  Not very intuitive and certainly not a finished product.  Still, though, it’s good enough to give you an idea of what you can do and how you might do it.

Second, as with everything we do here, the sample code should be accepted “as-is,” with no guarantee implied.  I’ll provide it all at the end, but use it at your own risk.  I don’t think anything will blow up if you use it, but consider yourself warned.

Finally, while this little tutorial is intended for the beginner, there are a few prerequisites if you want to play the home version:

1. You need a Google Maps API key.  They’re free and easily obtained, but they’re also uniquely assigned based on your domain name.  While you might see the Nerdliness.com one in my samples, it simply won’t work for you.  Get your own here:  http://code.google.com/apis/maps/signup.html
2. If you’re interested in the Amazon.com part, you’ll need to sign up with them, too.  It’s also free and you can earn a commission on any sales you generate.  Sign up for that here:  https://affiliate-program.amazon.com/
3. Some sort of database.  I’m using MySQL for the backend database.  You can use whatever database you like, but you might need to adjust the various queries accordingly.
4. PHP. 

Other than that, you just need your favorite text editor and the ability to follow directions.  A little HTML/SQL/PHP/JavaScript knowledge wouldn’t kill you, but it’s totally not necessary.  I'm assuming you don't know much.

One last thing before we get started…  this will be a multipart post, but I have no idea exactly how many parts we’re looking at.  Sorta depends on how things look as we get going.  Subsequent parts should be posted within a couple of days, a week tops, though they might be broken up by additional Drupalesque posts from Jeremy and/or Fredric.

Ok, so in today’s post, we’re just going to set things up.  If you haven’t obtained that Google Maps API key I mentioned above, do that now.  Go ahead.  I’ll wait…  If you want to play the Amazon game, then snag that, too.

Done?  Great.  Now, let’s get you some data.  Like I mentioned before, our example is plotting some banned book information strip out of a PDF posted on the ACLU of Texas website (http://www.aclutx.org).  Believe me when I say that massaging that data was a long, tedious process, one that I don’t care to repeat any time soon and that I wouldn’t wish on you, gentle reader.  Instead, I’ll give you a shortcut. 

Download this zip file:  http://www.nerdliness.com/files/sqlsetup.zip

Inside, you’ll find several SQL scripts that will create the tables we need for this experiment and fill them with the data stripped from the ACLU PDF.  Because of the relationships between the tables, you’ll need to make sure to run the scripts in the following order:

1. createmysqltables.sql
2. authors.sql
3. books.sql
4. locations.sql
5. reasons.sql
6. bookauthors.sql
7. challenges.sql
8. challengereasons.sql

Of course, being the astute and savvy Internet user you are, you’ll be looking at these scripts before you actual execute them, right?  I certainly wouldn’t trust some faceless yahoo…

And having looked at the scripts, you’ll notice a couple of things.  First, we’re creating a fairly normalized database consisting of seven tables.  The relationships look something like this:

For those of you who aren’t database people, that there’s what ya call an Entity-Relationship Diagram.  As the name implies, it provides a visual representation of the relationships between various entities.  In this case, each entity is a table in our database. 

Each square is a table in our database, and the information listed in the square tells you a little something about that particular table.  For example, the CHALLENGES table is represented by a block with the word “challenges” at the top.  Simple, huh?  Within that block, you can see a list of the various columns within that table (challengeid, locationid, bookid, actiontaken, notes, challengeyear).  Any columns that are part of a Primary Key are listed at the top, underlined, and have the letters PK to the left.  Columns in a Foreign Key relationship (a value listed in this table that’s a Primary Key in another table) are labeled FK.  Finally, required columns (those created using the NOT NULL SQL clause) are in bold.

The lines between the different entities tell you about the relationship between them.  For instance, look at the line connecting the LOCATIONS table with the CHALLENGES table.  That particular relationship is called a one-to-many relationship, meaning that each CHALLENGE we log in the database is mapped to exactly one specific LOCATION.  However, each LOCATION could potentially issue one or more challenges.  But I digress…  if you want more on E-R Diagrams, talk to Google.

Second thing you might notice is that we’re not filling in all the data in the LOCATIONS table.  Why?  Well, it has to do with geocoding.

See, Google Maps can’t just plot any old location just by the address.  Instead, it plots them out according to the longitude and latitude of the place in question.  That process of translating a street address into its longitudinal and latitudinal coordinates is called geocoding.
 
The Google Maps API will allow you to geocode addresses on the fly.  In fact, that’s how I originally set up this example…  every time I clicked on a location, I’d send a request to Google to geocode that address.  When that one came back, I’d send another request to actually plot the point.

That method lends itself to a couple of problems.  First, it greatly increases the amount of time it takes to plot out points.  Geocoding is an expensive process (resource-wise, not necessarily monetarily).  When you geocode every point every time, loading the page can take a while.  In some situations, you might not have a choice.  Want to map a moving target?  You’ll have to geocode on the fly.  In our case, though, them schools ain’t going any where, so we’re pretty safe to geocode once and store the data in our table for future use. 

The second problem is that the code needs to be a little more robust if you’re geocoding on the fly.  What happens if you have a malformed address in the mix and your code fails trying to geocode it?  If you don’t take that into account, your code might choke every time you try to plot that point.  I’m lazy.  I like the idea of running the script one time, monitoring for failures, and dealing with them by simply ignoring any entries in the database missing the longitude and latitude info.

And this, my friends, is where we're going to leave off for today.  Tune in next time as we create our first little bit o' AJAX to geocode all our addresses and fill in the rest of the blanks in our table.