Creating Proportional Symbol Maps with Google Maps
This article is written in response to the following question asked by Luis Esteban Valencia Muñoz on the Katmai Spatial forums:
I want the Colombia Map, and next to some cities a number retrieved from the database, depending of the number there will be a small or big circle.
The Solution
There are several ways that you could approach this problem - one way would be to create a 'circular' polygon centred on the location of each city, using the STBuffer() method. The radius of the circle would be set based on the value of the item of data that you wished to represent (e.g. the population of that city). The disadvantage of this method is that plotting polygons on a map is much more complex than plotting points - and 'circular' polygons require a large number of points to define the perimeter of the circle, leading to more data passed from SQL Server to the map, and the map having to do more work to render that data. So, rather than use polygons, I'm going to represent each city on the map as a point location represented by an icon, but then scale that icon to different sizes based on the underlying data. This is much more efficient than using complex polygons, and has the added advantage that you can choose whatever icon you want - although the original question asked to plot a small or big circle, I'm going to use an icon of the Colombian flag instead.
Here's the finished product:
And here's how to do it:
Getting Some Sample Data
I'll admit that I don't know much about Colombia, and I certainly don't have a handy Colombian dataset sitting around, so the first thing I need to do is get some sample data. Enter Wikipedia to the rescue.
First of all, I got a list of the top cities in Colombia, taken from http://en.wikipedia.org/wiki/List_of_cities_in_Colombia. This table contains column of data with the population of each city, which I'll use to determine the size of the appropriate circle representing that city on the map. Next, I need to find out the coordinates of each city so that I know where to position the centre of each circle. You could probably look this up in a gazetteer on the internet, or use a geocoding technique as described in Chapter 7. However, since I've only got a small number of locations (and I'm curious to know more about Colombia), I'm simply going to find them by looking them up in Google Earth, and writing down the latitude and longitude coordinates displayed at the bottom of the screen when I'm centered over each city.
Having done all this, I can define a sample set of data as follows:
CREATE TABLE ColombiaCities ( Name VARCHAR(255), Population INT, Location geography ) INSERT INTO ColombiaCities VALUES('Bogotá',7363494,geography::Point(4.65,-74.1,4326)) INSERT INTO ColombiaCities VALUES('Valle del Cauca',2498074,geography::Point(3.42,-76.5,4326)) INSERT INTO ColombiaCities VALUES('Antioquia',2042093,geography::Point(6.25,-75.57,4326)) INSERT INTO ColombiaCities VALUES('Atlántico',1429031,geography::Point(10.96,-74.8,4326)) INSERT INTO ColombiaCities VALUES('Bolívar',1001044,geography::Point(10.4,-75.5,4326))
Now that I've got the points defining each city, I could try to acquire a vector (polygon) dataset representing the country of Colombia against which to plot the location of the cities. But why would you want to do this when you can plot your own spatial data against a base map provided by Virtual Earth or Google Maps instead? These mapping providers (and others) provide an astounding quality of existing raster data (aerial photography) and vector data (roads, railways etc.) ready for use in your own applications.
To plot data from SQL Server on either Virtual Earth or Google Maps, let's create a .NET handler that connects to SQL Server, executes a query to select data from the ColombiaCities table, and then styles the results obtained from the STAsText() method into the appropriate JavaScript constructor methods for the API in question. (This technique is covered in detail in Chapter 9).
Create the stored procedure
The stored procedure will retrieve the results from the table and pass them to the .NET handler. There's no special magic done by the stored procedure - it just retrieves the data for each city, including the WKT representation of the point. It is the role of the .NET handler to take the results returned by the stored procedure and turn them into the appropriate constructor functions for Google Maps.
CREATE PROCEDURE [dbo].[uspSelectColombiaCities] AS BEGIN SELECT Location.STAsText() AS WKT, Name, Population FROM ColombiaCities END GO
ColombiaCities.ashx
The .NET handler creates a connection to SQL Server, executes the stored procedure, and loops through the resultset. It manipulates the WKT representation of each point to the appropriate syntax required to create a Google Earth marker representating that city. It then sets the size of the icon for that marker based on the value of the population of that city.
<%@ WebHandler Language="VB" Class="ColombiaCities" Debug=true%> Imports System Imports System.Web Imports System.Data.SqlClient Imports System.Configuration Imports System.Text Public Class ColombiaCities : Implements System.Web.IHttpHandler Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest 'The response will be javascript context.Response.ContentType = "text/javascript" 'Tell the browser not to cache the results context.Response.CacheControl = "no-cache" context.Response.Expires = 0 'Define the variables Dim Output As String = "" 'The javascript we will build and send back Dim WKT As String = "" 'The original WKT representation Dim id As Integer = 0 'Shape counter Dim Shape As String = "" 'The unique name for each shape Dim ShapeTitle As String = "" 'The title to display for each shape Dim ShapeDescription As String = "" 'The description attached to the shape 'Set up a connection to the server Dim myConn = New SqlConnection("server=VIRTUAL-WINXP;" & _ "Trusted_Connection=yes;" & _ "database=Spatial") 'Open the connection myConn.Open() 'Define the stored prcedure to execute Dim myQuery As String = "dbo.uspSelectColombiaCities" Dim myCMD As New SqlCommand(myQuery, myConn) myCMD.CommandType = Data.CommandType.StoredProcedure 'Create a reader for the resultset Dim myReader As SqlDataReader = myCMD.ExecuteReader() 'Go through the results While myReader.Read() 'Set a unique variable name for this shape Shape = "shape" + id.ToString() 'Determine the appropriate icon size Output += "iconBlue.iconSize = new google.maps.Size(" + _ CInt((myReader("Population")) / 100000).ToString + ", " + _ CInt((myReader("Population")) / 100000).ToString + ");" 'Set the title for the icon Output += "options.title = """ + myReader("Name").ToString + _ "Population: " + myReader("Population").ToString + """;" 'Get the WKT representation of the object WKT = myReader("WKT").ToString 'Replace the double brackets that surround the coordinate point pair WKT = Replace(WKT, "POINT (", "") 'Remove the closing double brackets WKT = Replace(WKT, ")", "") 'Build the appropriate Pushpin/GMarker object from the coordinates Dim Coords() = Split(Trim(WKT), " ") Output += "var " + Shape + "=new google.maps.Marker(new google.maps.LatLng(" + Coords(1) + "," + Coords(0) + "), options);" 'Add the marker to the map Output += "map.addOverlay(" + Shape + ");" id = id + 1 End While 'Close the reader myReader.Close() 'Close the connection myConn.Close() 'Output all of the javascript to create all the objects context.Response.Write(Output) End Sub Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable Get Return False End Get End Property End Class
ColombiaCities.htm
Finally, we need to create the webpage that will contain the Google Maps control, and get it to call the .NET handler (via AJAX), and eval() the results to add them to the map.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>Colombian Cities</title> <script src="http://www.google.com/jsapi?key=INSERTYOURAPIKEYHERE" type="text/javascript"></script> <script src="ColombiaCities.js" type="text/javascript"></script> </head> <body> <div id="divMap" style="position:absolute; width:640px; height:480px;"></div> </body> </html>
ColombiaCities.js
// Declare the global map object var map = null; // Load the Google Maps API google.load("maps", "2"); // Set the onLoad callback google.setOnLoadCallback(getMap); // Set the unLoad callback if (window.addEventListener) { window.addEventListener("unload", disposeMap, false); } else if (window.attachEvent) { window.attachEvent("onunload", disposeMap); } // This function is called when the page has been loaded function getMap() { // Create a new map object in the divMap container map = new google.maps.Map2(document.getElementById("divMap")); // Configure the initial map view map.setCenter(new google.maps.LatLng(6, -74), 6, G_NORMAL_MAP); // Load the city data loadCityData(); } // This function creates a cross-browser AJAX object function GetXmlHttp() { var x = null; try { x = new ActiveXObject("Msxml2.XMLHTTP"); } catch (e) { try { x = new ActiveXObject("Microsoft.XMLHTTP"); } catch (e) { x = null; } } if (!x && typeof XMLHttpRequest != "undefined") { x = new XMLHttpRequest(); } return x; } // This function is called when the mouse is clicked function loadCityData() { var iconBlue = new google.maps.Icon(); iconBlue.image = "http://upload.wikimedia.org/wikipedia/commons/thumb/b/b2/Colombia_flag_300.png/50px-Colombia_flag_300.png"; iconBlue.iconSize = new google.maps.Size(12, 20); iconBlue.iconAnchor = new google.maps.Point(6, 20); var options = { draggable: false, icon: iconBlue }; map.clearOverlays(); //Get the appropriate XMLHTTP object for the browser var xmlhttp = GetXmlHttp(); // If we have a valid XMLHTTP object if (xmlhttp) { // Define the URL of the handler var url = "./ColombiaCities.ashx"; // Open the XmlHTTP request xmlhttp.open("GET", url, true); // Fire this when the readyState of the request changes xmlhttp.onreadystatechange = function() { // readystate 4 indicates that the request is complete if (xmlhttp.readyState == 4) { // Read in the JavaScript response from the handler var result = xmlhttp.responseText; // Update the status message window.status = 'Loading Data... Please Wait.'; try { // Execute the dynamically created JavaScript eval(result); // Update the status message window.status = 'Data Loaded!'; } catch (e) { // If the response cannot be evaluated window.status = 'Data could not be loaded.'; } } } // Send the XMLHTTP request xmlhttp.send(null); } } // This function is called when the page is unloaded function disposeMap() { // Dispose of the map object google.maps.Unload(); // Unset the map variable map = null; }
Comments
Post new comment