Tuesday, December 22, 2009

Getting boundary data out of OS OpenSpace

County boundaries in SQL Server

I’ve been able to pull out a single county’s boundary data from OS OpenSpace using its JavaScript API, but I wanted to get the data for all counties. Now I could have used my little example webpage to do this manually for each county, but that would be extremely tedious. So based on the fact that all AJAX APIs can be considered as simple HTTP requests, I wrote a little piece of C# code that made the required HTTP request and dumped the data into my SQL Server table. This is very rough and ready but it met my needs. Some time soon I will probably put together a class to handle all the OS Open Space interaction a bit more nicely and with more functionality.

      HttpWebRequest req = (HttpWebRequest)WebRequest.Create(
        "http://openspace.ordnancesurvey.co.uk/osmapapi/boundary?geometry=true&"+
        "key=74B560DED619715AE0405F0AF060615A&f=xml&" +
        "url=http%3A%2F%2Flocalhost%2FOpenSpaceBoundary%2FDefault.aspx&area_code=CTY&" +
        "bbox=0%2C0%2C1400000%2C1400000&resolution=1000&dojo.preventCache=1261317566652&" +
        "callback=dojo.io.script.jsonp_dojoIoScript4._jsonpCallback");
      using (WebResponse resp = req.GetResponse())
      using (Stream respStream = resp.GetResponseStream())
      using (StreamReader reader = new StreamReader(respStream))
      {
        string response = reader.ReadToEnd();
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.LoadXml(response);

        using (SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OGC;Data Source=NEWDOOGAL"))
        {
          conn.Open();
          using (SqlCommand command = conn.CreateCommand())
          {
            command.CommandText = "DELETE FROM CountyBoundaries";
            command.ExecuteNonQuery();
          }

          JavaScriptSerializer serializer = new JavaScriptSerializer();
          XmlNodeList nodes = xmlDoc.SelectNodes("BoundaryResultVO/items/item/geojson");
          foreach (XmlElement element in nodes)
          {
            Dictionary<string, object> data = 
              (Dictionary<string, object>)serializer.DeserializeObject(element.InnerText);
            Dictionary<string, object> properties = (Dictionary<string, object>)data["properties"];
            string countyName = (string)properties["NAME"];

            // get polygon data
            Dictionary<string, object> geometry = (Dictionary<string, object>)data["geometry"];
            object[] coordinates = (object[])geometry["coordinates"];
            
            for (int i = 0; i < coordinates.Length; i++)
            {
              StringBuilder wktBuilder = new StringBuilder();
              object[] polygonParts = (object[])coordinates[i];
              wktBuilder.Append("POLYGON(");
              for (int j = 0; j < polygonParts.Length; j++)
              {
                if (j>0)
                  wktBuilder.Append(",");
                wktBuilder.Append("(");
                object[] points = (object[])polygonParts[j];
                for (int k = 0; k < points.Length; k++)
                {
                  object[] point = (object[])points[k];
                  if (k > 0)
                    wktBuilder.Append(",");
                  wktBuilder.Append(point[0]);
                  wktBuilder.Append(" ");
                  wktBuilder.Append(point[1]);
                }
                wktBuilder.Append(")");
              }
              wktBuilder.Append(")");

              // put into database
              using (SqlCommand command = conn.CreateCommand())
              {
                command.CommandText = string.Format(
                  "INSERT INTO CountyBoundaries (County, Boundary) VALUES ('{0}', " +
                  "geometry::STPolyFromText('{1}', 4277))", countyName, wktBuilder.ToString());
                command.ExecuteNonQuery();
              }
            }
          }
        }
      }

No comments: