Reformat SQLGeography polygons to JSON

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 开发者_JS百科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?

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.

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"
}

To answer your question about efficiency, For this particular case, I don't think that Replace vs RegEx is going to be that big of a difference. All we are really changing is some parenthesis and commas. Personally, I prefer to do things in TSQL for web applications because I can offload the computational work onto the SQL Server instead of the Web Server. In my case I have a lot of data that I am generating for a map and therefore don't want to bog down the webserver with lots of conversions of data. Additionally, for performance, I usually put more horsepower on the SQL server than I do a webserver, so even if there is some difference between the two functions, if Replace is less efficient it is at least being handled by a server with lots more resources. In general, I want my webserver handling connections to clients and my SQL server handling data computations. This also keeps my web server scripts clean and efficient. So my suggestion is as follows:

Write a Scalar TSQL function in your database. This uses the SQL REPLACE function and is somewhat brute force, but it performs really well. This function can be used directly on a SELECT statement or to create calculated columns in a table if you really want to simplify your web server code. Currently this example only supports POINT, POLYGON and MULTIPOLYGON and provides the "geometry" JSON element for the geoJSON format.

GetGeoJSON Scalar Function

CREATE FUNCTION GetGeoJSON (@geo geography) /*this is your geography shape*/
RETURNS varchar(max)
WITH SCHEMABINDING /*this tells SQL SERVER that it is deterministic (helpful if you use it in a calculated column)*/
AS
BEGIN
/* Declare the return variable here*/
DECLARE @Result varchar(max)

/*Build JSON "geometry" element for geoJSON*/

SELECT  @Result = '"geometry":{' +
    CASE @geo.STGeometryType()
        WHEN 'POINT' THEN
            '"type": "Point","coordinates":' +
            REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'POINT ',''),'(','['),')',']'),' ',',')
        WHEN 'POLYGON' THEN 
            '"type": "Polygon","coordinates":' +
            '[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'POLYGON ',''),'(','['),')',']'),'], ',']],['),', ','],['),' ',',') + ']'
        WHEN 'MULTIPOLYGON' THEN 
            '"type": "MultiPolygon","coordinates":' +
            '[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'MULTIPOLYGON ',''),'(','['),')',']'),'], ',']],['),', ','],['),' ',',') + ']'
    ELSE NULL
    END
    +'}'

    /* Return the result of the function*/
    RETURN @Result

END

Next, use your GetGeoJSON function in your SELECT statement, for example:

SELECT dbo.GetGeoJSON([COLUMN]) as Geometry From [TABLE]

I hope this provides some insight and helps others looking for a methodology, good luck!

The method outlined in James's answer works great. But I recently found an error when converting WKT where the Longitude had a value over 99.

I changed the regular expression:

@"(-?\d{1,2}\.\dE-\d+|-?\d{1,3}\.?\d*)\s(-?\d{1,2}\.\dE-\d+|-?\d{1,2}\.?\d*)\s?0?\s?0?,?"

Notice the second "2" has been changed to a "3" to allow longitude to go up to 180.

Strings are immutable in .net, so when you replacing some, you creating an edited copy of previous string. This is not so critical for performance, as for memory usage.

Look at JSON.net

Or use StringBuilder to generate it properly.

StringBuilder sb = new StringBuilder();
sb.AppendFormat();

Utility function that is used for formatting spatial cells as GeoJSON is shown below.

DROP FUNCTION IF EXISTS dbo.geometry2json
GO

CREATE FUNCTION dbo.geometry2json( @geo geometry)
RETURNS nvarchar(MAX) AS
BEGIN
RETURN (

'{' +
(CASE @geo.STGeometryType()
WHEN 'POINT' THEN
'"type": "Point","coordinates":' +
REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'POINT ',''),'(','['),')',']'),' ',',')
WHEN 'POLYGON' THEN
'"type": "Polygon","coordinates":' +
'[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'POLYGON ',''),'(','['),')',']'),'], ',']],['),', ','],['),' ',',') + ']'
WHEN 'MULTIPOLYGON' THEN
'"type": "MultiPolygon","coordinates":' +
'[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'MULTIPOLYGON ',''),'(','['),')',']'),'], ',']],['),', ','],['),' ',',') + ']'
WHEN 'MULTIPOINT' THEN
'"type": "MultiPoint","coordinates":' +
'[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'MULTIPOINT ',''),'(','['),')',']'),'], ',']],['),', ','],['),' ',',') + ']'
WHEN 'LINESTRING' THEN
'"type": "LineString","coordinates":' +
'[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'LINESTRING ',''),'(','['),')',']'),'], ',']],['),', ','],['),' ',',') + ']'
ELSE NULL
END)
+'}')

END