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.