Friday, June 3, 2011

Utilizing Maps services for runtime determining nearest relations

In a former SharePoint project – an external facing public website -, a required functionality was to display the nearest X insurance advisors given the submitted postal code of user. We weighed several alternatives to realize this:
  1. Re-use the distance-calculation algorithm used by the predecessor of the website (note: the essence of the project was a technical migration from a non-strategic proprietary WCM platform to the enterprise architecture target platform: SharePoint)
  2. Utilize service of postal company: a data repository of all postal code tuple combinations, with the distance between them administrated.
  3. Utilize Geo-coordinates to runtime calculate the distance between 2 locations, via the Haversine formula.
The first would have the disadvantage to still depend on the non-strategic platform and supplier. Also it had a rather complex programming API, without documentation and support.
The second would require us to host an external database besides the SharePoint content database in which to administrate the millions of postal code / distance records. This database would have to be updated each year with the management update from the postal company. Also, security constraints would not allow to directly access from the SharePoint WFE in the DMZ, the external database hosted in the internal network. The access should be regulated from DMZ to inner network via a secure webservice interface; which of course implies extra work and additional hosting costs.
So, the third alternative appeared the best fitted. Issue here is how to translate the postal code (submitted by the website user) into geo-coordinates (required for the Haversine formula). Luckily, both GoogleMaps and BingMaps provide services for this. Rough outline of the applied approach:
  1. Store in a SharePoint List the relations (less than 5000) data, including their geo-coordinates

  2. Runtime
  3. Invoke Maps service to derive the geo-coordinates (Latitude + Longitude) given the postal code submitted by user
  4. Calculate the distance to each applicable relation via the Haversine formula
  5. Ascending sort on calculated distance
  6. Display the X nearest advisors
An hosting constraint for step 2 is that it is not allowed to directly invoke from the SharePoint farm, external webservices. To circumvent that restriction we decided to call the Maps service in the security context of the browser, via jQuery/JSON. The returned geo-coordinated are next via a postback with arguments transmitted to the server side; where then the distance calculations are done.
Architecture sketch:

No comments:

Post a Comment