2

I am modifying a .Net (C#, MSSQL) system to add some geography that I'd like to display on a leaflet map.

I had originally expected to save GeoJSON directly into text strings in the DB, but after seeing some SQL2008 spatial types, I wonder if I'd be better served by keeping it as WKT or GML so I can run SQL-Spatial queries against it and use C# native objects.

But then, I need to get stuff into GeoJSON or some other leaflet-compatible format.

I realize there are utilities to do the conversion (Convert GML to GeoJSON), but I can't use that on the fly to serve content.

Am I barking up the wrong tree? Any chastisement is welcome. =)

Improve this question
6
  • Are you against installing additional software? Geoserver can handle MSSQL spatial types in their native format and serve that out however you need.
    – DPierce
    Commented Jul 16, 2013 at 18:31
  • @DPierce In this particular case, yes, kind of opposed. We try to limit the 3rd Party footprint as much as is reasonable. Setting up another server with a different runtime to host 500 shapes is not going to fly with the boss. Edit: I realize I didn't make it clear before that there's an existing system I'm bolting onto. I'll fix the original question.
    – Michael come lately
    Commented Jul 16, 2013 at 22:10
  • 1
    I think the best answer for you depends on your situation. If you don't expect data in your database to change often (or ever), and since you've only got roughly 500 shapes, the easiest thing to do will be to store the data in both formats. Run your spatial queries against the spatial types the DB understands, and then serve the pre-converted geoJSON representation. Otherwise, you'd need some converters. If you're comfortable writing C#, it's possible to roll your own, and I can help you do that.
    – Jeff-Meadows
    Commented Jul 19, 2013 at 18:45
  • @Jeff-Meadows I had not considered storing them both; if I have to, perhaps I will. On the other hand, I will happily write C# and parse WKT into geoJSON. Does projection become a problem, too, or am I safe there?
    – Michael come lately
    Commented Jul 22, 2013 at 13:15
  • Projection is only a problem if you're trying to change it. GeoJSON can specify a projection, but it defaults to WGS84. To be honest though, I'd store both formats unless you've got a very good reason not to - why write the extra code, and exercise the server on every request? Don't think of it as redundant, think of it as pre-caching =)
    – Jeff-Meadows
    Commented Jul 23, 2013 at 21:47

1 Answer 1

Reset to default
2

The easiest thing to do will be to store the data in both formats. Run your spatial queries against the spatial types the DB understands, and then serve the pre-converted geoJSON representation for consumption by your Leaflet client.

Note that this solution is best suited for data that won't change frequently and that has a (relatively) small number of geographies (such that they're all stored in the database ready to serve).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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