[GIS] Draw SQL server geometry records as graphics on map using ArcGIS API for WPF 2.4 -can it be done and how

arcgis-api-wpfgeometrysql server

I have records (of public festivals changing daily) in the in SQL server 2012 that have a geometry column (polygons where they occur). I need to show the records as graphics on a map using ArcGIS API for WPF 2.4. I tried researching it in the internet, but haven't got results. Does anybody know if can it be done and if so, how?

P.S. : I know there is the QueryLayer\QueryTask, but aside the fact that it's a bit too complex(in my opinion), I'm looking for something else

Best Answer

Ok, here is my suggestions on how to do this. Depending on your situation, you may have ESRI's MapIt, which seems to do most of the hard work for you. I looked at their site, and it doesn't seem like it is available anymore. If you have it, try it. If not, here is my longer version, which @kes alluded to in the comment.

Create a way to access the data in your SQL Server. There are 2 main ways to do this.

1: You can set up your connection through the System.Data namespace. There are a few options to connect so look through the documentation and pick one you like best. The trick here will be setting up your connection string. There are options to do either Windows Authentication with your SQL Server or store (or request) a user name and password on the client machine.

2: You can use something like the QueryTask or something like HTTPWebRequest to generate a request to a web API you set up that will then grab the information from your SQL Server and send it to your application. This can be done in place a direct connection from your application to your SQL Server. This would mean establishing a way for your API to send information back (e.g. JSON string).

In either case, you should also provide a way to get your geometry information to your application in a usable format. I tend to prefer well-known text (WKT). It is easier to read when troubleshooting but can end up getting long depending on your coordinate system and how many points a particular feature holds. You can choose whichever way to get your geometry and parse it in the application that you want. That is just my preference.

My suggestion would be to set up a stored procedure to return this value to either request method. This also eliminates the need to directly execute SQL on your SQL Server (e.g. SELECT * FROM MyTable). In case things change, it is easier to update the stored procedure than it would be client applications.

Now comes the tricky part. After you've made your request, you will have some manner in which your data comes to your client application. You will need to establish a way to parse that information into something. System.Data will typically return a DataSet or DataTable, which have rows and can be iterated over. The web API version depends on how you set it up, but typically would come back as a JSON string which you can use the JavaScriptSerializerClass or JSON.Net to deserialize into an object (you'll need to define a class here to hold the data). You then need to take the data either as is, or define a class to hold the information in a more workable manner.

This next part will generate the graphics of the information. You need to follow the API information on Graphics for ArcGIS for WPF. You will take the geometries you returned from your SQL Server to generate the graphics. I suggest reading up on the Adding MapTips portion of the documentation to add information to your graphics. You could also do things a little differently and query on demand. Basically, you add some sort of event handler than monitors where your mouse is or a click event. Once triggered, it will either take a unique identifier of the graphic that triggered it (needs to relate to the SQL Server), or it will take the mouse position (in map coordinates), and it will do another query (in the method set up before, System.Data vs API) to get the information.

The basic idea of this is you're creating your own graphics layer. The application will act as an interpreter to your SQL Server data, and it just parses and converts it into a geometry object the ArcGIS API for WPF can read and render. You still need to decide a rendering schema for the data, which can potentially be integrated into your SQL Server as another table that gets joined to your spatial query and interpreted by your application.

This can easily get more complicated if you want to add editing abilities into the data or any other more complicating things like spatial querying.

The basic flow of data in any of these is like this:

SQL Server (holds data) -> Stored Procedure (get data from Server) -> Call Procedure (API/System.Data) -> Application (make request...parse response) -> Render on Map (interpret response)

Disclaimer...This is for websites but should have translations to your WPF app

ASP.NET and WPF have a lot of crossovers. I find it is relatively easy to translate things done in one to the other thanks to the .NET framework. So instead of where I do the web stuff (hidden field, JS array, etc), you can probably skip all that. Plus, for the Terraformer project I mention, there are probably some .NET projects that work in a similar fashion. I am sure there are some open-source projects that can deal with topological/coordinate issues for .NET and ESRI probably has a few classes in their namespace, too.

Using ASP and Entity Framework

I created a site using this idea. Without posting all the code, here is the basic process I used to create my point graphics.

1: Since I am dealing with lat/long points, I have my table set up using the Geography spatial type instead of Geometry.

2: In my ASP.Net page...I used the Entity Framework Code First approach and attached my database to the Page_Init event to grab the information from my database.

3: Since the data is pulled out using a LINQ query, I loop through the query results and create objects in a new class (whose properties are modeled after my data table fields) and store those in a collection. This is extra and unnecessary, but I want it there in case I expand functionality.

4: I take all those objects and create a delimited list string that is in JSON form. I then take the string and pass it as the value part of my <asp:HiddenField/> that I have on my webpage.

5: Because all that happens before my page is rendered, when the page loaded event fires (e.g. $(document).ready(), domReady), the information is there. First thing my map script does is load that data from the hidden control into a JS array.

6: After I initialize my map object, I loop through the array and create Graphic objects. I use the lat/long to create the point, assign it the correct SpatialReference (SRID: 4326), and give it a renderer (simple diamond shape). From there, I create a blank InfoPopup/InfoTemplate/etc and just pass in the Name value for the header. For the Graphic attr property, I pass in the JSON object from my array. So the window that pops up on click will display the key-value pairs (e.g. Name = Blah Place).

So, I wanted to prove my concept to see if it was feasible to put together a map like this without needing an ESRI Server/GeoServer/MapServer in the mix. To expand further into polylines and polygons, I think I would try to incorporate the Terraformer project to interpret well-known binary Geometry objects from my SQL Server instead of trying to manually parse complex geometries.

Web Update

Since I kinda went a bit on the web side of this, I wanted to update my answer for those who may be looking to do this through the ESRI JS API or a similar API. I stumbled upon this library, Wicket, which promises to convert WKT to geometry objects for APIs such as ESRI, Google, Leaflet, JSON, etc. I think this can be very handy for web developers who may stumble upon this question.

Related Question