List of links to all the SQL 2008 Spatial Methods
SoulSolutions -
![johnWeeGo[1]](/Portals/0/johnWeeGo.jpg "johnWeeGo[1]")
I always struggle to find the full list of SQL 2008 Spatial methods for Geography when I need them so this is really just a helper post so I can find them again later but maybe it will help you also. Since they are case sensitive and don’t have intelisence I can never remember the syntax.
And from http://sqlspatialtools.codeplex.com
Functions
These static methods, implemented in the class
Functions, can both be registered in SQL Server and used through T-SQL, as well as be used directly from the CLR:
bool IsValidGeographyFromGeometry(SqlGeometry geometry) Check if an input geometry can represent a valid geography without throwing an exception.
This function requires that the geometry be in longitude/latitude coordinates and that
those coordinates are in correct order in the geometry instance (i.e. latitude/longitude
not longitude/latitude). This function will return false (0) if the input geometry is not
in the correct latitude/longitude format, including a valid geography SRID.
bool IsValidGeographyFromText(string inputWKT, int srid) Check if an input WKT can represent a valid geography. This function requires that
the WTK coordinate values are longitude/latitude values, in that order and that a valid
geography SRID value is supplied. This function will not throw an exception even in
edge conditions (i.e. longitude/latitude coordinates are reversed to latitude/longitude).
SqlGeography MakeValidGeographyFromGeometry(SqlGeometry geometry) Convert an input geometry instance to a valid geography instance.
This function requires that the WKT coordinate values are longitude/latitude values,
in that order and that a valid geography SRID value is supplied.
SqlGeography MakeValidGeographyFromText(string inputWKT, int srid) Convert an input WKT to a valid geography instance.
This function requires that the WKT coordinate values are longitude/latitude values,
in that order and that a valid geography SRID value is supplied.
SqlGeography ConvexHullGeography(SqlGeography geography) Computes ConvexHull of input geography and returns a polygon (unless all input points are colinear).
SqlGeography ConvexHullGeographyFromText(string inputWKT, int srid) Computes ConvexHull of input WKT and returns a polygon (unless all input points are colinear).
This function does not require its input to be a valid geography. This function does require
that the WKT coordinate values are longitude/latitude values, in that order and that a valid
geography SRID value is supplied.
SqlGeography DensifyGeography(SqlGeography g, double maxAngle) Returns a
geography instance equivalent to its input, but with no two consecutive points spaced more than
maxAngle apart.
SqlGeography InterpolateBetweenGeog(SqlGeography start, SqlGeography end, double distance) Takes start and end
geography points and returns a new point that is a given distance from the start toward the end.
SqlGeometry InterpolateBetweenGeom(SqlGeometry start, SqlGeometry end, double distance) Takes start and end
geometry points and returns a new point that is a given distance from the start toward the end.
SqlGeography LocateAlongGeog(SqlGeography g, double distance) Takes a
geography linestring and finds the point a given distance along it.
SqlGeometry LocateAlongGeom(SqlGeometry g, double distance) Takes a
geometry linestring and finds the point a given distance along it.
SqlGeometry ShiftGeometry(SqlGeometry g, double xShift, double yShift) Takes a
geometry instance and shifts if by a given X and Y amount.
SqlGeometry VacuousGeographyToGeometry(SqlGeography toConvert, int targetSrid) A special case of the equirectangular projection, taking each point (lat,long) --> (y, x).
SqlGeography VacuousGeometryToGeography(SqlGeometry toConvert, int targetSrid) The inverse of the VacuousGeographyToGeometry projection.
Types
These types can be registered in SQL Server or used directly through the CLR.
SqlProjection This class provides an extensible access point to various projections and inverse projections. See the file
projection_example.sql for a sample of its use. Currently supported projections are:
- Albers Equal Area
- Equirectangular
- Lambert Conformal Conic
- Mercator
- Oblique Mercator
- Tranverse Mercator
- Gnomonic
AffineTransform This provides general affine transformations. See the example
transform_example.sql for a sample of its use.
Aggregates
While implemented as classes, aggregates are essentially functions that take a collection of inputs to a single result.
GeographyUnionAggregate
This aggregate computes the union of a set of geographies.
GeometryEnvelopeAggregate
This aggregate computes the envelope of a set of input geometries.
Route Optimization in Bing Maps powered by OnTerra’s free Stop Optimization Service
Hannes's Virtual Earth Blog -
OnTerra is a Microsoft Partner specialized on tailored Bing Maps solutions and focussing on but not limited to tracking and fleet management. Recently they launched a free beta version of a Stop Optimization Service. The service allows you to send an unlimited list of stops for your route and receive a string with the order of the stops optimized for the shortest driving distance. Let’s have a quick look at how it works:
Bing Maps supports out-of-the-box Multi-Waypoint Routing for up to 25 stops through the method VEMap.GetDirections. However, the routing algorithm processes the stops always in the order in which they appear in the array of locations. If we want to start for example a trip in the Microsoft Office in Reading and want to visit Swindon, Oxford, Maidenhead and Newbury before we return to the Microsoft office we have to know in which order we want to visit these cities. If we just send the list in the order mentioned above it will guide us from one location to the next in exactly this order and come up with a route that is 185 miles long and takes about 3 hours and 20 minutes of drive time.
The free OnTerra Stop Optimization Service figures out in which order we should drive for the shortest distance. In the example above it will suggest that we go to Maidenhead first, then Oxford, Swindon and Newbury. This will save us 45 miles and about 40 minutes of drive time.
That’s not bad at all but if you would like to use it for example as a dispatcher in a fleet management application you also need to consider the times when you can make a pickup or a delivery, you may want to optimize for shortest time rather than shortest distance or you may need to consider height and weight restrictions that apply to your trucks. This is not part of the free service but in addition to the free stop optimization, OnTerra also offers such advanced features for a fee. If you are interested in this type of advanced service contact routeopt@onterrasys.com for more details.
To use the free stop optimization service you will need to register and apply for a token. It requires 3 parameters:
- the locations which we want to optimize as a string, The string contains a label for the location and the latitude and longitude separated by a comma. Multiple locations are separated by a ‘#’.
- a Boolean parameter that indicates weather we do a roundtrip or a one-way trip
- our token
You see that we need to geocode the locations before we send them to the stop optimization service. In the sample application above I use the Bing Maps AJAX control and use the callback function for the VEMap.Find-method to concatenate a string with the locations as expected by the stop optimization service, e.g. “txtStop1,51.461179,-0.925943#txtStop2,51.561765,-1.781815#txtStop3,51.522375,-0.727256#txtStop4,51.405876,-1.325891#txtStop5,51.756205,-1.259490”.
Now here is one thing so consider: The optimization service splits the locations-string whenever it finds the character ‘#’. Unfortunately there appears to be a bug(?) which doesn’t process the string correctly when you work with the full number of decimal digits that comes back from the Bing Maps geocoder. In order to work around this bug(?) we can truncate the number of decimal digits to 6. This does actually not have a noticeable impact on the precision of the result but solves our problem.
Once we have our last location we call a JavaScript-function StopOpt which actually creates an AJAX-call
//Build String for Route Optimization
function AppendLocations(layer, resultsArray, places, hasMore, veErrorMessage) {
i = i + 1;
if (locations.length > 0) {
locations=locations+"#"
}
locations = locations + "txtStop" + i + ","
+ places[0].LatLong.Latitude.toFixed(6) + ","
+ places[0].LatLong.Longitude.toFixed(6);
if (i == numStops) {
StopOpt();
}
}
The AJAX-call goes to a web handler which will call the stop optimization service and hands over the locations-string as well as a parameter that indicates if we’re doing a roundtrip or a one-way-trip. The optimized order of the result is received as a string and we process it a bit before we call the VEMap.GetDirections method.
function StopOpt() {
//Build URL to call the server
var url = "./06-StopOpt.ashx?";
url += "locations=" + locations;
if (document.getElementById("cbRoundtrip").checked == true) {
url += "&roundtrip=true"
}
else {
url += "&roundtrip=false"
}
//Get the appropriate XMLHTTP object for the browser
var xmlhttp = GetXmlHttp();
//if we have a valid XMLHTTP object
if (xmlhttp) {
xmlhttp.open("GET", url, true); // varAsynx = true
//set the callback
xmlhttp.onreadystatechange = function() {
if (xmlhttp.readyState == 4) //4 is a success
{
//web service returns the optimized order of the stops
var result = xmlhttp.responseText
var stopArray = result.split(" >> ");
var stops = new Array();
var order = "Order (Optimized):<br>";
for (var i = 0; i < stopArray.length; ++i) {
order = order + document.getElementById(stopArray[i]).value + "<br>";
stops.push(document.getElementById(stopArray[i]).value);
}
if (document.getElementById("cbRoundtrip").checked == true) {
order = order + document.getElementById("txtStop1").value;
stops.push(document.getElementById("txtStop1").value)
}
var options = new VERouteOptions;
options.RouteCallback = DistTime;
document.getElementById("pOrder").innerHTML = order;
map.GetDirections(stops, options);
}
}
xmlhttp.send(null);
}
}
Finally, here is our web handler that we have been calling with our AJAX-call and which in turn calls the OnTerra stop optimization service:
Imports System.Web
Imports System.Web.Services
Imports BM_Azure_01_WebRole.OnTerra
Public Class _06_StopOpt
Implements System.Web.IHttpHandler
Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
'Get the URL-Parameters
Dim locations As String = context.Request.Params("locations")
Dim roundTrip As Boolean = CBool(context.Request.Params("roundtrip"))
Dim token As String = "YOUR TOKEN"
Dim svcOT As New OnTerra.OnTerraStopOptClient("basicEndPoint")
Dim output As String
output = svcOT.GetStopOpt(locations, roundTrip, token)
context.Response.ContentType = "text/plain"
context.Response.Write(output)
End Sub
ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
Get
Return False
End Get
End Property
End Class
Mapping Roadshow pictures
SoulSolutions -
We were at the Bing Maps roadshow in both Brisbane and Sydney, 10 and 12 June. I’ve finally gotten around to posting a few of the pictures I took from the day in Brisbane. This year the sessions were broken in to customer and partner focus.
First up we had Peter Ulm, the ANZ Region Business Development Manager for Bing Maps for Enterprise, to do a bit of an intro and covered all the licensing details.
Then Chris Pendleton handled the technical stuff. Chris is a Bing Maps Technical Evangelist from the US. He came all the way here to do the roadshow and a session at Remix.
And finally John talked about our experience as a Bing Maps Partner.
Drawing a circle in Bing Maps
SoulSolutions -
I noticed my old code samples around the place are a little outdated so I created this little sample based off the Bing Maps iSDK today. This is a little helper function that calculates 360 points around the location provided at the given radius in KM. The co-ordinates are quite accurate and you will notice the effects of adding a circle at different Latitudes on the Mercator map.
Full source:
DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>Circle Example Bing Mapstitle>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2">script>
<script type="text/javascript">
var map = null;
var pinid = 0;
function GetMap() {
map = new VEMap('myMap');
map.LoadMap();
map.SetZoomLevel(2);
}
function AddPolyline() {
var ll = map.GetCenter();
var shape = new VEShape(VEShapeType.Polyline, getCircle(ll, 100));
shape.SetTitle('My circle');
shape.SetDescription('This is shape number ' + pinid);
pinid++;
map.AddShape(shape);
}
function getCircle(loc, radius) {
var R = 6371; // earth's mean radius in km
var lat = (loc.Latitude * Math.PI) / 180; //rad
var lon = (loc.Longitude * Math.PI) / 180; //rad
var d = parseFloat(radius) / R; // d = angular distance covered on earth's surface
var locs = new Array();
for (x = 0; x <= 360; x++) {
var p2 = new VELatLong(0, 0)
brng = x * Math.PI / 180; //rad
p2.Latitude = Math.asin(Math.sin(lat) * Math.cos(d) + Math.cos(lat) * Math.sin(d) * Math.cos(brng));
p2.Longitude = ((lon + Math.atan2(Math.sin(brng) * Math.sin(d) * Math.cos(lat), Math.cos(d) - Math.sin(lat) * Math.sin(p2.Latitude))) * 180) / Math.PI;
p2.Latitude = (p2.Latitude * 180) / Math.PI;
locs.push(p2);
}
return locs;
}
script>
head>
<body onload="GetMap();">
<div id='myMap' style="position:relative; width:400px; height:400px;">div>
<div><a href='#' onclick='AddPolyline();'>Add Circlea>div>
body>
html>
If you’re after a full toolkit of useful functions for Bing Maps you should check out the awesome: http://vetoolkit.codeplex.com/
Technorati Tags:
Circle,
Bing Maps
Getting your custom imagery onto Bing Maps with Global Mapper and the Cloud
SoulSolutions -
It is actually very easy to display gigabytes of custom imagery on Bing Maps with fantastic results, the trick is to know what tools to use. Read on for a streamlined process for the common geo image formats using Global Mapper, SpaceBlock, Windows Azure and a few helpful tips along the way.
At Bing Map’s core is the ability to render really large images in your browser. Both the AJAX, Silverlight and Mobile versions all use a concept of a tile pyramid to make this possible and effective over the web, you can read more about the tile system in great detail here. Today we are going to look at a process I use to process custom imagery to overlay Bing maps.
In the GIS field these custom images are called raster images and come in various file formats like GeoTiff and ECW. These formats have the images correctly “projected” and include the location co-ordinates. Since the world is not flat in order to view it on a flat surface, like Bing Maps 2D, all the data needs to be “projected” to the Mercator projection. If your custom imagery isn’t already projected and/or not in a Geo image format then the free MapCruncher tool that I will cover in my next post will be more useful for you. Lets assume you have your imagery ready to go in ECW or GeoTiff format.
Global Mapper is a very powerful GIS viewer and data convertor. It supports an amazing number of formats and can export directly to Bing Map tiles. You can open one or many of your ECW or GeoTiff files onto a layer an order as you like. The tool isn’t free but is quite inexpensive and a valuable tool for any Bing Maps developer, join their forums first and look for a discount code before you purchase. I’m going to use a built-in free online data source today so I can show you the results online royalty free and you can follow along.
Step 1. Set your projection.
On the title screen it is the fourth option or choose Tools –> Configure. Under the project tab you will see:
For Bing maps I will choose Mercator and WGS84.
Step 2. Load your data.
Again the welcome screen presents you with 3 options: Open your file, find data online or use a free online source. This is where you would load your GeoTiff or ECW.
For this article I used the free online data from the USGS using a 5 mile bounds from New York.
Step 3. Set a transparency colour and tolerance.
This one is a little tricky to find. Open the control centre (Alt-C) or Tools –> Control Centre. Select your layer and click on Options. Under the Display tab you can select a colour to be transparent and also set small variations of the colour to also be transparent (useful on compressed images).
This is important to get a seamless edge on your custom area but be aware that it could turn areas transparent that you don’t want. Once applied you will see that colour now changed to the default yellow background.
Step 4. Export as Virtual Earth tiles
From the File Menu choose Export Web Formats –> Virtual Earth Tiles.
With every increase in level you create four times the number of tiles then the previous layer. Ideally you should try to match the resolution of the source imagery. Set the number the zoom levels to output, it is easy to turn these off later so I normally export all but the top 4-5. For example here I choose level 16 as my highest since the resolution is pretty poor and 10 levels to export, this mean I will have levels 7->16 in Bing Maps. The export bounds can be set to the whole layer, the current screen or simply draw a box for the area you want.
The export process can take some time depending upon the IO performance of your system, I have global mapper setup on a second computer (actually my media centre PC with fast disks) and remote desktop in from my dev machine.
Step 5. Setup Azure storage
We need to host these tiles on the web, we need a system that is designed to serve many small files. I highly recommend using a cloud based service for this, you can evaluate Windows Azure currently for free. Sign up here, it will take about 2-3 days to get your account. Once you have your account, setup a new storage account. You will need to know your storage account name and shared key for the next step. For a full tutorial on Azure see this awesome post from Katrien.
Step 6. Upload to Azure Blob Storage
I use SpaceBlock to upload and explore Azure Blob Storage, it scales well and allows for multiple accounts.
Under Tools –> Options add your azure account using a key from step 5.
The Azure blob storage can be directly accessed using http, but you need to set the permissions on your container to be public.
The first level of folders in SpaceBlock for Azure are the Azure Containers, from their all the sub folders are just a neat naming trick that SpaceBlock handles for you. You need only set this permission on these top containers.
Step 7. Use in your application.
Now your tiles are public you need to add a tile layer to your application to show them. In the Silverlight CTP control you can do this in your code behind for a Map called “map”:
using System.Windows;
using Microsoft.VirtualEarth.MapControl;
namespace SoulSolutions.TileExample
{
public partial class Page
{
public Page()
{
InitializeComponent();
Loaded += Page_Loaded;
}
private void Page_Loaded(object sender, RoutedEventArgs e)
{
var tileLayer = new MapTileLayer();
map.Children.Add(tileLayer);
//add the overlay
var tileSource = new LocationRectTileSource(
"http://brisbane.blob.core.windows.net/tiles/nyDEM/{0}.png",
new LocationRect(new Location(40.76026, -74.08459), new Location(40.6874, -73.94211)),
new Range<double>(7, 16));
tileLayer.TileSources.Add(tileSource);
map.SetView(new Location(40.71499, -74.00734), 14);
}
}
}
See live example here.
In the AJAX control you do this (complete page):
DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>SoulSolutions Tile Example AJAXtitle>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2">script>
<script type="text/javascript">
var map = null;
var tileLayer;
function GetMap()
{
map = new VEMap('myMap');
map.LoadMap(new VELatLong(40.71499, -74.00734), 14, 'a', false);
GetTiles();
}
function GetTiles()
{
var bounds = [new VELatLongRectangle(new VELatLong(40.76026, -74.08459), new VELatLong(40.6874, -73.94211))];
var tileSourceSpec = new VETileSourceSpecification("lidar", "http://brisbane.blob.core.windows.net/tiles/nyDEM/%4.png");
tileSourceSpec.NumServers = 1;
tileSourceSpec.Bounds = bounds;
tileSourceSpec.MinZoomLevel = 7;
tileSourceSpec.MaxZoomLevel = 16;
tileSourceSpec.Opacity = 0.8;
tileSourceSpec.ZIndex = 100;
map.AddTileLayer(tileSourceSpec, true);
}
script>
<style type="text/css">
html, body {
height: 100%;
overflow: auto;
}
body {
padding: 0;
margin: 0;
}
style>
head>
<body onload="GetMap();">
<div id='myMap' style="position:relative; width:100%; height:100%;">div>
body>
html>
See live example here.
And of coarse you can change to 3D mode and get this awesome effect:
In both examples you have to specify:
- URL to the tiles, with parameter to be replaced with quadkey filename
- The bounds of the tile layer
- The valid zoom levels
Thankfully GlobalMapper produces a .htm file in its output path that will specify these settings for you. However I find it is a little generous and I then use my location chooser to trim this down a little.
I hope you enjoyed this article and feel inspired to tile out your own custom imagery and overlay on Bing Maps to enhance your application.
Imagine Cup 2009 – Team Croatia Builds GeoScout with Bing Maps
Virtual Earth, a developer blog -
The Imagine Cup 2009 finals is in full swing. On July 3, 2009 in Cairo the winner will be announced for the most innovative project. The Imagine Cup is the world’s premier student technology competition, Imagine Cup is one way Microsoft is encouraging young people to apply their imagination, their passion, and their creativity to technology innovations that can make a difference in the world – today. Now in its seventh year, the Imagine Cup has grown to be a global event. In 2008, more than 200,000 students from 100 countries entered the Imagine Cup competition.
The Croatian Team consisting of Tomislav Fotak, Ivan Lozić, Marko Škvorc, Ivan Švogor integrated Deep Earth (which uses Bing Maps) into their GeoScout.net project. Check out the videos of GeoScout to better understand how they’re leveraging Bing Maps.
GeoScout.Net is a platform which main purpose is emphasizing information flows, bringing experts, organizations, societies and people together. The fundamental assumption of this platform began with e-Governance. To visualize it, you can picture a pyramid. The idea that guides us is to use strong basis, and very general system, which components can later be developed used for specific and specialized purpose. Therefore we developed an infrastructure that is very easily expanded. Integrating a new set of functionalities is a few clicks away. Infrastructure like this can allow any developer to build in independent modules, specialized for solving specific millennium goals. Our experts have advised to build a module that can implicitly help to solve multiple millennium goals, i.e. child and maternal health, stop spreading infectious diseases, and keep a sustainable environment as well as world partnership. GeoScout.Net brings together organizations with same missions, trough an incident tracking module. It is no wonder that you can read in newspaper the entire villages were wiped out by some disease, natural disaster or the flora and fauna are threatened by environment pollution. GeoScout.Net allows organizations to select some endangered areas, and publish this information to all other users. Now, field experts and teams can spend less time organizing and reporting. This time allows them better work and greater service quality for those in needs. System can track the resources and needs of mobilized teams. Organization headquarters users can efficiently track the mission progress, and estimate when more help is needed. Also, when reporting a danger zone, system allows users to estimate risk, and help in supply assessment. When some historic data is needed, it is very simple to travel through it, since it is visualized on the map. Entire system is based on high level of visualization provided by Virtual Earth, and Silverlight web application. GeoScout.Net includes a mobile application which is optimized for field work. I t provides an interface for easy reporting, and browsing data in areas with poor ICT infrastructure. GeoScout.Net can also be used in different purposes; adding new modules application can explore whole new possibilities.
Go Team Croatia!
CP
TrekWireless Shows Electric Car Charging Points on Bing Maps
Hannes's Virtual Earth Blog -
Following the announcement of the a UK-wide trial of low carbon and electric cars TrekWireless has created a map of electric car charging points (juice points) in Westminster. The infobox shows the location of electric car charging points in 360 degree POSIPIX images and through their SMS service a mobile user can request a geo-tagged image be sent to his navigation device.
Spatial-Enabled Windows Azure (Part 2)
Hannes's Virtual Earth Blog -
Step 4: Build the basic Bing Maps Application with the Tile Layer
We start by creating a new Cloud Service Solution. A Web Cloud Service will do for this purpose.
For our development and debugging we will use the development fabric but we will not use the development storage (I actually didn’t manage to get the development table storage to work with binary data types). Hence we can disable the start of development storage services in the properties of our Azure project.
Next we add a new Silverlight application to our WebRole-Project:
Let’s also create a test page and make sure that Silverlight debugging is enabled:
To this project we add a our Bing Maps Silverlight Control as additional reference. The DLL is not in the Global Assembly Cache so you need to browse for it. If you installed the Bing Maps Silverlight Control in the default location you’ll find the DLL in the folder “C:\Program Files\Microsoft Virtual Earth Silverlight Map Control\CTP\Libraries”.
In the Page.xaml we add now the reference to our map control:
<UserControl x:Class="_01_SL_Charts.Page"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:m="clr-namespace:Microsoft.VirtualEarth.MapControl;assembly=Microsoft.VirtualEarth.MapControl" >
…the map itself…
<m:Map HorizontalAlignment="Stretch" VerticalAlignment="Stretch" x:Name="MyMap"
Center="0, 0"
ZoomLevel="2"
Mode="Road" />
….and other design components as you need it such as checkboxes that allow us to switch the tile layers on and off.
<StackPanel>
<CheckBox x:Name="cbGDP" Click="cbGDP_Click" >
<TextBlock Text="GDP"></TextBlock>
</CheckBox>
<CheckBox x:Name="cbCapita" Click="cbCapita_Click" >
<TextBlock Text="GDP per Capita"></TextBlock>
</CheckBox>
</StackPanel>
At the end of our user control we can also import other user controls that we may want to use as pop-ups. In this example we use user-controls as pop-ups for example to explain the colour codes. Let’s assume we have already created 2 new Silverlight user controls in our Silverlight project. Both of them have show- and close-functions in the code behind. We can import them now for use in our Page.xaml:
<mychart:LegendGDP x:Name="legendGDPPopup" Visibility="Collapsed" />
<mychart:LegendCapita x:Name="legendCapitaPopup" Visibility="Collapsed" />
In my sample I have also added a mini map and buttons to toggle this mini map as well as one to toggle fullscreen mode. You will find the complete code in the sample code at the end of this blog post.
In the extract of the XAML above you see that we define the initial centre-point, zoom-level and map-style directly in the XAML. You also see that we have prepared for 2 functions that will fire when we check or uncheck the checkboxes. So let’s have a look at the code behind in the Page.xaml.vb-file. You will see that we have attached a handler that captures when the target-view of our map changes. That actually happens whenever we pan or zoom the map. when this event occurs we check the target zoom-level and since we rendered the tile layer only for levels 1-7 we make sure that we can’t zoom any closer than that. Next we create functions that overlay our own custom tile layer when we check the checkbox and hides them again when we uncheck it. We also show and hide the legend as part of these functions.
Imports Microsoft.VirtualEarth.MapControl
Partial Public Class Page
Inherits UserControl
'Tile Layer
Public gdpLayer As MapTileLayer
Public capitaLayer As MapTileLayer
Public Sub New()
InitializeComponent()
' Set up Main Map Events
AddHandler MyMap.TargetViewChanged, AddressOf MyMap_TargetViewChanged
End Sub
Private Sub MyMap_TargetViewChanged(ByVal sender As Object, ByVal e As Microsoft.VirtualEarth.MapControl.MapEventArgs)
If MyMap.TargetView.ZoomLevel > 7 Then
MyMap.SetView(MyMap.Center, 7)
End If
End Sub
Private Sub cbGDP_Click(ByVal sender AsSystem.Object, ByVal e As System.Windows.RoutedEventArgs)
If cbGDP.IsChecked = True Then
' The bounding rectangle that the tile overlay can be placed within.
Dim boundingRect As New LocationRect(New Location(90, -180), New Location(-90, 180))
' Creates a new map layer to add the tile overlay to.
gdpLayer = New MapTileLayer()
' The source of the overlay.
Dim tileSource As New LocationRectTileSource()
tileSource.UriFormat = "http://hannesvestorage.blob.core.windows.net/vetiles/GDP/{0}.png"
' The zoom range that the tile overlay is visibile within
tileSource.ZoomRange = New Range(Of Double)(1, 7)
' The bounding rectangle area that the tile overaly is valid in.
tileSource.BoundingRectangle = boundingRect
gdpLayer.TileSources.Add(tileSource)
gdpLayer.Opacity = 0.7
MyMap.Children.Add(gdpLayer)
legendGDPPopup.Show()
Else
MyMap.Children.Remove(gdpLayer)
legendGDPPopup.Close()
End If
End Sub
End Class
Well that’s it for the tile layer. At this point we can run the project and see our statistical information as a thematic Bing Map using the Silverlight control.
Step 5: Implement spatial queries from our Bing Maps application to the Windows Azure Table Storage
First we add a few references to this WebRole project: We need the same StorageClient.dll we used in Step 3 as well as the System.Data.Services.Client from the GAC to access the Windows Azure Table Storage but we also need to Microsoft.SqlServer.Types for the spatial queries. You will have the latter already in your GAC if you have SQL Server 2008 installed otherwise download and install either SQL Server 2008 or the SQL Server CLR Types from the Feature Pack. Make sure both the StorageClient and the SqlServer.Types are copied locally so that they will be packaged and uploaded to Windows Azure.
Now here comes the first tricky bit. The spatial libraries in SQL Server consists actually of an managed and an unmanaged part. We need both of them but with the approach mentioned above we only get the managed piece. Since the other part is unmanaged we could simply copy the SqlServerSpatial.dll from C:\Windows\System32 to the bin-directory of our WebRole, e.g. C:\Users\jkebeck\Documents\Visual Studio 2008\Projects\BM-Azure-01\BM-Azure-01_WebRole\bin.
However keep in mind that Windows Azure runs on 64-bit processors. If your system is like my laptop on 32-bit you need to download the 64-bit version of the SQL Server CLR Types from the feature pack extract the *.msi using a command such as
msiexec /a SQLSysClrTypes_64bit.msi /qb TARGETDIR="C:\MyFolder"
and copy the SqlServerSpatial.dll from there.
Well, by default Windows Azure does not allow you to run native code but since the latest update you can now enable this feature. In order to do so open the ServiceDefinition.csdef in the Azure project and set the enableNativeCodeExecution to true.
Next we need to consider that at least during the development we do cross-domain calls from our Silverlight application in the local development fabric to the Windows Azure Table Storage. Hence we’ll need a crossdomain.xml file. So let’s just create a new xml-file with that name and enter the following:
<access-policy>
<cross-domain-access>
<policy>
<allow-from http-request-headers="*">
>
<domain uri="*"/>
</allow-from>
<grant-to>
<resource path="/" include-subpaths="true"/>
</grant-to>
</policy>
</cross-domain-access>
</access-policy>
Now we add the same class GDP.vb that we created in Step 3 as an existing item to our web role. Remember, we need this class to describe the table and entities in our Windows Azure Table Storage.
In our web.config we add next the section for our Windows Azure credentials
<appSettings>
<add key="AccountName" value="YOUR ACCOUNT NAME" />
<add key="AccountSharedKey" value="YOUR SHARED KEY" />
<add key="TableStorageEndpoint" value="http://table.core.windows.net" />
</appSettings>
Finally we get to look at some code again. To connect to the Windows Azure Table Storage from our Silverlight application we create a new Silverlight-enabled WCF Service in our WebRole-project.
In this WCF service we will receive the latitude and longitude of the location we clicked on as input parameters and then construct a geometry of type POINT from it. Now we loop through all the records in our Windows Azure table, retrieve the byte array describing our geometry and convert it into a GEOMETRY data type. Once we have this we can determine if the point that we clicked on is in this particular geometry. If so, we return the entity to the function that called the service.
Imports System.ServiceModel
Imports System.ServiceModel.Activation
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Types
<ServiceContract(Namespace:="DataService")> _
<AspNetCompatibilityRequirements(RequirementsMode:=AspNetCompatibilityRequirementsMode.Allowed)> _
Public Class DataService
<OperationContract()> _
Public Function GetCountry(ByVal myLat As String, ByVal myLon As String) As GDPRecord
'set culture to en-UK to avoid potential problems with decimal-separators
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-UK")
'Build geometry
Dim myWKT As New SqlChars(New SqlString("POINT(" + myLon + " " + myLat + ")"))
Dim myPoint As SqlGeometry
myPoint = SqlGeometry.STGeomFromText(myWKT, 4326)
'Query Azure table and compare geometries
Dim myTable As New GDP
For Each GDPRecord In myTable.GDPTable
Dim b As Byte() = GDPRecord.Geom
Dim g As SqlGeometry
g = SqlGeometry.STGeomFromWKB(New SqlBytes(b), 4326)
If g.STContains(myPoint) = 1 Then
Return GDPRecord
Exit For
End If
Next
End Function
End Class
Once we have this service we can build the WebRole-project and add a Service Reference to our Silverlight project.
This will not only create the reference but also a ServiceReferences.ClientConfig file. In this file you have to remove the tags for the transport-mode within the security tags so that the file reads similar to:
<configuration>
<system.serviceModel>
<bindings>
<basicHttpBinding>
<binding name="BasicHttpBinding_DataService" maxBufferSize="2147483647"
maxReceivedMessageSize="2147483647">
<security mode="None"/>
</binding>
</basicHttpBinding>
</bindings>
<client>
<endpoint address="http://dummy/DataService.svc" binding="basicHttpBinding"
bindingConfiguration="BasicHttpBinding_DataService" contract="DataServiceReference.DataService"
name="BasicHttpBinding_DataService" />
</client>
</system.serviceModel>
</configuration>
The address of the endpoint doesn’t actually matter. It will be different in the development fabric, different in the Windows Azure staging environment and different again in the Windows Azure production environment. Hence we are going to write the URL later in our code.
Step 6: Add a chart using the Microsoft Silverlight Toolkit
First we need to add the assembly as reference to our Silverlight project. If you installed the Silverlight Toolkit in the default directory you’ll find it in the path “C:\Program Files\Microsoft SDKs\Silverlight\v2.0\Toolkit\March 2009\Libraries\System.Windows.Controls.DataVisualization.Toolkit.dll”. Again: make sure that you copy this assembly locally.
Now we create a new Silverlight user control Chart.xaml in our Silverlight project and we will find the Chart in your Visual Studio Toolbox
![image_thumb[1]](https://psijhg.blu.livefilestore.com/y1m46bJrEhX1JcmyALiDoEd2FVD7bHfVHNC8kIqpc5KLRaGK71_7PeXZpJHqBghzjxlXnb147TLZ2Zt5vLbaCHBsyhLQcb5SArfEHUubfcP6qvItwAAtAK_tJZqgfvRu7DTZ7feBE5Q5LSWnVkqAfOlWQ/image_thumb[1][3].png "image_thumb[1]")
Let’s prepare the user control to be used as a popup from our main user control Page.xaml and add a couple of more controls to host the details:
<Grid x:Name="LayoutRoot" Width="245" >
<Popup x:Name="popChart" VerticalAlignment="Stretch" Width="245" Margin="0,0,0,0" HorizontalAlignment="Stretch">
<Border Width="245" BorderThickness="3,3,3,3" CornerRadius="10,10,10,10"
BorderBrush="#FFFFFFFF" Background="#FFFFFFFF">
<StackPanel Orientation="Vertical" Margin="10,10,10,10" Canvas.ZIndex="0" Width="220">
<TextBlock x:Name="myName" Margin="5,0,0,0" ></TextBlock>
<StackPanel Orientation="Horizontal" Margin="5,0,0,0">
<TextBlock Text="GDP (Mio USD): "></TextBlock>
<TextBlock x:Name="myTotal" VerticalAlignment="Top"></TextBlock>
</StackPanel>
<StackPanel Orientation="Horizontal" Margin="5,0,0,0">
<TextBlock Text="GDP/Capita (USD): "></TextBlock>
<TextBlock x:Name="myCapita" VerticalAlignment="Top"></TextBlock>
</StackPanel>
<StackPanel Orientation="Horizontal" Margin="5,0,0,0">
<TextBlock Text="Growth Rate (%): "></TextBlock>
<TextBlock x:Name="myGrowth" VerticalAlignment="Top"></TextBlock>
</StackPanel>
<chart:Chart Height="310" Title="Percentage Added By" x:Name="MyPieChart" Width="220">
<chart:Chart.Background>
<LinearGradientBrush EndPoint="0.5,1" StartPoint="0.5,0">
<GradientStop Color="#FFFCAC34"/>
<GradientStop Color="#FFFFFFFF" Offset="1"/>
</LinearGradientBrush>
</chart:Chart.Background>
<chart:PieSeries
IndependentValueBinding="{Binding Path=myName}"
DependentValueBinding="{Binding Path=myValue}"
LegendItemStyle="{StaticResource MyLegendItemStyle1}"
StylePalette="{StaticResource MyStylePalette}"/>
</chart:Chart>
<TextBlock Margin="5,0,0,0" VerticalAlignment="Top" Text="Note: if the chart area is empty…"/>
</StackPanel>
</Border>
</Popup>
</Grid>
If you want to make some major changes in the style of the control, modify the tooltip, etc it is a bit painful but then the beauty of this type of control is that you aren’t being boxed and can do it after all. A good guide on advanced styling for the chart control of the Silverlight Toolkit using Expression Blend is here.
In the code behind we prepare functions to open and close the popup. In the opening function we want to be able to receive a couple of parameters and use them to display various information and add data points to the chart.
Imports System.Windows.Controls.DataVisualization.Charting
Imports System.Globalization
Partial Public Class Chart
Inherits UserControl
Public Sub New()
InitializeComponent()
End Sub
Public Sub Close()
popChart.IsOpen = False
Me.Visibility = Windows.Visibility.Collapsed
End Sub
Public Sub Show(ByVal country As String, ByVal total As Double, ByVal capita As Double, ByVal growth As Double, ByVal agr As Double, ByVal ind As Double, ByVal man As Double, ByVal ser As Double)
myName.Text = country
myTotal.Text = CDbl(total).ToString("N1", CultureInfo.InvariantCulture)
myCapita.Text = CDbl(capita).ToString("N1", CultureInfo.InvariantCulture)
myGrowth.Text = CDbl(growth).ToString("N1", CultureInfo.InvariantCulture)
Dim ps As PieSeries = MyPieChart.Series(0)
Dim myData As New List(Of myDataClass)
myData.Add(New myDataClass("Agriculture", agr))
myData.Add(New myDataClass("Industry", ind))
myData.Add(New myDataClass("Manufacturing", man))
myData.Add(New myDataClass("Service", ser))
ps.ItemsSource = myData
popChart.IsOpen = True
Me.Visibility = Windows.Visibility.Visible
End Sub
End Class
Public Class myDataClass
Private _myName As String
Public Property myName() As String
Get
Return _myName
End Get
Set(ByVal value As String)
_myName = value
End Set
End Property
Private _myValue As Integer
Public Property myValue() As Integer
Get
Return _myValue
End Get
Set(ByVal value As Integer)
_myValue = value
End Set
End Property
Public Sub New(ByVal _myName As String, ByVal _myValue As Integer)
myName = _myName
myValue = _myValue
End Sub
End Class
All right, now let’s string it together. In our Page.xaml we reference this new popup for the chart
…
<mychart:Chart x:Name="chartPopup" Visibility="Collapsed" />
<mychart:LegendGDP x:Name="legendGDPPopup" Visibility="Collapsed" />
<mychart:LegendCapita x:Name="legendCapitaPopup" Visibility="Collapsed" />
<mychart:PleaseWait x:Name="waitPopup" Visibility="Collapsed"/>
</Grid>
</UserControl>
In the code behind, i.e. Page.xaml.vb we want to introduce a feature that allows us to double click on a location in the map and then:
- add a point to the map that marks the clicked location
- determine the location we clicked on and call the web service we created in step 5
- The response will be used as parameters when we open the chart and hand over the details
First we declare a new MapLayer in our class
'Chart Layer
Public chartLayer As MapLayer
In the Public Sub New we add a new handler that takes care of a double-click
AddHandler MyMap.MouseDoubleClick, AddressOf MyMap_MouseDoubleClick
The handler will first add the layer to the map if it doesn’t already exist and then add an icon on the clicked position. Then we dynamically build the URL of the endpoint for our web service and call it asynchronously with the latitude and longitude of the clicked location as parameters.
Private Sub MyMap_MouseDoubleClick(ByVal sender As Object, ByVal e As MapMouseEventArgs)
'Add Layer for chart points
If Not MyMap.Children.Contains(chartLayer) Then
chartLayer = New MapLayer
MyMap.Children.Add(chartLayer)
End If
chartLayer.Children.Clear()
chartPopup.Close()
Dim loc As Location = MyMap.ViewportPointToLocation(e.ViewportPoint)
Dim image As New Image()
image.Source = New BitmapImage(New Uri("/IMG/blue.png", UriKind.Relative))
image.Stretch = Stretch.None
Dim location As New Location(loc.Latitude.ToString, loc.Longitude.ToString)
Dim position As PositionMethod = PositionMethod.Center
chartLayer.AddChild(image, location, position)
Dim wsURL As String = "http://" + HtmlPage.Document.DocumentUri.Host + _
":" + HtmlPage.Document.DocumentUri.Port.ToString + "/DataService.svc"
Dim svc As New DataServiceClient()
svc.Endpoint.Address = New ServiceModel.EndpointAddress(wsURL)
AddHandler svc.GetCountryCompleted, AddressOf svc_GetCountryCompleted
svc.GetCountryAsync(loc.Latitude.ToString, loc.Longitude.ToString)
e.Handled = True
End Sub
When we receive the response we hand the details over to the Chart user control and open it.
Private Sub svc_GetCountryCompleted(ByVal sender As Object, ByVal e As GetCountryCompletedEventArgs)
chartPopup.Show(e.Result.Name, e.Result.Total, e.Result.Capita, _
e.Result.Growth, e.Result.Agri, e.Result.Ind, e.Result.Manu, e.Result.Serv)
End Sub
And finally we’re done. We can publish our work to Windows Azure from the context menu of the Azure project.
![image_thumb[5]](https://psijhg.blu.livefilestore.com/y1muiPmrTT5euNawjYTF_Ax7EKcv0yNelJs9t_3WMBjFPzKHO1wQ3iZ1O06jWenSbaT40NGwEikxRiApECRxrY21QKnyI-bsPwB_NI2LxEsD0crfnsuuiySVvO_Ssw-OuSuHbQtmvOX4lRQq9ZeEMN-Ow/image_thumb[5][3].png "image_thumb[5]")
This is how it looks like
![image_thumb[3]](https://psijhg.blu.livefilestore.com/y1mj65cOwZbxxvgSO0GqaC7lRR-m9GmqUAEXPd_Gak1FOa1DOk_Xq0H5e-rnnCyiAibtDjxa8InPfg2K7ZgEKNrWv9zhpFWAUpDZtSfgZ01gmVJxFgqTIfEq1DsqJS3dxFCY0byHsA_1huAnSmXV16WFQ/image_thumb[3][3].png "image_thumb[3]")
You will find the sample live on Windows Azure here and the source code is here
(the source code has actually a couple of more samples from this site)
Spatial-Enabled Windows Azure (Part 1)
Hannes's Virtual Earth Blog -
Introduction
I have previously blogged about Bing Maps and Windows Azure (Part 1: Introduction, Part 2: Accessing Blob Storage, Part 3: Accessing Table Storage) and since we brought together a mapping application with our operating system for the cloud this is already sort of spatial-enabling but now I want to go a step further. Now I would also like to use spatial data types and spatial functions as we have them in SQL Server 2008. That may sound a bit ambitious but in their infinite wisdom the SQL Server Spatial team has made the spatial data types and spatial functions available for external use in a separate library that comes with SQL Server 2008 but also separately with the SQL Server 2008 Feature Pack. To be more precise you find them in the package “SQL Server System CLR Types”. Well, that’s almost all I need and with a little tweaking I can use this library in a way that I can leverage the spatial data types and spatial functions within Windows Azure.
For this walk-through I’m going to keep it simple. I will store a couple of country-boundaries in Well Known Binary (WKB) format together with business data in the Windows Azure Table Storage. The application will allow me to click on a a country in Bing Maps and retrieve the detailed information for the selected country similar to my previous blog post Data Visualization with Bing Maps. You might wonder why I don’t just you use the reverse-geocoder in Bing Maps or MapPoint Web Service to determine the country that contains the location I clicked on. Indeed you have a valid point. However, it is not very simple to retrieve the country through the reverse-geocoder in Bing Maps. In MapPoint Web Service it is much more straight forward since you can filter the response from the SOAP web service and get only the entities of type “Sovereign” which contain the country-name. From there I could use a simple WHERE-clause to look up the business data. Unfortunately it is not always that simple. In this example I use several data sets around the Gross Domestic Product and the above mentioned approach works well for countries like Germany but if we look for example at France I want to be able to distinguish between mainland France and its overseas dependencies. In that case it will be much simpler to use a spatial query and determine the geography that contains the location.
After all a spatial-enabled Windows Azure will allow me to use the same approach not only on a country level but basically for any geography you can think off (e.g. super output areas, etc) and more important I cannot only use it for simple queries like “in which area is this point” but also for “find points of interest along a route” or “find hotels within 2 miles of Hadrian’s Wall”. Even territory management type queries where I want to aggregate geographies for example into sales territories are possible then.
In the previous blog post on Data Visualization with Bing Maps we used the Bing Maps AJAX Control and the Microsoft Chart Control. Unfortunately the chart control doesn’t work on Azure yet. This is a known issue and a fix is on the way but there is no ETA yet. So I chose to use the charts in the Microsoft Silverlight Toolkit and because I’m already at Silverlight I’m also using the Bing Maps Silverlight Control. The complete list of tools I used is:
We will use the same statistical information around the Gross Domestic Product (GDP) from the GEO Data Portal of the Unites Nations Environment Programme (UNEP) as in the previous blog post and go through the following steps
- Create a Bing Maps Tile Layer for the colour coding of the countries using Safe FME and upload it to the Windows Azure Blog Storage
- Load vector data into SQL Server 2008 using Safe FME
- Migrate the spatial data from our local SQL Server 2008 to the Windows Azure Table Storage
- Build our basic Bing Maps application which overlays the Tile Layer
- Implement spatial queries from our Bing Maps application to the Windows Azure Table Storage
- Add a chart using the Microsoft Silverlight Toolkit
As usual you will find the sample code at the end of this blog for download.
Step 1: Create the Bing Maps Tile Layer and Upload to Windows Azure
Since I already explained the generation of the tile layer using Safe FME in the previous blog post we can keep this short and go straight to the upload into the Windows Azure Blog Storage. I use Spaceblock for this which is available for free download from Codeplex.
Step 2: Load Vector Data into SQL Server 2008
So far we have created a tile layer – basically a set of images – that we can overlay on Bing Maps. This will allow us to create a quite visual colour-coded map but obviously we will loose the meta data and the granularity of the information will depend on the number of colours we use. For example Germany, France, Italy and the UK are all mapped to the the same colour. In our example we want to be able to click on a country and retrieve the detailed information. To do that we will use the original vector data and spatial relationship queries as provided by the spatial functions in SQL Server 2008. Since we will ultimately not deploy the data on SQL Server 2008 but on Windows Azure we will have a couple of constraints. One is that SQL Server 2008 also provides spatial indexing and unfortunately we can’t use that in Windows Azure. More important though is that the Windows Azure Table Storage doesn’t support the SQL Server 2008 spatial data types natively so we have to work around it using the binary data type and that one only supports an array of bytes with a size of up to 64 kB.
Well, the bad news is that a geometry for a country like Canada is much bigger than that but fortunately we can use Safe FME to generalize the geometries. We could actually do something similar with the Reduce-method in SQL Server 2008 as well but FME supports more algorithms and - most important - preserves shared boundaries between countries. Below you find the FME workflow…
…and the settings for the Generalizer I chose:
We do the same loading procedure for all data sets that we have downloaded previously.
When you query the data in SQL Server 2008 using a spatial function such as…
select geom.STArea() from GDP_Capita;
…you will probably get an error message because the generalized data set has self-intersections which lead to invalid geometries.
To validate the data execute the following SQL-statement:
update GDP set GEOM=GEOM.MakeValid();
Finally let’s create a view which joins all the statistical information and the spatial data:
CREATE VIEW V_GDP
AS
SELECT t1.NAME,
t1.Y_2005 AS GDP,
t2.Y_2005 AS GDP_Capita,
t3.Y_2005 AS GDP_Growth_Rate,
t4.Y_2005 AS GDP_Agri_Add,
t5.Y_2005 AS GDP_Ind_Add,
t6.Y_2005 AS GDP_Manu_Add,
t7.Y_2005 AS GDP_Service_Add,
t8.Y_2005 AS GDP_Trade_Add,
t1.GEOM
FROM GDP AS t1 INNER JOIN
GDP_Capita AS t2 ON t1.ID = t2.ID INNER JOIN
GDP_Growth_Rate AS t3 ON t1.ID = t3.ID INNER JOIN
GDP_Agri_Add AS t4 ON t1.ID = t4.ID INNER JOIN
GDP_Ind_Add AS t5 ON t1.ID = t5.ID INNER JOIN
GDP_Manu_Add AS t6 ON t1.ID = t6.ID INNER JOIN
GDP_Service_Add AS t7 ON t1.ID = t7.ID INNER JOIN
GDP_Trade_Add AS t8 ON t1.ID = t8.ID
ORDER BY t1.NAME
Step 3: Migrate Data from SQL Server 2008 to Windows Azure
For this step we create a small WinForm-application that reads data from our SQL Server and inserts the records in a Windows Azure table. In order to access the Windows Azure Storage we use the StorageClient Library from the Windows Azure Samples. After we installed the Windows Azure SDK we will find these samples in the folder C:\Program Files\Windows Azure SDK\v1.0. So let’s compile the samples as described in the readme.txt, add the StorageClient.dll as reference to our project and double-check in the properties that “copy local” is set to true:
If we want to use the StorageClient.dll we need to define table objects and entities in a class. Hence we create a new class GDP.vb to define the entities. We will use the same class later in our web application. Note that we define the property that will hold our spatial data in Well Known Binary (WKB) format as byte array.
Imports Microsoft.Samples.ServiceHosting.StorageClient
Imports System.Data.Services.Client
Public Class GDPRecord
Inherits TableStorageEntity
Private _Name As String
Public Property Name() As String
Get
Return _Name
End Get
Set(ByVal value As String)
_Name = value
End Set
End Property
Private _Total As Double
Public Property Total() As Double
Get
Return _Total
End Get
Set(ByVal value As Double)
_Total = value
End Set
End Property
Private _Capita As Double
Public Property Capita() As Double
Get
Return _Capita
End Get
Set(ByVal value As Double)
_Capita = value
End Set
End Property
Private _Growth As Double
Public Property Growth() As Double
Get
Return _Growth
End Get
Set(ByVal value As Double)
_Growth = value
End Set
End Property
Private _Agri As Double
Public Property Agri() As Double
Get
Return _Agri
End Get
Set(ByVal value As Double)
_Agri = value
End Set
End Property
Private _Ind As Double
Public Property Ind() As Double
Get
Return _Ind
End Get
Set(ByVal value As Double)
_Ind = value
End Set
End Property
Private _Manu As Double
Public Property Manu() As Double
Get
Return _Manu
End Get
Set(ByVal value As Double)
_Manu = value
End Set
End Property
Private _Serv As Double
Public Property Serv() As Double
Get
Return _Serv
End Get
Set(ByVal value As Double)
_Serv = value
End Set
End Property
Private _Geom As Byte()
Public Property Geom() As Byte()
Get
Return _Geom
End Get
Set(ByVal value As Byte())
_Geom = value
End Set
End Property
Public Sub New(ByVal _Name As String, ByVal _Total As Double, ByVal _Capita As Double, _
ByVal _Growth As Double, ByVal _Agri As Double, ByVal _Ind As Double, _
ByVal _Manu As Double, ByVal _Serv As Double, ByVal _Geom As Byte())
MyBase.New("Country", String.Format("{0:d10}", DateTime.UtcNow.Ticks))
Name = _Name
Total = _Total
Capita = _Capita
Growth = _Growth
Agri = _Agri
Ind = _Ind
Manu = _Manu
Serv = _Serv
Geom = _Geom
End Sub
Public Sub New()
End Sub
End Class
Public Class GDP
Inherits TableStorageDataServiceContext
Public Sub New()
MyBase.New(StorageAccountInfo.GetDefaultTableStorageAccountFromConfiguration())
End Sub
Public ReadOnly Property GDPTable() As DataServiceQuery(Of GDPRecord)
Get
Return CreateQuery(Of GDPRecord)("GDPTable")
End Get
End Property
End Class
Next we create a app.config that will hold our credentials for the Windows Azure Storage
<appSettings>
<add key="AccountName" value="Your Account Name"/>
<add key="AccountSharedKey" value="Your Shared Key”
<add key="TableStorageEndpoint" value="http://table.core.windows.net"/>
</appSettings>
In our WinForm we create just 1 button. When we load the form we try to create a new table GDP in our Windows Azure Table Storage. If this table already exists the command will do nothing. When we click the button we will read through our database view, retrieve the alphanumeric data in their normal format and the spatial data as Well Known Binary (WKB) and add each record to our Windows Azure Table
Private Sub btnStartTransfer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnStartTransfer.Click
BackgroundWorker1.RunWorkerAsync()
End Sub
Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) _
Handles BackgroundWorker1.DoWork
Dim myConn As New SqlConnection("Data Source=jkebeck1; Initial Catalog=Statistics; Integrated Security=SSPI;")
Dim myQ1 As String = "SELECT COUNT(*) FROM V_GDP"
Dim myC1 As New SqlCommand(myQ1, myConn)
Dim numRecords As Integer = 0
myConn.Open()
numRecords = myC1.ExecuteScalar()
Dim i As Integer = 0
Dim myQ2 As String = "SELECT NAME, GDP, GDP_Capita, GDP_Growth_Rate, GDP_Agri_Add, GDP_Ind_Add, GDP_Manu_Add," + _
"GDP_Service_Add, GEOM.STAsBinary() FROM V_GDP"
Dim myC2 As New SqlCommand(myQ2, myConn)
Dim myReader As SqlDataReader = myC2.ExecuteReader()
While myReader.Read
Dim svc = New GDP()
svc.AddObject("GDPTable", New GDPRecord(myReader(0), myReader(1), myReader(2), myReader(3), myReader(4), _
myReader(5), myReader(6), myReader(7), myReader(8)))
svc.SaveChanges()
i = i + 1
lblStatus.Text = "Transfer Record " + i.ToString + " of " + numRecords.ToString
End While
myReader.Close()
myConn.Close()
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim tables = TableStorage.Create(StorageAccountInfo.GetDefaultTableStorageAccountFromConfiguration())
tables.TryCreateTable("GDPTable")
End Sub
The source code for this little tool is available here
Once we completed the upload we may want to use a tool such as the Azure Storage Explorer to verify everything went well.
Rendering Historic Maps on Bing
Virtual Earth, a developer blog -
One feature of the Bing Maps APIs that has gained a lot of traction is the ability to overlay custom tiles atop the Bing Maps tiles to illustrate tons of data in a raster format. The ability to draw data on the map using vector graphics is all fine and dandy, but when you want to render census data, weather illustrations or even historic maps of what an area used to look like, well, browser-supported drawing techniques would pretty much crap out. So, what you’ll do is render that information prior to putting in on the map (using the Tile Pyramid Schema or Map Cruncher) and overlay it onto the map (using map.AddTileLayer() in AJAX; or, MapTileLayer.TileSource.Add(tileSource) in Silverlight) and set the opacity (VETileSourceSpecification.Opacity in AJAX; or, MapTileLayer.Opacity in Silverlight). You can put as many raster layers atop the map as you want. I found a couple applications that do just this – one using the Bing Maps AJAX Control; the other using the Bing Maps Silverlight Control. There are quite a few out there, but these just hit the web so I figured I’d call them out...
Time Map – This ShootHill application contains historic maps of the town of Shrewsbury and parts of London using the Bing Maps AJAX Control. Use the TimeScope to move around the map and the TimeSlider to see how land has changed over time. Lot’s of cool features built into this application. By default, you’ll see a Bing Map with a window (aka TimeScope) which you can move around sort of like a magnifying glass to see historic maps of a specific area. As you view the area, you can specify which period of time you’re most interested in ranging from 1882 – 2009 by moving the TimeSlider left or right. Clicking on the Options button on the TimeScope provides a menu with other cool features:
- Flip TimeScope – swap the Bing Maps from the background into the TimeScope and put the historic map as the backdrop.
- Historic Photos – bring up the aerial photos of London from 1966 to see changes over time.
- Hide / Show Scope – hides or shows the boundaries of the TimeScope.
- Historic London – zips you over to London to interact with the TimeScope over London.
- Historic Shrewsbury – zips you back to Shrewsbury to interact with the TimeScope over Shrewsbury.
- 3D TimeMap, Record and Point Marker are reserved as future enhancements.
State of Queensland – Built by Soul Solutions using ICE, Map Cruncher and hosted in Azure Services Platform, The State of Queensland (Department of Natural Resources and Water) provides an Bing Maps Silverlight application that allows users to control the year using a simple sliding bar. The years range from 1958 – 2009, so my Ozzie friends can see the aerial photos of the past in Queensland. Muti-image scaling greatness with Silverlight.
Fun stuff and certainly something that’s interesting to view the evolution of land over time. An excellent means of researching land development and population spread. Throw some spatial / demographic information atop this over the years, plus maybe some real estate home values, etc. Well, now I’m just giving away ideas that consultants get paid to come up with.
CP
PreviousNext