Creating 3D Prism Maps using Google Earth
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 END
This 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#
- Click Website -> Add Reference
- From the .NET tab, scroll down and highlight Microsoft.SqlServer.Types
- Click OK
<!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>
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) { }
<%@ 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; } } }
