We wanted the ability to show our clients on a map, for various reasons. The GIS department was never able to produce this and anytime I asked about it, there was discussion about using the zip. I wanted a pin point on the address, so I took it upon myself to figure that out.
I knew that I could easily convert an address to latitude and longitude coordinates on Google Maps so I needed to do this for our client addresses stored in our ERP. After some digging around I found out this was called geocoding. Great. Then hunt for geocoding web services began. I had about 15,000 addresses to do and most of the APIs on the Internet would allow a couple hundred in a day, or 1,000 total. Until I found the MapQuest Goecoding API, which provided a very open service that I allowed 5,000 addresses per day.
I had to prove this out. I used C# .NET on a simple web page to supply an address to the API and display the coordinates and it worked great. Now I knew I needed a place to store these coordinates in our ERP. Luckily, all addresses were in one table in the database but the ERP did not allow custom fields on that table. So, I had to create a new table and use the addresses unique key to store just the latitude and the longitude. Once that was in place, I used C# .NET and Visual Studio to create a simple console application that would read each address, make a call to the MapQuest API, and then store the resulting coordinates in the table I made. It would loop through 5,000 address and then I ran it again the next day and the next. Viola! I have all the coordinates I needed.
But what if the address changes or a new address is entered or an address is deleted? Again back to Visual Studio, but this time I created a custom workflow using our ERPs API. I can tell our ERP to fire off this custom workflow when an address is added, changed, or deleted. It then will update the coordinates when the record is saved, or delete the existing coordinates if the address is removed. Viola! I now have auto updated coordinates.
Now on to display. I chose Google Maps API for it’s documentation and simplicity, and this was a proof of concept after all. I went the simple route of JavaScript objects and then used .NET to grab all of my coordinates and plop them in the JavaScript. Long load time, but the results were spectacular. I took it a step further and displayed client information on mouse hover. I then looked at the Next Meeting Date in the system for the clients and color coded according to overdue, about due, or in the clear. You could go any direction with this: Open a/r, overdue billings, last invoice date, map marker size by revenue, and so on. It was at this point that I took the solution to all the stakeholders that would find this useful or find a use for it. Everyone was very excited.
And then I was put on other projects and this sits on the shelf collecting dust. It sure was a blast to work on and it will be an awesome solution when someone is ready.