GoogleMaps and Search for NZ Schools

GoogleMaps and Search for NZ Schools

As preparation for my family's intended emigration to New Zealand in 2008, I thought it would be helpful (and, yes, fun!) to create a map of NZ schools, with some selection by decile. It became an intriguing exercise in juggling technologies. And it succeeded.

Screenshot from the Auckland School map

You can visit the NZ School map.

Finding the Schools Data

I started with superbly-useful spreadsheet of schools for 2008, downloadable from the NZ Ministry of Education. This has excellent detail: keyed on School Number, with postal address, decile, some category information and a head count.

Spreadsheet from the  Directory of Educational Institutions

Geocoding Schools Data

It was a matter of moments to get this into an SQL table, and then do some normalising to split out separate tables for the local authority and regional council. (Though I think I only did this from habit because I don't plan to use the information). More usefully, I could pick the gender and age categories, useful for drop-down boxes in my intended search form.

At this point I took my first step into unusual territory. As expected, there was address information but (of course) no Lat/Long which GoogleMaps require. Rolling up my sleeves, I visited the GoogleMap Geocoding help. This told me first how I could look up addresses "on the fly" to get coordinates, and more usefully, how to use an http request to give a physical address (in a web parameter) and get back either detailed JSON (structured text) results or simple LatLong with a status code. That's the chap for me!

But ordinarily that would mean typing in a URL for each school, and cut&paste the results from my browser into the SQL table. No way. So the other trick required the use of CURL for PHP. I had used this on another project this year (thanks again Richard) and it automates the get-and-put cycle within any PHP framework you like. So in a loop through the schools, I format the address, send it to Google with CURL, get a CURL return, test the status and if OK, insert that into my table.

This bit sound potentially useful to other propellerheads so I'll put it in full, if only to show how easy it is:

   $ch = curl_init();
   curl_setopt($ch, CURLOPT_RETURNTRANSFER,1); // return the page into a variable
   while($row = mysql_fetch_array($result)) {
      if (empty($row['lat_long']))   // If you haven't already done it
         $strFullAddress = $row['postal address 1'].','.$row['postal address 2'];
         // Geocoder gets confused if there is a repeat element 
         // eg Riverside Road,Orewa,Orewa
         if ( ($row['postal address 2'] !== $row['postal address 3']) 
            and (!empty($row['postal address 3'])) )
            $strFullAddress .= ', '.$row['postal address 3'];
          // Change spaces to plus sign, which Google likes
         $strGoogleAddress = str_replace (' ', '+', $strFullAddress);  
         $strUrl = ''
                      . $strGoogleAddress.'&gl=nz&output=csv&key='
                      . get_google_api_key()  ;
         curl_setopt($ch, CURLOPT_URL, $strUrl);    // Push the request
         $google_result = curl_exec ($ch);             // and get the result
         if (substr($google_result, 0, 3) == '200') {   // successful return
            $arrValues = explode (',',$google_result);
            $sql = "UPDATE tbl_nz_schools SET lat_long ='"
                       .$arrValues[2].','.$arrValues[3]  ."'"
                      .' WHERE school_number='.$row['school_number'];
            $update_result = mysql_query($sql);
            $strReturn .= '<br>Could not geocode '.$row['school_number']
                      .' '.$google_result
                      . ": ".$row['name'].': '.$strFullAddress ." \n" ;

   curl_close ($ch); 

In the request to Google, I used the country code &gl=nz to restrict results to NZ. And "get_google_api_key()" is my routine which returns my key, you will need your own.

I did this only for Auckland-area schools, of which there were 355. Of these, 26 did not geocode. I amended the code slightly to use some street address variables in place of the postal address ones, which helped. Then finally use manual searches plus a modified version of a Google geocoding example to nail the final few. I think it is of immense credit to Google technology that I could do this lookup so easily and with so few failures to geocode. Of those, only two could be called mistakes in Google data, tiny typoes in street names, which I submitted to their corrections page. Bravo, big G.

Map markers With my LatLongs glistening afresh in their table, I needed custom markers. I revisited some earlier work I had done for an oil-and-gas consultancy, where we plotted a Google world map of exploration fields. I crafted some simple markers as PNG files, different colours for each decile (well, with dull grey representing the lower half of the table, sorry!). I had an unduly fiddly time getting their shadows to work but it came together nicely. The code is too long to quote here, and anyway contributes nothing to your knowledge which you could not get much more reliably from Google themselves.

School Websites

The next and final task was to add to the data a column for school websites. At a cursory search, I could not find this in one comprehensive form such as a spreadsheet. However, the Ministry of Education pointed me to Te Kete Ipurangi (TKI). Here you can use a variety of search forms, in particular a wildcard search for school name. The results are shown in an HTML table, with a link holding the school ID (thankfully standard and equivalent to that in the MinEdu spreadsheet); plus a school name and a website if available.

It did not seem to allow a "*" search so I took a punt: I searched just for schools with "e" in their name, and saved the HTML. I repeated for "a", "t" and "s", gambling that between them this should get just about every school, and any that missed out, well, tough. Next, I used my trusty UltraEdit text editor which can record Macro sequences plus column editing. It can sort and remove duplicates. The details are not noteworthy, and the end result was a CSV file:

1086;Pukenui School (Kaitaia);
1087;Pukepoto School; 
1089;Purua School; 
108;Onewhero Area School; 

I kept the school name, even though I didn't need it in the destination table. I used it as a sanity check to ensure that the rows had kept aligned (between school ID and website) during the process; a glance shows the names and sites corresponded. I have learnt from experience on long data-collation projects, put some verification at milestones along the way else one mistake means you have to go back to Square One and start again.

This CSV I could import with phpMyAdmin or Navicat, making a new SQL table. I added a new column into my schools table, (arguably I could have kept this as a foreign key table), then used an UPDATE...SELECT...WHERE SQL query to merge the site column. A final twiddle with the GoogleMap display code put the site address as a clickable link into the GoogleMap info balloon using marker.openInfoWindowHtml.

GoogleMaps, SQL, CURL
Date: 2008-2009

Top of page