8

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]
    }
};
3

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"
  • Do you know of sample code for accomplishing that with GDAL's .Net API? – Harlan Wescott Oct 8 '15 at 18:35
  • @HarlanWescott - No, sorry I don't. – Chad Cooper Oct 8 '15 at 19:14
1

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

  • 1
    Can you update the answer then? It has been over a year. – JP Hellemons Apr 18 '16 at 13:21
0

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/

  • The question was tagged as being for C#, not JavaScript – SteveC May 27 '16 at 19:49
  • @SteveC - agreed, translating it should be easy enough for the OP. Better have a solution that shows the way it could be done, then having none. – Dementic Jun 25 '16 at 9:34
0

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.

-1

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

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.