I decided to take on a Google Maps project, as Google Maps is one of my favorite apps.

In looking for data to Geocache, I came across the NYC Metro Intergroup website.  They have over a thousand meetings a week in NYC and its environs, yet their interface for finding a meeting leaves something to be desired.

There were quite a few obstacles, the first of which was scraping the data from the site itself.  I decided to start with the Manhattan meetings, of which there are about 750.  In order to scrape the data, I used the iMacros add-in for Firefox, and extracted to a CSV.  From there, I wrote some VBA code in Excel to parse the data into a workable form.

At first I decided to use a free mapping service called Click2Map, but the main problem with that was that it somehow, in the process of geocaching from Yahoo, placed half of the Manhattan meeting in Brooklyn.  It was also fairly primitive and slow, so I figured I would have a go at creating my own map using the Google Maps API, along with an excellent book from Apress called “Beginning Google Maps Applications with PHP and Ajax.”

I have a webserver hosted at GoDaddy under the domain michaelblaustein.com, but it was running IIS 6, with no PHP support.  After some poking around I discovered I could upgrade to IIS 7 which supported PHP 5.  I have been programming in VB for the past ten years, and am now out of a job, so I figured this would be a good opportunity to expand my skillset, using technologies that are a bit more widespread.  VBA doesn’t get much respect among programmers. although I have found it highly useful in the business world.

I had never really played with PHP before, except for a stint at a web marketing company and when I asked someone to code a shopping cart and he used PHP.  Still, I never really touched the code in either of those cases, and I was a little intimidated by the syntax.  I started with some examples from the Apress book, and the Google documentation (which is also quite good), and then came upon a way I could geocode all the addresses in the csv using the Google Geocoding API and some code examples.  I created a mySQL database on my server, used the phpMyAdmin tool provided by GoDaddy, created the table, and imported the data.  I wrote out the PHP to get the lattitude and longitude for these addresses, only to discover that PHP on my server was not able to request files.

A couple of calls to GoDaddy and some research showed me that the setting I needed was in the php.ini file, but I didn’t have access to it on the server.  I found out I could create my own and put it on the web root, but had no clue as to how to do so.  GoDaddy sent me a list of dlls in the extensions folder, and I found on the web someone elses php.ini.  Tried loading a couple of different versions of the ini only to break PHP.  Finally I discovered that running getinfo on my machine spelled out the current settings for me, so I went over the php.ini line by line trying to preserve the settings and making adjustments when needed.  I got it to work, but then it was timing out, so I edited the ini again.  Finally I had to edit the SQL in the PHP script a few times because it would only geocode about 150 records before giving me a 500 error.

I now have all the Manhattan meeting geocoded in a mySQL database on the server.  Next step is writing the Javascript to customize a map.  Once I get the map up on michaelblaustein.com, I also want to turn it into a mapplet, so that it can be found easily on Google.  Then I will submit my work to Metro Intergroup and see if they will give me access to the data on their web server so that I can avoid all this web page  scraping nonsense.  Then, perhaps, I can find some work doing this for a real estate company, or some such institution.

In the next few posts, I will go into a bit more detail about iMacros, the VBA code I wrote, the php.ini solution, and the specific php and javascript I wrote in order to geocode the data.

Advertisements