Getting boundary data out of OS OpenSpace

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();
              }
            }
          }
        }
      }

Getting all available geographic reference systems out of SQL Server 2008

As a reminder to myself, the query required to get a list of all available geographic reference systems out of SQL Server 2008 is

SELECT * FROM sys.spatial_reference_systems

uDig – free GIS

To be frank I don’t much like anything Java based but uDig is the exception that probably proves the rule. It’s a free GIS based on Eclipse and it is pretty user friendly. Unfortunately it doesn’t handle data stored in SQL Server 2008, which marks it down a bit in my eyes, but it does support most other data sources.