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?

user_15852051 · Answer

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 Answers