4

I have a spatial table in SQL Server, which I want to map using either OpenLayers or Leaflet.

My problem is I can't figure out how to convert the returned results from my query to geojson.

My Linq query looks like this:

 var world = _db.worlds.Select(w => new
        {
            w.ID, w.CODE ,
            COUNTRYNAME = w.CNTRY_NAME,
            COUNTRYPOP = w.POP_CNTRY,
            w.FIPS,
            GEOMETRY = w.geom,
            w.WKT
        });

Tried using GeoJson.Net but it's not documented enough for me to understand, but what I know is the geometry that is returned when parsed to text, it returns Well Known Text with Multiple Geometry, i.e polygons and multipolygons to be precice.

var geometries = new GeoJSON.Net.Geometry.GeometryCollection(world.WKT);
//This is giving an error that world.WKT is not of type IGeometryCollection<>

Anyone know a work around my problem even using other packages that isn't the GeoJson.Net?

2
1

I finaly used GeoJSON.Net.Contrib package as suggested by @Stonetip. The properties variable simply carries the data i want to be displayed as Feature Properties i.e.

{
  "type": "Feature",
  "properties": {
   /*propereties start*/
   'id':'Full Name',
   'faultType':'fault-type',
   /*more properties*/
  },
  "geometry":{}
}

Here is the full MVC Controler

    public ActionResult GetUnassignedResult()
    {
        var unassigned = _db.FaultRecords.Where(c => c.Responce.Equals("Unassigned"));
        var pointList = new List<Feature>();
        foreach (var results in unassigned)
        {
            var date = results.DateTimeCaptured;
            var dateformated = $"{date:yyyy-MM-dd}";
            var geometry = new Point(
                new GeographicPosition(double.Parse(results.YCoordinate.ToString(CultureInfo.InvariantCulture)),
                    double.Parse(results.XCoordinate.ToString(CultureInfo.InvariantCulture))));
            var properties = new Dictionary<string, object>
            {
                {"id", results.Id},
                {"reportedby", results.C_FullName},
                {"faulttype", results.FaultType},
                {"areaname", results.Area},
                {"datetimecaptured", dateformated},
                {"reportedvia", results.ReportedVia},
                {"comment", results.Comments},
                {"status", results.Responce},
                {"latitude", results.YCoordinate },
                {"longitude", results.XCoordinate },
                {"cemail", results.C_Email },
                {"zone", results.Zone },
                {"recordid", results.RecordID },
                { "cidnumber", results.C_IDNumber },
                { "cmobilenumber", results.C_MobileNumber }

            };
            var feature = new Feature(geometry, properties);
            pointList.Add(feature);
        }
        return Json(pointList, JsonRequestBehavior.AllowGet);
    }

This returns a Feature array which i then used in my javascript code to make a FeatureCollection like :

var geojson_feature_collection = {
  "type": "FeatureCollection",
  "features": []/*GeoJSON Feature Array from MVC Controller*/
}

Also now using SQL Server 2016 onward you can return JSON straight from SQL Server. Since GeoJSON is JSON, it can be formatted using new FOR JSON clause in SQL Server. Check out this blog or this

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.