Archive for February, 2011

February 23, 2011

Mappa Mercia Demonstrating the Power of Open Street Maps

Earlier I came across a post on the mappa mercia site that really demonstrates the power of the collaborative nature of Open Street Map compared to other web mapping providers. One determined user has chosen to explore and map all the features in a particular UK postcode area, B72, in Birmingham. And when I say “map”, I don’t just mean the road network… the Open Street Map of this area now shows 3500 individual labelled residential properties, including gardens, almost 300 named retail units, 100 commercial buildings, together with all the normal OSM details of bus stops, litter bins etc. The level of detail in the resulting map is remarkable.

For reference, here’s a screenshot from Bing Maps’ roadmap view of a small area in the B72 postcode area:

image

Here’s Google Map’s, marginally more detailed, view of the same area:

image

And, largely based on the efforts of one person, here’s the Open Street Map version (click to enlarge):

image

Note the house numbers, additional detail of the shape of roads, boundaries of properties, every individual retail outlet listed separately… just brilliant.

This is only a small area of one country but, with the coordinated effort of talented users on the ground, it’s easy to see how OSM’s collaborative model can create more accurate, more up-to-date mapping information than the commercial data providers used by Google, Bing, Yahoo, and the like could possibly match. The future looks bright for mapping….

February 23, 2011

Heat Mapping Crime Data with Bing Maps and HTML5 Canvas

Crime data is frequently presented using “heat maps”. See, for example:

imagehttp://www.bbc.co.uk/truthaboutcrime/crimemap/ image http://www.zubedpi.com/

One of the reasons why heat maps are used to visualise crime data is that crimes are typically recorded as individual incidents, occurring at one particular location, yet we tend to want to think of areas at high- or low- risk of crime. We therefore need some way of aggregating those individual crime occurrences into regions that can be analysed.

It doesn’t necessarily make sense to create a thematic map of crimes based on geographic or administrative divisions – a burglar probably does not have much regard for whether he commits a crime in one polling district/ward/street of a city or another, for example, and we should not attempt to fit the distribution of crimes to such arbitrary regions. A heatmap creates a way of summarising and presenting a set of point data with the only influencing factor being the underlying geographic distribution of the points – a greater number of points in the same proximity leads to a “hotter” region.

Seeing as the government has just launched the http://www.police.uk/data portal providing access to a dataset of all reported crime in England and Wales (at a ridiculous cost of £300,000 for 40 basic CSV files, updated monthly), I thought that this would be a good opportunity to update my old heatmapping code to use Bing Maps AJAX v7. What’s more, seeing as Internet Explorer 9 now supports the <canvas> element, I thought I’d try creating heat maps dynamically in the client-side browser rather than rendering them as images on the server-side, as I typically have done in the past.

To start with, I created an array of locations from which my heatmap would be created. I used all of the “anti-social behaviour” crimes reported by Norfolk constabulary during December 2010 (3,185 records). Having converted each record in the CSV file to a Microsoft.Maps.Location, my array looked like this:

var locations = [
new Microsoft.Maps.Location(52.672802972383, 0.943923796417529),
new Microsoft.Maps.Location(52.624236498992, 1.29493956651563),
new Microsoft.Maps.Location(52.6218783667792, 1.29080178760268),
…
]

I then created a <canvas> element in my HTML page and placed it so that it exactly covered the <div> element in which my Bing map would be created:

var canvas = document.createElement('canvas');
canvas.id = 'heatmapcanvas'
canvas.style.position = 'absolute';
canvas.height = 800;
canvas.width = 1024;
canvas.style.zIndex = 1;
document.getElementById('mapDiv').lastChild.appendChild(canvas);

Note that, as Bing Maps v7 doesn’t have an AddCustomLayer() method as in the previous v6.x versions, I’m having to manually add the canvas element into the correct place in the DOM using appendChild(). This method is not perfect as it places the canvas on top of the map controls as well as the map tiles – I’m going to be looking at this issue later.

The next step is to loop through each of these locations and add a radial gradient at the appropriate pixel location on the canvas (which, because the canvas is placed exactly over the map, can be determined by the tryLocationToPixel() method). The appearance of each heat point produced is affected by two parameters – radius determines the extent to which heat spreads out around a point, and intensity determines how hot it is at the centre of each point.

// Create the Intensity Map
for (var i = 0; i < locations.length; i++) {
var loc = locations[i];

// Convert lat/long to pixel location
var pixloc = map.tryLocationToPixel(loc, Microsoft.Maps.PixelReference.control);
var x = pixloc.x;
var y = pixloc.y;

// Create radial gradient centred on this point
var grd = ctx.createRadialGradient(x, y, 0, x, y, radius);
grd.addColorStop(0.0, 'rgba(0, 0, 0, ' + intensity + ')');
grd.addColorStop(1.0, 'transparent');

// Draw the heatpoint onto the canvas
ctx.fillStyle = grd;
ctx.fillRect(x - radius, y - radius, x + radius, y + radius);
}

To start with, I used values of radius 20 and intensity 0.25. This meant that the centre of any single point would be plotted as black colour with 0.25 transparency. The visibility of each point would gradually diminish to be fully transparent 20 (pixels) away from the centre. However, the heat of any points closer than 20 (pixels) to each other would create an additive effect, and produce a heat intensity map like this:

image

Having created a single-channel heat intensity map as above, you can then colourise it by creating a linear gradient, and mapping the alpha values of each pixel on the canvas to the appropriate value from the gradient.

The following code demonstrates a linear colour gradient that progresses from cool colours to hot colours, as typically used in a heat map:

function createColourGradient() {
var ctx = document.createElement('canvas').getContext('2d');
var grd = ctx.createLinearGradient(0, 0, 256, 0);
grd.addColorStop(0.0, 'magenta');
grd.addColorStop(0.25, 'blue');
grd.addColorStop(0.5, 'green');
grd.addColorStop(0.75, 'yellow');
grd.addColorStop(1, 'red');
ctx.fillStyle = grd;
ctx.fillRect(0, 0, 256, 1);
return ctx.getImageData(0, 0, 256, 1).data;
}

And this colour gradient can be used to colour the intensity map as follows:

      var dat = ctx.getImageData(0, 0, canvas.width, canvas.height);
var pix = dat.data;
var ColourGradient = createColourGradient();
for (var p = 0; p < pix.length;) {
var a = pix[p + 3]*4; // get the alpha value of this pixel
pix[p] = ColourGradient[a]; // get the red value from linear gradient that corresponds to this alpha
pix[p + 1] = ColourGradient[a + 1]; //get the green value based on alpha
pix[p + 2] = ColourGradient[a + 2]; //get the blue value based on alpha
p+=4; // Move on to the next pixel
}
ctx.putImageData(dat, 0, 0);

// Populate the canvas
ctx.fillRect(0, 0, 1024, 800);

The preceding code might need a bit of explanation…

  • Each pixel in the array returned by getImageData() is represented by 4 bytes of data, those bytes representing the Red, Green, Blue, and Alpha values of that pixel.
  • So, in the for loop above, the line pix[p+3]*4 is used to return the alpha value only of each pixel in the pix array. (Remember that the canvas was created by placing radialgradients around each point, which varied from an alpha of intensity at the centre to alpha of 0 at a distance of radius from the centre).
  • I then look up the pixel that lies at the equivalent position in the ColourGradient spectrum, and copy the first 3 bytes from it into the pixel array, replacing the purely black image with the Red, Green, and Blue values.

The result of colourising the map using the linear gradient defined above is as follows:

image

Of course, HTML5 makes it very easy to colour the heat map using any colour gradient you want. Changing the ColorStops in the linear gradient as follows, for example:

grd.addColorStop(0.0, 'black');
grd.addColorStop(0.5, 'red');
grd.addColorStop(0.9, 'pink');
grd.addColorStop(1, 'white');

leads to the following:

image

There’s still a few issues I’m having with my code – namely trying to get the <canvas> element inserted at the right place in the DOM to be above the map tiles but underneath the navbar (it seems that all elements in the AJAX v7.0 control are displayed at z-index:0, so I can’t use this as a method top position it). Also, whilst the code above works great in IE9 and Google Chrome, Firefox often gives me the “slow script” warning… I’m not sure what element it’s unhappy with, or whether it’s just that Firefox is not as HTML5-compliant as it claims to be… (ahh the joys of creating cross-browser web apps!)

If I get these issues ironed out, and if anybody shows any interest, then I might package up the complete heatmap code into a reusable class and pop it up on codeplex.

February 21, 2011

Using OGR2OGR to Convert, Reproject, and Load Spatial Data to SQL Server

In a previous post, I used OGR2OGR to join a set of shapefiles together prior to loading them into SQL Server using Shape2SQL. But OGR2OGR can do much more than simply appending shapefiles – it can convert data into different formats, transform coordinates into different spatial references systems, read and write a whole variety of spatial datasources, and (with a bit of fiddling) load them into SQL Server 2008.

To demonstrate, I thought I’d repeat the objective of my previous post, but instead of simply appending the Ordnance Survey open data shapefiles together, I would reproject them into a different SRID, and import them into SQL Server too using OGR2OGR alone, without using Shape2SQL or any other tools.

Use OGR2OGR to create a CSV file containing WKT

OGR2OGR can’t insert spatial data directly into SQL Server 2008, but it can create CSV files that can be read into SQL Server. To create an output file in CSV format, set the –f CSV output option. You can also manually set layer creation options to dictate the field delimiter and line return character of the CSV file, using the LINEFORMAT and SEPARATOR layer creation options.

By default, the CSV file created only contains columns containing the attributes associated with each shape (i.e the data in the .dbf file associated with a shapefile), but it doesn’t include the shape itself. To include the shape of the geometry in Well-Known Text format, you also need to state the –lco “GEOMETRY=AS_WKT” layer creation option.

But that’s not all – OGR2OGR can also reproject data between coordinate systems as it converts it. To convert to another spatial reference system, include the -t_srs parameter, together with either the full WKT definition (hard to escape properly,i.e. GEOGCS(ELLIPSOID(“WGS 84”, ……) or the EPSG code (much easier, i.e. EPSG:4326 for WGS84).

Putting this all together, you can use the following command to load a shapefile (in this case, the Ordnance Survey OS Vector Map settlement_area polygon shapefile), reproject the data into SRID 4326, and then save the result as a CSV file containing a column with the WKT of each resulting polygon:

ogr2ogr -f "CSV" "Settlement_Area" "Settlement_Area.shp" -t_srs "EPSG:4326" -lco "GEOMETRY=AS_WKT" -lco "LINEFORMAT=CRLF" -lco "SEPARATOR=SEMICOLON"

NOTE – I’ve noticed some problems with the quote/speechmark characters in this blog being changed to “smartquotes”, even when I use the code-formatting option. OGR2OGR isn’t happy about this, so I recommend that you don’t try and copy and paste the line above into a CMD window – if you get an error about “Couldn’t fetch request layer uT” or something similar then be sure to retype using normal speech marks.

More information on CSV options for OGR2OGR can be found here: http://www.gdal.org/ogr/drv_csv.html

Create a Format File

To import the CSV into SQL Server, we have a range of options – you could use SSIS, or you could use the Tasks –> Import Data wizard. Or you could use BCP or BULK INSERT. However, I’m going to use OPENROWSET. This will allow me to write a single query to access the CSV file directly from T-SQL as if it were an existing table.

In order to do this, we first need to define a format file which specifies the datatypes of each of the columns in the input csv file, and how they should be mapped to datatypes of columns in a sql table. More information on format files can be found here.

The following shows the XML format file required for the OS VectorMaps Settlement Area shapefile just converted:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="0" xsi:type="CharTerm" TERMINATOR="""/>
<FIELD ID="1" xsi:type="CharTerm" MAX_LENGTH="2147483647" TERMINATOR="";"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="WKT" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="FEATCODE" xsi:type="SQLDECIMAL"/>
<COLUMN SOURCE="3" NAME="FEATDESC" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

Query the CSV from SQL Server using the Format File

Finally, you can write a SELECT query in SQL Server to query the CSV file using  OPENROWSET in conjunction with the format file above. Since the CSV contains column headers, I’ve included the FIRSTROW=2 parameter to skip the header row. I’ve also used the geometry::Parse() method in the SELECT to dynamically create the geometry data from the Well-Known Text representation contained in the WKT column of the supplied CSV file.

SELECT
FEATCODE,
FEATDESC,
geography::Parse(WKT)
FROM OPENROWSET(
BULK 'C:\Settlement_Area.csv',
FORMATFILE='C:\Settlement_Area_format_file.xml',
FIRSTROW = 2
) AS CSV;

And here’s the results showing my Ordnance Survey data, originally provided as a shapefile using projected coordinates in SRID 27700, now loaded as latitude/longitude coordinates (EPSG:4326) using the geography datatype:

image

February 19, 2011

Using ESRI Base Map Tile Layers in Bing Maps

( UPDATE: For loading ESRI Base Map Tiles on the AJAX v7.0 control, please go to http://alastaira.wordpress.com/2011/04/01/displaying-open-street-map-and-esri-tiles-on-bing-maps-ajax-v7/ )

I received an email this morning from the ESRI announcements mailing list stating that “ArcGIS Online basemaps published and hosted by Esri are now freely available to all users regardless of commercial, noncommercial, internal, or external use.”.

This is a nice surprise – it’s always useful to have a greater choice of tile styles on which to build your map, and ESRI have a nice selection of alternative map types. So, I decided to test them out by creating some ESRI tile layers in the Bing Maps Silverlight control.

The basic method of creating a new tilelayer using the Bing Maps Silverlight control is to define a new custom class in your .xaml.cs code file that inherits from the Microsoft.Maps.MapControl.TileSource class. You set the base of this class to represent the URL template of the tiles that will be used to build this layer. Then, by overriding the GetUri() method of this class, you insert the parameters corresponding to the x, y, and zoomlevel of each requested tile image from this tilelayer. For example, the following code demonstrates how to construct a class based on the ESRI topo world map tiles:

public class ESRITileSource : Microsoft.Maps.MapControl.TileSource
{
 public ESRITileSource() : base("http://services.arcgisonline.com/ArcGIS/rest/services/World_Topo_Map/MapServer/tile/{0}/{1}/{2}")
 { }

public override Uri GetUri(int x, int y, int zoomLevel)
 {
 return new Uri(String.Format(this.UriFormat, zoomLevel, y, x));
 }
}

Then, in your .xaml file, hide the default Bing Maps base tiles by specifying the MercatorMode of the map, and create a new tilelayer based on the custom tilelayer class instead:

<m:Map x:Name="Map1" Grid.Row="1" Grid.Column="1" Center="55,-2" ZoomLevel="6" CredentialsProvider="{StaticResource MyCredentials}" HorizontalAlignment="Stretch" VerticalAlignment="Stretch">
 <!-- Do Not Display Bing Maps base layer -->
 <m:Map.Mode>
 <mcore:MercatorMode></mcore:MercatorMode>
 </m:Map.Mode>
 <!-- Add ESRI Tile Layer -->
 <m:Map.Children>
 <m:MapTileLayer>
 <m:MapTileLayer.TileSources>
 <local:ESRITileSource></local:ESRITileSource>
 </m:MapTileLayer.TileSources>
 </m:MapTileLayer>
 </m:Map.Children>
</m:Map>

Using this method, I tried out a few of the different ESRI styles, as shown below:

World Topography

URL Template: http://services.arcgisonline.com/ArcGIS/rest/services/World_Topo_Map/MapServer/tile/{0}/{1}/{2}

image

World Shaded Relief

URL Template: http://services.arcgisonline.com/ArcGIS/rest/services/World_Shaded_Relief/MapServer/tile/{0}/{1}/{2}

image

DeLorme World Basemap

URL Template: http://server.arcgisonline.com/ArcGIS/rest/services/Specialty/DeLorme_World_Base_Map/MapServer/tile/{0}/{1}/{2}

image

World Physical Map

URL Template: http://services.arcgisonline.com/ArcGIS/rest/services/World_Physical_Map/MapServer/tile/{0}/{1}/{2}

image

There’s plenty more map styles, including demographic maps (US only), specialist maritime maps etc. – see http://www.esri.com/software/arcgis/arcgisonline/standard-maps.html for more details.

Follow

Get every new post delivered to your Inbox.

Join 53 other followers