Mapping Your Data with Bing Maps and SQL Server 2008

Jonas Stawski takes you, step by step, through a sample project that demonstrates how to create an application that can get GeoSpatial coordinate data for addresses within a SQL Server database, and then use that data to locate those addresses on a Bing Map on a website as pushpins, either grouped or ungrouped: And there is full source-code too, in the speech-bubble.

Almost all applications rely on data one way or another, and most of those applications contain some sort of geographical or spatial data. If you think about the current application you are working on right now, I am sure that you can find some sort of spatial data. It could be an address, or perhaps the location of materials within a warehouse. With the spatial data types that were introduced with Microsoft SQL Server 2008, and the Bing Maps for Enterprise (Virtual Earth API), you have a new way to represent your data visually. This article shows you how to get started.

Microsoft SQL Server 2008 Spatial Data Types

Spatial data is represented through points, lines, and areas on a surface, using elements such as coordinates, triangles, rectangles, and polygons. In our day-to-day  work, we use these elements to represent locations on the earth, but the use of spatial data is not limited to geographical representations.  We can also use them to represent locations on a grid, a warehouse, or a simple rectangle. SQL Server 2008 has two new built-in Data types, the geometry and geography spatial data types. These  are implemented as Common Language Runtime (CLR) types, the CLR being introduced in SQL Server 2005. The main difference between these two types is that the geography data type is used for geodetic spatial data and the geometry data type for planar spatial data.

Figure   1 – Geodetic Model

Figure 2 –Planar Model

Bing Maps for Enterprise

Bing Maps provide two separate APIs to developers, the JavaScript API and Web Services API. The first one enables them to write applications with mapping and geo search capabilities, while the second one “allows you to match addresses to the map, search for points of interest, integrate maps and imagery, return driving directions, and incorporate other location intelligence into your Web application.” Bing Maps for Enterprise is the engine behind Bing Maps (http://www.bing.com/maps). Through the use of JavaScript and the Bing Maps API, one can develop applications that allow the user to perform both location and local searches as well as to visualize their business needs.

The Scenario

Your company has just upgraded from SQL Server 2005 to 2008. You are in charge of a new project that will visualize all the geospatial data of your database and provide tools to make better business decisions.

Transforming Your Data

In order to render the Bing maps, you must first transform your data so as to use the spatial data type. Before SQL Server 2008, the only possible way to include spatial data was to have one column for the latitude and another for the longitude, which combined results in a point. With SQL Server 2008 all we have to do is add a column with a geography (or geometry) data type to our table and then, just once, geocode the data into coordinates.  Once you have the geography data type, there are built-in functions that provides  data such as the length of a line, area of a square, surface of a circle, or simply  the latitude and longitude of a point. In our case, we need to transform our addresses into coordinates and then save them as points in the database. There are many free and paid services for geocoding, and most of the free ones limit you on the numbers of request you can make on a given period of time. I will use the staging service of the Bing Maps Web Service.

The accompanying project contains a SQL Server Express database with a customer table and some records with addresses.

First, we will geocode the data. I will do so by creating a Console Application that communicates with the Bing Maps Web Service.  You need to get a developer account before doing  this. You can get more information on the following url: http://msdn.microsoft.com/en-us/library/cc966926.aspx. After you sign up, you will receive instructions on how to add a reference to the Bing Map service and a web reference to the Token Service. Figure 3 shows how your Visual Studio project should look like after all the steps are finished.

Figure 3: Visual Studio Project with both references to the WCF Bing Map Service and the Token Web Service

We will use a console app that will perform  a one-time conversion. We need to get all the records that need geocoding  by calling the GetCustomersToGeocode() function. This  returns a List<Customer>. Customer which is a custom entity that will hold the customer information from our database.

Figure 4 displays the class definition of the Customer entity.

public class Customer {

 

public int ID { get; set; }

public string Address1 { get; set; }

public string Address2 { get; set; }

public string City { get; set; }

public string State { get; set; }

public string ZipCode { get; set; }

}

Figure 4: Customer entity definition

Once we have the list of customers to geocode, we need to run the actual geocoding: This is done by calling the Geocode function and passing in the list of customers to geocode. We will take advantage of the Bing Maps Service to pass in an address and get the latitude and longitude back. Figure 5 shows the code needed to call the Geocode function of the Bing Maps Service. We initially prepare the request by setting our own token, the address to geocode, and a filter of ‘high confidence’ in order to  get only precise results. We then send the request and wait for the response, which can return zero or more results. If we do have more than one result we are only interested in the first one so we populate our LocationResult object with the latitude, longitude, and the formatted address so we can return it to the caller function. Note that the code above uses the BMTokenService web service to retrieve a token using our Bing Maps username and password. The code is displayed on Figure 6.

client = new GeocodeServiceClient();

GeocodeRequest request = new GeocodeRequest();

 

//set the credentials

request.Credentials = new Credentials();

request.Credentials.Token = Token;

 

//Set the address

request.Query = String.Format(location);

 

//Get only results with a high confidence

ConfidenceFilter[] filters = new ConfidenceFilter[1];

filters[0] = new ConfidenceFilter();

filters[0].MinimumConfidence = Confidence.High;               

GeocodeOptions options = new GeocodeOptions();

options.Filters = filters;

 

//Make the request

GeocodeResponse response = client.Geocode(request);

if (response.Results.Length > 0 && response.Results[0].Locations.Length > 0)

{

result = new LocationResult

      {

            Latitude = response.Results[0].Locations[0].Latitude,

            Longitude = response.Results[0].Locations[0].Longitude,

            Address = response.Results[0].Address.FormattedAddress

      };

}

Figure 5: Code for utilizing the Geocode function of the Bing Maps Service

CommonService commonService = new CommonService();

//Set the credentials

string username = ConfigurationManager.AppSettings[“AccountID”];

string password = ConfigurationManager.AppSettings[“Password”];

commonService.Credentials = new NetworkCredential(username, password);

 

//Prepare the request

TokenSpecification tokenSpec = new TokenSpecification();

tokenSpec.ClientIPAddress = GetIPAddress();

tokenSpec.TokenValidityDurationMinutes = 60;

 

//Get the token

token = commonService.GetClientToken(tokenSpec);

Figure 6: Code for getting a token.

Once we have successfully retrieved the latitude and longitude from the service we then have to save the Point into the database. We do this by means of a simple SQL Update using the SQL Server STGeomFromText static method, which takes a string with the type of geography object you want to create and a Spatial Reference ID (SRID).  Figure 7 shows the query for updating the table. The string needs to be formatted in a Well-Known Text (WKT) representation from the Open Geospatial Consortium (OGC). In our case we use “POINT(Longitude Latitude),” but you can use others such as LINESTRING and POLYGON. The SRID is the type of Spatial Reference System this specific Geography object is using. Why do we need this? Because SQL Server will not perform calculations on Spatial objects of different systems. One system might use centimetres, while another one might use inches. In our example we are using 4326, which is the World Geodetic System, a standard used in cartography, geodesy, and navigation. Running the console app updates the Customer table with the correct points.

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

cmd.CommandText = “update Customer set SpatialLocation = geography::STGeomFromText(‘POINT(“ + location.Longitude + ” “ + location.Latitude + “)’, 4326) where CustomerID = “ + customer.ID;

 

Figure 7: SQL Query for updating the geography column

Figure 8: Results of the Customer table after the one-time conversion. Note the SpatialLocation column.

Visualizing the Spatial Data

 Now that our data has been ‘geocoded’, we need to create a visualisation of it on a webpage using the JavaScript API for Bing Maps. In order to do this, we will need to add a JavaScript reference to the API, passing in the specific version we want to use. As of the time of writing this article, the latest version is 6.2. After setting a reference we need a canvas upon which our map will be shown. The canvas is represented by a simple DIV whose position needs to be relative. Last but not least, we need to  initialize the map using JavaScript. This is done by calling the GetMap function on the page’s OnLoad event. Figure 9, 10, and 11 all show what is needed to generate a simple map on screen and Figure 12 shows the end result.

<script type=”text/javascript” src=”http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2″></script>

Figure 9: Reference to Bing Maps javascript API

<div id=’myMap’ style=”position:relative; width:400px; height:400px;”></div>

Figure 10: The map’s canvas, represented as a div.

var map = null;

function GetMap() {

map = new VEMap(‘myMap’);

map.LoadMap();

}

Figure 11: Javascript code needed to initialize the map.

Figure 12: The end result of the map initialization.

 In this case all we want to do is to visualize all those sales that are in Oregon. We do so by adding a button and querying our database. Unfortunately, Bing Maps utilizes JavaScript to manipulate the map and our button’s click event is executed on the server. Therefore, we need to find a way to bring the data we are querying on the server side down to the client side.

There are many ways to do so, but for this example we are simply going to generate a javascript array on the server side and then include it in the response by using the ScriptManager.RegisterClientScriptBlock static method.

 Let’s look at the code in Figure 13. The query gets the Lat and Long of the SpatialLocation column by joining SalesOrder and the Customer. Remember that the Geography datatype is a CLR Type and so it contains properties and methods. We then iterate through all the results and create two JavaScript arrays: one that holds all the latitudes and the other one that holds all the longitudes. After the creation of the arrays, we use the ScriptManager to include the arrays into the response so they could be made available to JavaScript on the client side. Finally, we call the LoadPushPins() JavaScript function on the page’s OnLoad event. Figure 14 shows the HTML brought back to the client containing the JavaScript array.

 

protected void btnGetSalesInOregon_Click(object sender, EventArgs e)

{

  SqlConnection conn = new SqlConnection();

  conn.ConnectionString =

ConfigurationManager.ConnectionStrings[“conn”].ConnectionString;

  SqlCommand cmd = new SqlCommand();

  cmd.Connection = conn;

  cmd.CommandText = “select distinct c.SpatialLocation.Lat Lat, “;

  cmd.CommandText += “c.SpatialLocation.Long Long “;

  cmd.CommandText += “from SalesOrder so “;

  cmd.CommandText += “inner join Customer c “;

  cmd.CommandText += “on c.CustomerID = so.CustomerID “;

  cmd.CommandText += “where c.State = ‘OR’”;

  cmd.CommandType = CommandType.Text;

  conn.Open();

  SqlDataReader dr = cmd.ExecuteReader();

  StringBuilder sb = new StringBuilder(“var lats = new Array(); var longs =

new Array();”);

  sb.AppendLine();

  int i = 0;

  while (dr.Read())

  {

    sb.AppendFormat(“lats[{0}]={1};longs[{0}]={2};”, i, dr[“Lat”],

dr[“long”]);

    i++;

  }

  conn.Close();

 

  ScriptManager.RegisterClientScriptBlock(this, this.GetType(),

“PointArrays”, sb.ToString(), true);

  body.Attributes[“onload”] += “LoadPushPins();”;

}

Figure 13: Server side code for retrieving all sales in Oregon.

<script type=“text/javascript”> //<![CDATA[var lats = new Array();

var longs = new Array();

lats[0]=43.4155817799037;longs[0]=-123.345552938182;lats[1]=44.0504273726889;

longs[1]=-121.354689398906;lats[2]=44.4613588739135;longs[2]=-122.767500368345;

lats[3]=44.5181382364839;longs[3]=-122.909226793623;lats[4]=44.5187939346442;

longs[4]=-122.908789661516;lats[5]=44.5284142560901;longs[5]=-122.785012973377;

lats[6]=44.6517025857035;longs[6]=-123.274368706652;lats[7]=44.8088686536;

longs[7]=-122.804506333268;lats[8]=44.9296263981259;longs[8]=-122.973348609551;

lats[9]=44.9423510405496;longs[9]=-123.002035404065;lats[10]=44.963299230734;

longs[10]=-123.159361981407;lats[11]=45.144972;longs[11]=-122.841643;

lats[12]=45.1555041690728;longs[12]=-122.836130109125;lats[13]=45.2176657207091;

longs[13]=-122.599450394006;lats[14]=45.3132883690892;longs[14]=-122.519550841095;

lats[15]=45.3252172944747;longs[15]=-122.585557789234;lats[16]=45.3344653706109;

longs[16]=-122.540232653902;lats[17]=45.3482999187747;longs[17]=-122.67126300294;

lats[18]=45.402125224529;longs[18]=-122.583030619241;lats[19]=45.410033;

longs[19]=-122.623621;lats[20]=45.438120321455;longs[20]=-122.841894788785

lats[21]=45.4437483973311;longs[21]=-122.612919527049;lats[22]=45.467383;

longs[22]=-122.889187;lats[23]=45.477759;longs[23]=-122.623477;lats[24]=45.489975;

longs[24]=-122.742481;lats[25]=45.494157;longs[25]=-122.577451;

lats[26]=45.5209022141955;longs[26]=-122.699007231348;lats[27]=45.529816;

longs[27]=-122.778038;lats[28]=45.5324759697441;longs[28]=-122.960644457695;

//]]>

</script> 

 

Figure 14: The end result of the javascript arrays

 After everything has been brought down to the client, it is then the turn of the LoadPushPins() function to do it’s magic. We start by  centering  and zooming the map. In this case we are hard-coding the center and the zoom level to show the entire state of Oregon. Then we iterate through every lat and long element of the arrays and on every iteration we create a VEShape item which is represented as a pushpin. Each pushpin will hold a specific set of coordinates from our sales data, utilizing the lats and longs array. We then set a title and a description that will be displayed when the user’s  mouse-pointer is over the pushpin  and add it to the map using map.AddShape. Figure 15 shows the code and Figure 16 shows the end result.

function LoadPushPins() {

map.SetCenterAndZoom(new VELatLong(43.79488907226601, -121.014), 6);

for (var i = 0; i < lats.length; i++) {

            var pp = new VEShape(VEShapeType.Pushpin, new VELatLong(lats[i],

longs[i]));

pp.SetTitle(“Address “ + i);

            pp.SetDescription(“This is the “ + i + ” element in the array”);

            map.AddShape(pp);

            }

        }

Figure 15: The javascript function that adds the pushpins to the map.

Figure 16: All sales in Oregon

 Making it Easier for the Eye

The marketing team is very happy that they can now see where all their sales are being made, and therefore make better business decisions. There is, however, a problem because  some areas are doing better than others and the pushpins are all on top of each other making the map very hard to read. To solve this, and make it easier on the eye, we are going to take advatage of the clustering functionality of the API. With clustering turned on, Bing Maps will group all those pushpins that are very close together and shows a special icon. This lets the user know that there are multiple pushpins in that location. If the user zooms in,  the API starts to automatically ungroup all the pushpins. In this example we are going to use the same query as the last example and automatically bring it down on the page load. The only difference is that we are going to automatically cluster the results and let the users decide whether they want to see clustered or unclustered pushpins. Figure 17 shows the GetMap javascript function which differs a little bit from our previous one.

 

function GetMap() {

map = new VEMap(‘myMap’);

      map.LoadMap();

      map.SetCenterAndZoom(new VELatLong(43.79488907226601, -121.014), 6);

      layer = new VEShapeLayer();

      layer.SetClusteringConfiguration(VEClusteringType.Grid);

      map.AddShapeLayer(layer);

      LoadPushPins();

}

Figure 17: The GetMap javascript function that automatically clusters all results.

To turn clustering on we need to add a layer on top of the map, set the layer’s clustering configuration to VEClusteringType.Grid and add all the pushpins to the layer instead of the map. To turn clustering on or off is just a matter of changing the layer’s clustering configuration from Grid to None. Figure 18 shows how to do so and Figure 19 and 20 show the end result.

function Uncluster() {

layer.SetClusteringConfiguration(VEClusteringType.None);

}

 

function Cluster() {

layer.SetClusteringConfiguration(VEClusteringType.Grid);

}

Figure 18: Javascript for clustering and unclustering

Figure 19: Clustered view  

  

Figure 20: Zoomed in view of clustered pins

Conclusion

We saw how easy it is to create spatial data in SQL Server and display it on the map using Bing Maps. We now know how to integrate SQL Server  and Bing Maps so as to visualize our geospatial data on the earth’s map, thereby making it easier for our business users to make decisions on their daily work. Stay tuned for part 2 of the article, which will show more examples on how to use the power of Bing Maps in conjunction with the power  of the SQL Server spatial data types API.

Happy Programming!