3

I have polygon data in SQL Server 2012 that I need to get onto a Leaflet application - we're not talking thousands of polygons, but simplified U.S. counties where only one state will be displayed at a time. The database gets updated every 5 minutes and the map needs to reflect that. I know there are several ways to get the data to geojson/json and put that onto the map (such as the jQuery $.getJSON method). We've thought about creating a web service to pull the data and do the conversion to geojson, then get it onto the map with Ajax. The concern there is that this sounds slow and CPU-intensive. Thoughts on that?

I'd lke to know if there are any methods that would allow me to more directly query SQL Server (from within the application perhaps) and get the polygon data into my Leaflet app, keeping in mind that it needs to be refreshed every 5 minutes.

|improve this question
  • Why are you converting the geometry to GeoJSON? Wouldn't having a static GeoJSON file(for the geometry), and a query for only the dynamic variables be better? – Devdatta Tengshe Dec 5 '13 at 13:42
  • @DevdattaTengshe True, indeed the geometry stays the same and attributes of each county are dynamic. So are you suggesting querying the dynamic data only directly from the application? – Chad Cooper Dec 5 '13 at 14:02
  • I haven't tried it yet, but we are working to do so: SQL Server offers the possibility to publish your database directly as a WebService (old fashioned SOAP Style). If you publish a view where you offer Geometries as a WKT, it should be possible to write a leaflet layer that renders the WKT from this service. I think there isn't any ready to use solution for this, but it would be a nice project to get SQL Server spatial data quickly into the web. – Jürgen Zornig Dec 5 '13 at 14:45
  • Hi Chad, Have you come across any solution? we also want to implement same. like Filter the data in application then return the result as GeoJson to populate on the leaflet map. – Pragnesh Patel Apr 11 '14 at 15:33
2

To expand on my comment, let me talk about a similar application I had developed to showcase results of an election. There was some data which was updated periodically in the database (i.e. the Results), while most of the data, including the Geometry did not change.

  1. My JavaScript Application used a static GeoJSON file for the static data and Geometry.

  2. I had a C# windows Service on the Server. It periodically queried the database, and dumped the dynamic parts as a JSON file in the WebServer.

  3. My JavaScript Application, periodically queried for the JSON file with a timestamp attached to the GET request, so that it got the latest data. Once the Ajax call received the data, it changed the symbology of the Vector layer.

Part 2 was developed in this way, instead of a normal web service, to reduce the strain on the database. A conventional service would be hit by all clients, and hence there would be many calls to the database. We wanted to avoid that, and cache this data. Hence we proceded with the architecture given in step 2.

|improve this answer
  • A workflow based off this answer was the end solution. Static geojson files of county boundary geometries, with a Azure webservice spitting out json. Lots of JQuery and underscore.js to join the incoming webservice json onto the geojson by county. – Chad Cooper Apr 18 '14 at 1:26
  • 1
    @ChadCooper: Thanks for pointing chad. can you share any code for converting json to geojson? – Pragnesh Patel Apr 22 '14 at 16:27

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.