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?
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.