Part II. Geocoding Addresses with PHP/MySQL and the Google Maps API

This tutorial was adapted from the Google Maps API tutorial found here.


Assumptions:
- Must have a working knowledge of PHP/MySQL
- Must have WAMP/MAMP server installed, which can be found here.
(Note: WAMP is for Windows, MAMP is for Mac.)


Geocoding is the process of converting an address into a set of latitude/longitude coordinates (i.e. 1600 Pennsylvania Avenue --> 38.898, -77.037
), making it possible to plot addresses on a map.


Goal: This tutorial will teach you how to use the Google Maps API Geocoder to geocode a database of addresses.


This tutorial is broken up into four parts:

• Creating an Address Table
• Populating the Address Table
• Processing the Addresses with PHP
• Sample PHP Code

I. Creating an Address Table

We are going to create a table of addresses such that we are going to find the latitude and longitude coordinates for them. Using PHPMyAdmin create the table below, in a database that you have access to. The table name used here is markers in a database called omits_db.

Common Error: Sometimes when typing the name of a new table, there is an extra whitespace after the end of the word and that will cause a SQL error so be careful when entering the table name.

II. Populating the Address Table

After you create the table, you have to fill it with addresses to geocode. If you copy the text below and save it as a CSV (comma-separated values) format you can use the IMPORT tab to import the data into your table.

Note: In the example below I copy and pasted the text and saved the file as (phpsqlajax_data.csv). Make sure that in the Fields terminated by box that there is a comma. This is a common error. You must also input the column names in the bottom fields exactly as you inputted them in the table, or you will get an error.

III. Processing the Addresses with PHP

At this point, you should have a table named markers filled with sample data. You now need to write some PHP to iterate through the table, send a request to the geocoder, handle the response, and update the table appropriately.

To retrieve our addresses, we connect to our MySQL server, select the database in question, and run a query on the database to return those rows of interest. The HTTP geocoder in Google Maps API can return both XML and CSV format data, but I choose to get XML. The code below will connect to the database and then update the geocodes retrieved from Google.

Note: The default server on MAMP is called localhost and the default username and password is root. It is usually a good idea to change the default settings and also not to disclose your password like the demo below.

Note: In the sample code below, I wrote this script so that I could make sure my code was connecting to the database and retrieving the addresses, and then printing them to the screen.

Note: You should see something like the image below. Instead of actually having non-zero numbers, you should have all zeros.

Note: Before you can actually use the Google Maps API you have to sign up for an API key. Google "Google Maps API Key". You should see something like the screen below. Once you sign up you have to copy and paste the key into the sample code below, and replace the key that is already there.

Common Error: For every directory you are using the Google Maps API in, you have to generate a new key. For instance, http://localhost:8888/hello, http://localhost:8888/world, and http://localhost:8888/hello/world/ would require three different API keys.

Note: If you run the script below this will update the addresses in your database.For debugging purposes you should print some of the coordinates to the screen to make sure you are getting correct values.

Note: I added echo "$lat"."$lng"."htmlnewline"; on the blank line on line 48 to print the coordinates below.

Common Error: When debugging you sometimes have to print XML output to the screen, but sometimes it does not show up depending on which browser you use. So try different browsers once in a while.

Common Error: I don't know what this error is caused by, but sometimes I get errors when I have some extra blank lines in my code.

Then you should be able to view your data in your database with the updated coordinates.


Note: Adding the code below will print all of your errors to the screen. Apparently it solves 9/10 PHP problems.