Using OGC functions in SQL Server 2008 part 5 – Google Maps and polygons

In this series of posts I’ve so far managed to pull some spatial data into SQL Server, query it in a number of ways and finally been able to display some of that data in Google Maps. The next thing I wanted to do was to display the county boundary for Lancashire, since it was the postcodes for that great county I was displaying. I’ve already got the data for the county boundary from OS OpenSpace but the problem is this data is in UK OS coordinates rather than latitude and longitude as used by Google Maps. There are two ways to deal with this. Convert the data to latitude/longitude before displaying it or implement the GProjection interface to handle the conversion in the web page. The latter is something I want to look at at some point but for this I decided to do the conversion server-side.

So the first thing I did was to write a simple generic handler to generate an XML document containing the points for the county boundary. This code uses the .NET Coordinates library I have converted from the Java original. This is what the handler looks like

  public void ProcessRequest (HttpContext context) 
  {
    context.Response.ContentType = "text/xml";
    StringWriter stringWriter = new StringWriter();
    XmlTextWriter writer = new XmlTextWriter(stringWriter);
    writer.WriteStartElement("points");
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["postcodesDatabase"].ConnectionString))
    {
      conn.Open();
      using (SqlCommand comm = conn.CreateCommand())
      {
        comm.CommandText = "SELECT Boundary.STNumPoints() FROM CountyBoundaries WHERE County='Lancashire'";
        int numPoints = (int)comm.ExecuteScalar();
        for (int i = 1; i <= numPoints; i++)
        {
          writer.WriteStartElement("point");
          // get X
          comm.CommandText = "SELECT Boundary.STPointN(" + i + ").STX FROM CountyBoundaries WHERE County='Lancashire'";
          double x = (double)comm.ExecuteScalar();

          // get Y
          comm.CommandText = "SELECT Boundary.STPointN(" + i + ").STY FROM CountyBoundaries WHERE County='Lancashire'";
          double y = (double)comm.ExecuteScalar();

          // convert to lat/long
          OSRef osRef = new OSRef(x, y);
          LatLng latLong = osRef.ToLatLng();

          writer.WriteAttributeString("x", latLong.Longitude.ToString());
          writer.WriteAttributeString("y", latLong.Latitude.ToString());
          
          writer.WriteEndElement();
        } 
      }
    }
    writer.WriteEndElement(); // points
    context.Response.Write(stringWriter.ToString());
  }

Next up was the JavaScript required in the web page to display the data. This is pretty simple

        // load boundary
        var boundReq = new XMLHttpRequest();
        boundReq.open("GET", "GetBoundary.ashx", true);
        boundReq.onreadystatechange = function() {
          if (boundReq.readyState == 4) {
            if (boundReq.status == 200) {
              var nodes = boundReq.responseXML.selectNodes("points/point");
              var latLongs = new Array();
              for (var i = 0; i < nodes.length; i++) {
                var lng = nodes[i].getAttribute("x");
                var lat = nodes[i].getAttribute("y");
                var latLong = new GLatLng(lat, lng);
                latLongs.push(latLong);
              }
              var polygon = new GPolygon(latLongs, "#FF0000", 3);
              map.addOverlay(polygon);
            }
          }
        };
        boundReq.send(); 

If you look at the image above you may think the boundary displayed is not correct since it doesn’t include some places that are quite obviously in Lancashire (Blackburn and Darwen for instance) and this confused me for a while. But the boundary displayed is that of Lancashire County council, rather than the full county, which doesn’t include some areas which are in unitary authorities but still within the county.