Sue Hernandez's SharePoint Blog

SharePoint and Related Stuff

SharePoint, JQuery, Google Maps, and the CorasWorks DIT

I am going to show you how to use the CorasWorks Data Integration Toolset to retrieve geo-coded location information from a database and show it in a Content Editor Web Part in SharePoint using JQuery and the Google Maps api V3.

First of all, I recommend that you create 4 document libraries: 1 for holding Data Connections, 1  for holding Data Providers, 1 document library that holds Web Part Pages that you can use to display your data (unless you want the data on your default.aspx page), and finally 1 library to hold your jquery script, and any other supporting things you need like css or images.

For the data connection library, you will need to create a connection to your data source that will retrieve the geo-coded locations.  Here is a sample data connection file that is in the format that the DIT needs in order to load its data:

<?xml version="1.0" encoding="utf-8"?>
<CorasWorks>
	<Data>
		<Name>geodata</Name>
		<Default>true</Default>
		<ConnectionType>ADO</ConnectionType>
		<ConnectionString>Provider=sqloledb;Data Source=SERVER_NAME;Initial Catalog=DB_NAME;User Id=USER_ID;Password=PASSWORD</ConnectionString>
		<Query>
			SELECT     LocationId, Title, Address, City, State, Zip, Latitude, Longitude
			FROM       GeoLocations
		</Query>
		<Values>
		</Values>
	</Data>
</CorasWorks>

Load that up into your Data Connections library, and copy the URL to that file.

Next, create a web part page and place it in the Data Providers library.  Place an External Data Provider web part from CorasWorks on to the page and configure it with the URL to your data connection (put the URL in the “Source XML File Location” under the “Source XML” heading).  Make sure you turn on “Output XML” in the “Output Properties” heading.  You should now be presented with xml output similar to the following:

<NewDataSet>
     <Data>
          <LocationId>1</LocationId>
          <Title>Sues House</Title>
          <Address>123 Main Street</Address>
          <City>Manassas</City>
          <State>VA</State>
          <Zip>20110</Zip>
     </Data>
</NewDataSet>

Next, upload the latest version of jquery to your Document Library you created for this.  Copy the link to the file so you can use that in your map as a script reference.

Now we’re going to create a web part page to hold our map using a Content Editor Web Part.  Drop a CEWP on there and start off by putting  a script reference to both your jquery instance as well as the Google Maps API.

<script type="text/javascript" language="javascript" src="https://YOUR_SHAREPOINT_SERVER/PATH_TO_SITE/Scripts/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=false"></script>

Now we’ll add in the CEWP a div or a span (doesn’t matter which) to hold your map.

<div id="map_canvas" style="height:500px;width:100%">
     Loading...<br/>
     If the map does not load in a few seconds, then you may have security set to not allow unsecured content.  Please refresh the page and allow non-secure content to render on the page.
</div>

Now we’ll add a script tag, and put in some variables and an initialize function to initialize the Google Map.

<script type="text/javascript" language="javascript">
     var geocoder;
     var map;

     function initialize() {
     }
</script>

Now we’ll fill in the initialize function.  First we’re going to set up the Google Map, and then we’re going to make an AJAX call to the URL of the Data Provider (that XML output) that we created with the CorasWorks External Data Provider.

     function initialize() {
          geocoder = new google.maps.Geocoder();

          //  Roughly the middle of the US - make this whatever lat lon you want
          var myLatlng = new google.maps.LatLng(39.011902,-98.4842465);

          var myOptions = {
              zoom: 4,
              center: myLatlng,
              mapTypeId: google.maps.MapTypeId.ROADMAP
          }

          // Get the canvas
          var canvas = document.getElementById("map_canvas");

          // Get the Map and set it into the canvas
          map = new google.maps.Map(canvas, myOptions);

          $.ajax({
              url: "https://YOUR_SHAREPOINT_SERVER/YOUR_PATH_TO_SITE/Data%20Providers/GeoList%20Data.aspx",
              dataType: "xml",
              complete: processAjaxResult,
              contentType: "text/xml; charset=\"utf-8\""
          });
     }

Now we need to fill in the function “processAjaxResult”, which is a function called asynchronously after the AJAX call is made.  The function will get the nodes of your data and set up markers for each one.  It will either use the Latitude and Longitude if it was provided, or it will use the Google Geocoding service if it doesn’t have an address.

     function processAjaxResult(xData, status) {
          // Geocode and set marker

          var xDoc = xData.responseXML;
          var nodes = xDoc.selectNodes("//Data");

          for(var i = 0; i < nodes.length; i++)
          {
              var lat = nodes(i).selectSingleNode("Latitude");
              if(!lat) { lat = ""; }
              else { lat = nodes(i).selectSingleNode("Latitude").text; }

              var lon = nodes(i).selectSingleNode("Longitude");
              if(!lon) { lon = ""; }
              else { lon = nodes(i).selectSingleNode("Longitude").text; }

              var title = nodes(i).selectSingleNode("Title");
              if(!title) { title = "Your Location"; }
              else { title= nodes(i).selectSingleNode("Title").text; }

              if(lat != "" && lon != "")
              {
                  var latLon = new google.maps.LatLng(lat, lon);
                  setMarker(latLon, title);
              }
              else
              {
                  // No Latitude and/or Longitude.  Need to Geo Code it.
                  var streetAddress = nodes(i).selectSingleNode("Address");
                  if(!streetAddress) { streetAddress = ""; }
                  else { streetAddress = nodes(i).selectSingleNode("Address").text; }

                  var city = nodes(i).selectSingleNode("City");
                  if(!city) { city = ""; }
                  else { city = nodes(i).selectSingleNode("City").text; }

                  var state = nodes(i).selectSingleNode("State");
                  if(!state) { state = ""; }
                  else { state = nodes(i).selectSingleNode("State").text; }

                  var zip = nodes(i).selectSingleNode("Zip");
                  if(!zip) { zip = ""; }
                  else { zip = nodes(i).selectSingleNode("Zip").text; }

                  var fullAddress = streetAddress + " " + city + " " + state + " " + zip;
                  codeAddress(fullAddress, title);
              }
          }
     }

There were 2 functions referenced in this code snippet: setMarker and codeAddress. Here are those functions – they are very simple.

     function codeAddress(address, title) {
          geocoder.geocode( { 'address': address}, function(results, status) {
              if (status == google.maps.GeocoderStatus.OK) {
                  setMarker(results[0].geometry.location, title);
              } else {
                  alert("Geocode was not successful for the following reason: " + status + "   Location: " + address);
              }
          });
     }

     function setMarker(latLon, title) {
          var marker = new google.maps.Marker({
              position: latLon,
              title: title
          }); 

          marker.setMap(map);
     }

Finally, we need to call the initialize function when the body has finished loading.  So let’s use jquery to add the initialize function to the body onload.

     $(document).ready(function() {
          initialize();
     });

That’s all there is to it!

If you have tons of points on your map, check out Fluster2Cluster from SourceForge http://sourceforge.net/projects/fluster/

I have used this with great success, and even modified it to accept the HTML that will show up in an InfoWindow (one of those Google Pop-ups).

Advertisements

One response to “SharePoint, JQuery, Google Maps, and the CorasWorks DIT

  1. Pingback: Tweets that mention SharePoint, JQuery, Google Maps, and the CorasWorks DIT « Sue Hernandez's Blog -- Topsy.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: