Reformat SQLGeography polygons to JSON

Question

I am building a web service that serves geographic boundary data in JSON format.

The geographic data is stored in an SQL Server 2008 R2 database using the geography type in a table. I use [ColumnName].ToString() method to return the polygon data as text.

Example output:

POLYGON ((-6.1646509904325884 56.435153006374627, ... -6.1606079906751 56.4338050060666))

MULTIPOLYGON (((-6.1646509904325884 56.435153006374627 0 0, ... -6.1606079906751 56.4338050060666 0 0)))

Geographic definitions can take the form of either an array of lat/long pairs defining a polygon or in the case of multiple definitions, an array or polygons (multipolygon).

I have the following regex that converts the output to JSON objects contained in multi-dimensional arrays depending on the output.

Regex latlngMatch = new Regex(@"(-?[0-9]{1}\.\d*)\s(\d{2}.\d*)(?:\s0\s0,?)?", RegexOptions.Compiled);

    private string ConvertPolysToJson(string polysIn)
    {
        return this.latlngMatch.Replace(polysIn.Remove(0, polysIn.IndexOf("(")) // remove POLYGON or MULTIPOLYGON
                                               .Replace("(", "[")  // convert to JSON array syntax
                                               .Replace(")", "]"), // same as above
                                               "{lng:$1,lat:$2},"); // reformat lat/lng pairs to JSON objects
    }

This is actually working pretty well and converts the DB output to JSON on the fly in response to an operation call.

However I am no regex master and the calls to String.Replace() also seem inefficient to me.

Does anyone have any suggestions/comments about performance of this?

Answer 1

To convert from WKT to GeoJson you can use NetTopologySuite from nuget. Add NetTopologySuite and NetTopologySuite.IO.GeoJSON

var wkt = "POLYGON ((10 20, 30 40, 50 60, 10 20))";
var wktReader = new NetTopologySuite.IO.WKTReader();
var geom = wktReader.Read(wkt);
var feature = new NetTopologySuite.Features.Feature(geom, new NetTopologySuite.Features.AttributesTable());
var featureCollection = new NetTopologySuite.Features.FeatureCollection();
featureCollection.Add(feature);
var sb = new StringBuilder();
var serializer = new NetTopologySuite.IO.GeoJsonSerializer();
serializer.Formatting = Newtonsoft.Json.Formatting.Indented;
using (var sw = new StringWriter(sb))
{
    serializer.Serialize(sw, featureCollection);
}
var result = sb.ToString();

Output:

{
  "features": [
    {
      "type": "Feature",
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [
            [
              10.0,
              20.0
            ],
            [
              30.0,
              40.0
            ],
            [
              50.0,
              60.0
            ],
            [
              10.0,
              20.0
            ]
          ]
        ]
      },
      "properties": {}
    }
  ],
  "type": "FeatureCollection"
}

Answer 2

Again just to just to close this off I will answer my own question with the solution im using.

This method takes the output from a ToString() call on an a MS SQL Geography Type. If the string returned contains polygon data contructed form GPS points, this method will parse and reformatted it to a JSON sting.

public static class PolyConverter
{
    static Regex latlngMatch = new Regex(@"(-?\d{1,2}\.\dE-\d+|-?\d{1,2}\.?\d*)\s(-?\d{1,2}\.\dE-\d+|-?\d{1,2}\.?\d*)\s?0?\s?0?,?", RegexOptions.Compiled);
    static Regex reformat = new Regex(@"\[,", RegexOptions.Compiled);

    public static string ConvertPolysToJson(string polysIn)
    {
        var formatted = reformat.Replace(
                        latlngMatch.Replace(
                        polysIn.Remove(0, polysIn.IndexOf("(")), ",{lng:$1,lat:$2}")
                        .Replace("(", "[")
                        .Replace(")", "]"), "[");

        if (polysIn.Contains("MULTIPOLYGON"))
        {
            formatted = formatted.Replace("[[", "[")
                                 .Replace("]]", "]")
                                 .Replace("[[[", "[[")
                                 .Replace("]]]", "]]");
        }

        return formatted;
    }
}

This is specific to my apllication, but maybe useful to somebody and maybe even create a better implementation.