Convert Sql Server Spatial data to GeoJSON & plot on map

Question

I have a sql server table with Geo spatial data. An example of row would be (with Column names):

type: streetline
code: 231001
geog: 0xE6100000011 ........
Centroid 0xE61000000C.......
geom: 0xE6100000011 ........

I am using C# with MVC3 to draw the above shapes on leaflet map. I am fetching the above sql data into a datatable. Using the sql function "ToString()" on column "geom" I get the following:

"LINESTRING (-1.131510412 52.65531, -1.13286 52.65559)",
"POLYGON ((-1.1116360 52.6409953, -1.1116683 52.6413, -1.11146723 52.641317, -1.11133263 52.6413572, -1.1113059))",

The question is how do I convert the above to GeoJSON to be able to plot on the leaflet map. Here is an example of the expected output (GeoJSON) I want:

var geojsonFeature = {
    "type": "Feature",
    "properties": {
        "name": "Coors Field",
        "amenity": "Baseball Stadium",
        "popupContent": "This is where the Rockies play!"
    },
    "geometry": {
        "type": "Point",
        "coordinates": [-104.99404, 39.75621]
    }
};

Answer 1

ogr2ogr should do this for you. Looks like you have multiple geometry types in the dataset, not sure how that will work out. You might have to filter out by geometry type. Below not tested. See docs linked to above for inputs and flags.

ogr2ogr -f "GeoJSON" "sqlexport.geojson"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT * FROM tbl"

Answer 2

You can use GeoJSON.Net. I'm planning to work with it. I'll let you know it's usefulness when I do.

Answer 3

You can easily solve this with something like (Javascript code)

    var dataRows = [{
        id: 1,
        geom: "LINESTRING(-1.131510412 52.65531, -1.13286 52.65559)"
    }];
    features = [];
    dataRows.forEach(function (row) {
        var coords = row.geom.replace("(", "[").replace(")", "]").substring(row.geom.indexOf("("), row.geom.length);
        features.push({
            "type": "Feature",
                "properties": {
                "id": row.id
            },
            "geometry": {
                "type": row.geom.substring(0, row.geom.indexOf("(")),
                "coordinates": coords
            }
        });
    });

    console.log(JSON.stringify(features[0]));
/* OUTPUT:
// {"type":"Feature","properties":{"id":1},"geometry":{"type":"LINESTRING","coordinates":"[-1.131510412 52.65531, -1.13286 52.65559]"}}
*/

I tried to keep this as simple as possible so you could easily migrate it to whatever language you would like. Please note, that even tough this solves yours issue, It is not recommended to do the parsing in this way.

jsfiddle: https://jsfiddle.net/e78cgogo/32/

Answer 4

If you're using Entity Framework, you can try GeoJSON4EntityFramework. I've just started using it, and it's pretty neat. Here's an example, using a DbGeometry field called Boundaries:

var feature = new Feature(Boundaries);
var geoJSON = feature.Serialize();

Pretty simple.

Answer 5

Loading and displaying GeoJSON with Leaflet has been pretty well detailed here: http://leafletjs.com/examples/geojson.html