Prism maps are a type of thematic map that display extruded three-dimensional features to represent the value of an underlying variable. In this post, I'll shown you how to create a prism map using SQL Server 2008 and Google Earth, to take the results of a query like that this:
and make them into something much more exciting, like this:
To view this example you need to get the Google Earth plugin, which is currently available for Firefox, IE6, and IE7 on Windows, and Safari and Firefox on Mac OSX. If you can't see the globe above, click here to see what you're missing.
To recreate the example shown here, follow these steps:
1. Create and Populate the Source Table
This prism map displays data representing the ecological footprint of different countries around the globe. Ecological footprint is a measure of sustainability that represents the area of land required to produce the materials used by each member of the population, and to absorb the waste they produce. It is measured in hectares per capita. You can download a dataset detailing the ecological footprint of various countries from the NationMaster website.
Having acquired the statistical data to plot on the map, we also need to use a set of shape data (i.e. geometry / geography polygons) representing the countries of the world. For thematic mapping purposes, we don't need these shapes to be very accurate, so I started with the excellent simple world borders dataset from thematicmapping.org. I imported the data into SQL Server using Shape2SQL (part of SQL Spatial Tools) and then made a few tweaks to ensure that all of the polygons could be represented using the geography datatype (corrected ring orientation, self-intersections etc.) before joining each of the shapes to their associated footprint data.
You can download the script used to create and populate the final table from the following link:
EcoFootPrint.zip
2. Create the Stored Procedure
To create the stored procedure, execute the following code in SQL Server Management Studio:
CREATE PROCEDURE [dbo].[uspEcoFootprint]
AS
BEGIN
SELECT
COUNTRYNAME AS Title,
'Footprint: ' + CAST(FOOTPRINT AS VARCHAR(32)) + ' hectares per capita' AS Description,
SHAPE AS Shape,
100000 + CAST(COALESCE(FOOTPRINT,0)*100000 AS INT) AS Elevation,
COALESCE(
sys.fn_varbintohexstr(
CAST(200 AS BINARY(1)) -- Alpha
+ CAST(100 AS BINARY(1)) -- Blue
+ CAST(200 - CAST(FootPrint / (SELECT MAX(FootPrint) FROM EcoFootprint) * 128 AS INT) AS BINARY(1)) -- Green
+ CAST(127 + CAST(FootPrint / (SELECT MAX(FootPrint) FROM EcoFootprint) * 128 AS INT) AS BINARY(1)) -- Red
),
'0xff808080' -- Grey if no data available
) AS Color
FROM EcoFootPrint
ENDThis is just a normal SELECT statement that returns the various columns of information we will need to build the prism map.
- The TITLE and DESCRIPTION columns will be used to populate a tooltip window when you click on any of the countries of the map
- The SHAPE column returns the geography polygon data in raw format, which we will later manpulate into the KML required by Google Earth using a .NET handler
- The ELEVATION column calculates the amount by which each country should be extruded in the resulting map. The footprint of each country is multiplied by a scale factor of 100,000 and a base level of 100,000 is applied to every country to give a good range of values in the result set. The result is CAST as an integer since Google Earth will expect the elevation of each feature to be measure in a whole number of units.
- The final expression determines the colour in which each country will be shaded on the globe. The result is a 4-byte hexadecimal value in the format 0xFFFFFFFF where the bytes are in listed in Alpha (Opacity), Blue, Green Red order. Note that this is not RGB, as you may be used to. Countries with a small footprint are shaded light green. As the footprint increases, the colour turns increasingly red (and less green).
3. Create the Visual Studio Website
- Click File -> New -> Website
- Highlight Empty Website, select Language C#
To use the native SqlGeometry and SqlGeography datatypes from within the .NET handler, you need to add a reference to the SqlServer.Types.dll assembly, as follows:
- Click Website -> Add Reference
- From the .NET tab, scroll down and highlight Microsoft.SqlServer.Types
- Click OK
Add a new HTML page to the website (Website -> Add New Item -> HTML Page) and enter the following code:
<!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></title>
<script src="http://www.google.com/jsapi?key=ABQIAAAA_ign6iVyyoQkHVSOpMADihRLUAcsATA5ZNXYi8LSZQTvgk_wHxTfH2nhvU3F7BlRO8Je5tPGG2kHCA"></script>
<script src="JScript.js"></script></head>
</head>
<body onload="init()">
<div id='map3d_container' style='height: 500px; width: 658px;'>
<div id='map3d' style='height: 100%;'></div></div>
</body>
</html>
</body>
</html>
Next, add a new javascript page to the website (Website -> Add New Item -> JScript file). Make sure it is called the same name as referenced in the HTML file above (JScript.js, by default). Enter the following code:
google.load("earth", "1");
var ge = null;
function init() {
google.earth.createInstance("map3d", initCallback, failureCallback);
}
function initCallback(object) {
ge = object;
ge.getWindow().setVisibility(true);
var networkLink = ge.createNetworkLink("");
networkLink.setFlyToView(true);
var link = ge.createLink("");
link.setHref("http://YOURNETHANDLERHERE.ashx");
networkLink.setLink(link);
ge.getFeatures().appendChild(networkLink);
}
function failureCallback(object) {
}
Remember to change the href to match the URL of the handler!
Finally, create a new generic handler, using the C# language, and enter the following:
<%@ WebHandler Language="C#" Class="Handler" %>
using System;
using System.Web;
using System.Text; // Required to build the string
using Microsoft.SqlServer.Types; // Required to access native geometry and geography types
using System.Data.SqlClient; // Required to set up a SQL connection
using System.Data; // Required to set the command type
using System.Xml; // Required to create XML response
public class Handler : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
//Set the MIME type for KML
context.Response.ContentType = "application/vnd.google-earth.kml+xml";
XmlDocument kmlDoc = new XmlDocument();
XmlDeclaration xmlDeclaration = kmlDoc.CreateXmlDeclaration("1.0", "utf-8", null);
// Create the root element
XmlElement rootNode = kmlDoc.CreateElement("kml");
rootNode.SetAttribute("xmlns", @"http://www.opengis.net/kml/2.2");
kmlDoc.InsertBefore(xmlDeclaration, kmlDoc.DocumentElement);
kmlDoc.AppendChild(rootNode);
XmlElement documentNode = kmlDoc.CreateElement("Document");
rootNode.AppendChild(documentNode);
XmlElement styleNode = kmlDoc.CreateElement("Style");
styleNode.SetAttribute("id", @"examplePolyStyle");
documentNode.AppendChild(styleNode);
XmlElement polystyleNode = kmlDoc.CreateElement("PolyStyle");
styleNode.AppendChild(polystyleNode);
XmlElement colorNode = kmlDoc.CreateElement("color");
polystyleNode.AppendChild(colorNode);
colorNode.InnerText="ff0000cc";
XmlElement colorModeNode = kmlDoc.CreateElement("colorMode");
polystyleNode.AppendChild(colorModeNode);
colorModeNode.InnerText = "random";
SqlConnection myConn = new SqlConnection("server=ENTERYOURSERVERNAMEHERE;Trusted_Connection=yes;database=Spatial");
//Open the connection
myConn.Open();
//Define the stored procedure to execute
String myQuery = "dbo.uspEcoFootprint";
SqlCommand myCMD = new SqlCommand(myQuery, myConn);
myCMD.CommandType = System.Data.CommandType.StoredProcedure;
//Create a reader for the resultset
SqlDataReader myReader = myCMD.ExecuteReader();
while (myReader.Read())
{
SqlGeography shape = new SqlGeography();
shape = (SqlGeography)myReader["Shape"];
XmlElement placeMarkNode = kmlDoc.CreateElement("Placemark");
documentNode.AppendChild(placeMarkNode);
// Create the required nodes
XmlElement nameNode = kmlDoc.CreateElement("name");
XmlText nameText = kmlDoc.CreateTextNode(myReader["Title"].ToString());
placeMarkNode.AppendChild(nameNode);
nameNode.AppendChild(nameText);
XmlElement descNode = kmlDoc.CreateElement("description");
XmlText descriptionText = kmlDoc.CreateTextNode(myReader["Description"].ToString());
placeMarkNode.AppendChild(descNode);
descNode.AppendChild(descriptionText);
XmlElement styleUrlNode = kmlDoc.CreateElement("styleUrl");
XmlText styleUrlText = kmlDoc.CreateTextNode("#examplePolyStyle");
placeMarkNode.AppendChild(styleUrlNode);
styleUrlNode.AppendChild(styleUrlText);
switch (shape.STGeometryType().ToString())
{
case "Point":
placeMarkNode.AppendChild(createPoint(kmlDoc, shape, myReader["FootPrint"].ToString()));
break;
case "LineString":
placeMarkNode.AppendChild(createLineString(kmlDoc, shape, myReader["FootPrint"].ToString()));
break;
case "Polygon":
placeMarkNode.AppendChild(createPolygon(kmlDoc, shape, myReader["FootPrint"].ToString()));
break;
case "MultiPoint":
case "MultiLineString":
case "MultiPolygon":
case "GeometryCollection":
XmlElement multiGeom = kmlDoc.CreateElement("MultiGeometry");
placeMarkNode.AppendChild(multiGeom);
for (int g = 1; g <= shape.STNumGeometries(); g++)
{
switch (shape.STGeometryN(g).STGeometryType().ToString())
{
case "Point":
multiGeom.AppendChild(createPoint(kmlDoc, shape.STGeometryN(g), myReader["FootPrint"].ToString()));
break;
case "LineString":
multiGeom.AppendChild(createLineString(kmlDoc, shape.STGeometryN(g), myReader["FootPrint"].ToString()));
break;
case "Polygon":
multiGeom.AppendChild(createPolygon(kmlDoc, shape.STGeometryN(g), myReader["FootPrint"].ToString()));
break;
}
}
break;
}
}
//Close the reader
myReader.Close();
//Close the connection
myConn.Close();
XmlTextWriter writer = new XmlTextWriter(context.Response.OutputStream, null);
kmlDoc.WriteTo(writer);
writer.Flush();
}
public XmlElement createPoint(XmlDocument kmlDoc, SqlGeography shape, String Z)
{
XmlElement pointNode = kmlDoc.CreateElement("Point");
XmlElement coordsNode = kmlDoc.CreateElement("coordinates");
coordsNode.InnerText = string.Format("{0},{1},{2}", shape.Lat.ToString(), shape.Long.ToString(), Z);
pointNode.AppendChild(coordsNode);
return pointNode;
}
public XmlElement createLineString(XmlDocument kmlDoc, SqlGeography shape, String Z)
{
XmlElement lineStringNode = kmlDoc.CreateElement("LineString");
XmlElement coordsNode = kmlDoc.CreateElement("coordinates");
StringBuilder coordsString = new StringBuilder("");
for (int i = 1; i <= shape.STNumPoints(); i++)
{
coordsString.Append(string.Format("{0},{1},{2}", shape.STPointN(i).Long.ToString(), shape.STPointN(i).Lat.ToString(), Z));
coordsString.Append(Environment.NewLine);
}
coordsNode.InnerText = coordsString.ToString();
lineStringNode.AppendChild(coordsNode);
return lineStringNode;
}
public XmlElement createPolygon(XmlDocument kmlDoc, SqlGeography shape, String Z)
{
XmlElement polygonNode = kmlDoc.CreateElement("Polygon");
XmlElement extrudeNode = kmlDoc.CreateElement("extrude");
XmlText extrudeText = kmlDoc.CreateTextNode("1");
extrudeNode.AppendChild(extrudeText);
polygonNode.AppendChild(extrudeNode);
XmlElement altitudeNode = kmlDoc.CreateElement("altitudeMode");
XmlText altitudeText = kmlDoc.CreateTextNode("relativeToGround");
altitudeNode.AppendChild(altitudeText);
polygonNode.AppendChild(altitudeNode);
XmlElement outerboundaryNode = kmlDoc.CreateElement("outerBoundaryIs");
polygonNode.AppendChild(outerboundaryNode);
XmlElement linearRingNode = kmlDoc.CreateElement("LinearRing");
outerboundaryNode.AppendChild(linearRingNode);
XmlElement coordsNode = kmlDoc.CreateElement("coordinates");
StringBuilder coordsString = new StringBuilder("");
for (int k = 1; k <= shape.RingN(1).STNumPoints(); k++)
{
coordsString.Append(string.Format("{0},{1},{2}", shape.RingN(1).STPointN(k).Long.ToString(), shape.RingN(1).STPointN(k).Lat.ToString(), Z));
coordsString.Append(Environment.NewLine);
}
coordsNode.InnerText = coordsString.ToString();
linearRingNode.AppendChild(coordsNode);
return polygonNode;
}
public bool IsReusable
{
get
{
return false;
}
}
}
Remember to change the credentials and connection settings to match your SQL Server!
Finally, save all the files, and click 'Preview in Browser'.
