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?

Note: Geographic definitions are for GB only, which is reflected in the regex. — James, Jun 28 '11 at 12:56
Don't return the data as text. Return it directly using `SqlGeometry` or `DbGeometry`. Or return is as GeoJSON directly from the DB. — jpmc26, Mar 24 '18 at 01:13

score 13 · Answer 1 · answered Aug 11 '11 at 13:12

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.

score 12 · Accepted Answer · answered Feb 14 '15 at 18:57

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

Probably the better answer these days, as I originally answered my own question over 10 years ago! — James, May 27 '21 at 17:18

score 10 · Answer 3 · edited Aug 20 '14 at 19:22

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!

Where did you get this error? When calling the function? If so, it takes geography as an argument, not geometry. — Erik Likeness, Dec 01 '14 at 15:24

score 2 · Answer 4 · answered Feb 28 '16 at 20:09

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.

score 1 · Answer 5 · answered Jun 28 '11 at 13:04

1

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

answered Jun 28 '11 at 13:04

Yuriy Naydenov

1,782
1
11
25

1

I know this, and I already use JSON.Net. Because the strings have no delimiter, I can not spit them and then add them to StringBuilder or JSON object. I can detect the Lat/Long pairs but the bracketing in the sting is also important because it denotes the depth of the array, otherwise I could just take my matches and build a JSON object and not care about the brackets. Essentially these strings are almost JSON objects anyway, they just need to be reformatted to JSON syntax. – James Jul 05 '11 at 12:14

score 1 · Answer 6 · edited May 06 '21 at 13:55

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

Reformat SQLGeography polygons to JSON

6 Answers6