ASHX To Serialize SQL Data To GeoJSON Format

Question

I am trying to build a web application that pulls data from SQL Server through an ASHX Handler, convert to a valid GeoJSON format, and display the markers on a leaflet map. I have the query to pull three fields from the SQLServer (Description, LAT, LONG). I have the ability to display GeoJSON data in Leaflet (from website documentation). What I can't figure out is how to successfully build GeoJSON data. Is there an easy way to build a GeoJSON Object using the Javascript Serializer, or is it a process I have to build in the hanlder.

Here is an example GeoJSON file I would like to create:

{
  "type": "FeatureCollection",
  "features": [{
      "type": "Feature",
      "properties": {
        "name": "Placemarker 1",
        "marker-color": "#0000ff",
        "marker-symbol": "airport"
      },
      "geometry": {
        "type": "Point",
        "coordinates": [
          -77.12911152370515,
          38.79930767201779
        ]
      }
    },
    {
      "type": "Feature",
      "properties": {
        "name": "Placemarker 2",
        "marker-color": "#FF0000",
        "marker-symbol": "hospital"
      },
      "geometry": {
        "type": "Point",
        "coordinates": [
          -7.12911152370515,
          5.79930767201779
        ]
      }
    }]
}

Here is contents of an ASHX file that I have that currently build a simple JSON File:

private class DataSet
{
    public string description { get; set; }
    public double valueLat { get; set; }
    public double valueLong { get; set; }
}

public void ProcessRequest(HttpContext context)
{
    List<DataSet> listResults = new List<DataSet>();
    int recordCount = 0;

    try
    {
        // Get Connection String From WEB.CONFIG
        string connStr = ConfigurationManager.ConnectionStrings["myConnStr"].ConnectionString;

        // Connect And Get Data
        OdbcConnection sqlConn = new OdbcConnection(connStr.ToString());
        OdbcCommand sqlCmd = new OdbcCommand("{call getSampleData}", sqlConn);
        sqlConn.Open();
        OdbcDataReader rdr = sqlCmd.ExecuteReader();

        while (rdr.Read())
        {
            DataSet results = new DataSet();
            results.description = rdr["description"].ToString();
            results.valueLat = Convert.ToDouble(rdr["lat"]);
            results.valueLong = Convert.ToDouble(rdr["long"]);
            listResults.Add(results);
            recordCount++;
        }

        sqlConn.Close();
    }
    catch (OdbcException o)
    {
       context.Response.Write(o.Message.ToString());
    }

    var result = new
    {
        iTotalRecords = recordCount,
        aaData = listResults
    };

    JavaScriptSerializer js = new JavaScriptSerializer();
    context.Response.Write(js.Serialize(result));
}

Answer 1

This might help you get the data into geoJson format from SQL Server https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/01/05/returning-spatial-data-in-geojson-format-part-1/

Edit based on comments:

You could also just loop through the "listResults" in javascript and build your geoJson manually

//listResults...this is dummy data, 
//but an example of what you are currently getting from SQL
var listResults = 
[{
    description:"description 1",
    valueLat: 39.8282,
    valueLong: -98.5795
},{
    description:"description 2",
    valueLat: 38.8282,
    valueLong: -97.5795
},{
    description:"description 3",
    valueLat: 37.8282,
    valueLong: -96.5795
}];

//empty geoJson collection
var geoJsonData = [];

//loop through the listResults to build individual geoJson features
for (var i = 0; i < listResults.length; i++) {
    var result = listResults[i];
    geoJsonData.push(
        {
         "type": "Feature",
         "geometry": {
         "type": "Point",
         "coordinates": [result.valueLong, result.valueLat]
         },
         "properties": {
         "description": result.description
         }
        }
    );

}

Once you build the geoJson collection, you can consume it with leaflet.