OpenLayers with MVC and SQL Server

Question

I have imported some shape files into SQL Server 2008. Now i want to use OpenLayers with my MVC 4 application to render those shape files from SQL Server Database.

Any help in this regard?

Answer 1

The database, only stores the data, and you need some way of getting that data in a format which OpenLayers will understand.

The easiest way of doing this would be to have a WMS server which can connect to your database, and serve out the data as a WMS service which you can then consume in OpenLayers.

I'll suggest that you look into using GeoServer. Using an extension, you can connect to spatial data in MS SQL from Geoserver. Please see this documentation page for more details.

Answer 2

I have done this but with Postgres. You could create an action to get the json from the controller to the html. Openlayers can create a layer using json data or geojson, using something like this:

   var xhttp;
   xhttp = new XMLHttpRequest();
   xhttp.onreadystatechange = function ()
   {
       if (xhttp.readyState == 4 && xhttp.status == 200)
       {
           return xhttp;
       }
   };
   xhttp.open("GET", 'g_estados/GetEstadosJson', false);
   xhttp.send();
var data = JSON.parse(xhttp.responseText);
var format = new ol.format.WKT();
   var features = new Array();
for (i = 0, len = data.length; i < len; i++)
   {
       var fea = format.readFeature(data[i].wkt, {
           dataProjection: 'EPSG:3857',
           featureProjection: 'EPSG:3857'
       });
   fea.setId(data[i]['id']);
   features.push(fea);

}
var layer = new ol.layer.Vector({
       id: 'test',
       name: 'test',
       source: new ol.source.Vector({
           useSpatialIndex: true,
           format: 'wkt',
           features: features
       }),
       visible: true
   });
   map.addLayer(layer);

and an Action like this:

   public JsonResult GetEstadosJson()
   {
       IEnumerable<g_estado> data = db.g_estado.ToList();
   return new JsonResult()
   {
       Data = data,
       MaxJsonLength = 86753090,
       JsonRequestBehavior = JsonRequestBehavior.AllowGet
   };

}

MaxJsonLenght limits the size of data returned in the action, so make sure there is not a lot of data to return, it could produce 2 kind of errors, limit exceeded or out of memory if this get too long.