ASHX 将 SQL 数据序列化为 GeoJSON 格式

Question

我正在尝试构建一个Web应用程序，该应用程序通过ASHX处理程序从SQL Server中提取数据，转换为有效的GeoJSON格式，并在传单地图上显示标记。我有一个查询从SQLServer中提取三个字段（描述，LAT，LONG）。我能够在传单中显示GeoJSON数据（来自网站文档）。我无法弄清楚的是如何成功构建GeoJSON数据。有没有一种简单的方法可以使用Javascript序列化程序构建GeoJSON对象，或者这是我必须在hanlder中构建的过程。

以下是我想创建的地理 JSON 文件示例：

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

以下是我拥有的当前构建简单 JSON 文件的 ASHX 文件的内容：

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

这

可能有助于您将数据从SQL Server https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/01/05/returning-spatial-data-in-geojson-format-part-1/获取为geoJson格式

根据评论进行编辑：

你也可以在javascript中循环访问"listResults"并手动构建你的geoJson

。

//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
         }
        }
    );
}

构建 geoJson 集合后，可以将其与传单一起使用。