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.

Using OGC functions in SQL Server 2008 part 4 – Google Maps

So I’d now got to the stage where I’d pulled in some postcode data into SQL Server, got the county boundaries for one county (the mighty Lancashire) and figured out how to do a spatial query to tell me which postcodes were in that county. Now I wanted to display those postcodes on a map. So time to turn to Google Maps and Visual Studio.

First I created a simple generic handler in C# that returned an XML document containing the geometries of the postcodes. This could return JSON if that floats your boat but here is what my version 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 Location.STX AS X, Location.STY AS Y, City, Region, Postcode FROM Postcodes WHERE " +
            "(SELECT Boundary FROM CountyBoundaries).STContains(OsLocation)=1";
          using (SqlDataReader reader = comm.ExecuteReader())
          {
            while (reader.Read())
            {
              writer.WriteStartElement("point");
              writer.WriteAttributeString("description", reader["Postcode"].ToString() + ", " + reader["City"].ToString() + ", " + reader["Region"].ToString());
              writer.WriteAttributeString("x", reader["X"].ToString());
              writer.WriteAttributeString("y", reader["Y"].ToString());
              writer.WriteEndElement();
            }
          }
        }
      }
      writer.WriteEndElement(); // points
      context.Response.Write(stringWriter.ToString());
    }

Next I created a HTML page with a bit of JavaScript to load up the postcode data, which looked like this.

      function loadMap() {
      var map;
      if (GBrowserIsCompatible()) {
        map = new GMap2(document.getElementById("map"));
        map.setCenter(new GLatLng(53.756830663572174, -2.73834228515625), 9);
        map.addControl(new GSmallMapControl());
        map.addControl(new GMapTypeControl());
        map.enableDoubleClickZoom();
        map.enableScrollWheelZoom();

        // load data
        var req = new XMLHttpRequest();
        req.open("GET", "GetPoints.ashx", true);
        req.onreadystatechange = function() {
          if (req.readyState == 4) {
            if (req.status == 200) {
              var nodes = req.responseXML.selectNodes("points/point");
              for (var i = 0; i < nodes.length; i++) {
                var lng = nodes[i].getAttribute("x");
                var lat = nodes[i].getAttribute("y");
                var marker = new GMarker(new GLatLng(lat, lng), { title: nodes[i].getAttribute("description") });
                map.addOverlay(marker);
              }
            }
          }
        };
        req.send();
      }
    }

Again nothing groundbreaking but the next stage could me more challenging. That is pulling out the data for the county boundary and plotting that on the map. And what is this all for? All part of my plans for world domination.

Using OGC functions in SQL Server 2008 part 3

It’s been a while since I last tried to do anything with the spatial features in SQL Server 2008. My next plan was to query the database to see which postcodes were in a particular county. To do this I got hold of the boundary data for some counties using the OpenSpace API. I just added one county, the one and only Lancashire, with the following code.

INSERT INTO CountyBoundaries (County, Boundary) VALUES ('Lancashire', 
geometry::STPolyFromText('POLYGON((335390.6 402509.3,335318.8 405919.1,337285.8 405717.9,341638.1 403012.7,341198.7 401158.3,

345355.3 399036.6,348231.1 404146.9,349111.4 402150.3,351662.6 402905.5,353409.1 407181.8,352474 409073.9,354484.8 412190.4,

358109.6 412599.6,358670 410970.3,360670.3 412859.2,362419.8 411151.7,366280.6 414615.7,364921.9 418843.3,366064.7 421813,

363060.8 427981.2,363748.5 429031.4,369403 431708.6,372966.5 423266.5,375369.8 421263.6,375568.6 419018,379162.2 417655.1,

380342.3 418953.7,382117.1 413139.9,383850.1 418586.1,385712 419187.4,387839.3 415759.4,389429.8 416106,390432.6 420649.6,

388662.6 425190.8,391447.8 428359.4,392703.4 434383.5,396065.9 436596.5,397063 439322.3,394185.1 441332.3,392652.8 446613.3,

388130.5 448821.7,387988.3 450699.5,387239.2 452627.7,385286.7 451690.4,384991.7 453964.6,380792.9 453190.5,379173.9 455357.1,

376997.3 455460,377844.2 457098.1,375605.7 461521.8,372209.8 460317.7,369352.3 461239,369714.2 464470.9,363191.2 470277.6,

363491 473169.9,365227.3 473672.8,370052.2 481319.2,370144.8 482748.7,362512.5 477911.7,358350.8 478665.3,355703.4 474137.7,

347972.3 478292.1,345446.8 475698.7,342749 476087.5,342784.7 474615.5,340414.8 475469.9,342882.8 469480.8,341304.5 465488.7,

344238.6 465331.3,340055 463217.2,340345.5 460141.4,335601.4 455045.4,336057.5 453597.1,332947.7 450366,329952.5 450443.2,

330737.1 442705.2,333041.8 441857.2,333572.8 437130.7,335319.6 433501.9,333963.4 433247,334425.2 431366.8,329729.3 431451.9,

329199.8 427517.3,328505.3 422791.4,330070.5 422095.3,336522 423531.2,337967.3 418422.4,332016.4 412241.1,332374.5 408594.8,

330736.4 405654.3,335390.6 402509.3))', 4277))

This is where things got a little tricky. The county boundaries used the OS coordinate system, whereas my postcodes were stored in latitude/longitude format. I was hopeful that SQL Server would do some magic for me and handle the different coordinate systems, but it would appear this isn’t the case.

So it looked like the only option was to convert my postcode points into the OS coordinate system. I’ve used phpCoord to do this conversion in the past, but this time I was looking for a .NET solution. So I took the Java implementation and converted it C#. That took a while (and I’ll upload the code at some point when I’ve OKed it with the original author) but after modifying my code to insert postcodes into the database I was able to run the following query and successfully pull out postcodes that were in the Lancashire area.

SELECT * FROM Postcodes WHERE (SELECT Boundary FROM CountyBoundaries).STContains(OsLocation)=1