Automated map service updates from SQL Server

4122
5
Jump to solution
09-17-2015 11:49 AM
ChrisSmith7
Frequent Contributor

I'm hoping to obtain some advice/best-practices recommendations from the community on automatically updating map services from SQL Server... Here's a bit of the back story:

I currently utilize ArcGIS Server to publish map services and spatial utilities that are consumed by a JS/ASP.NET application. Unfortunately, I am unable to utilize SQL Server spatial datatypes (not by my choice - maybe it will change in the future), so, when I display data using the JSAPI, it's through AJAX SQL calls that return a JSON object, or, through binding JSON data, obtained from a SQL proc call, to the map service template through a PK (say, to create a choropleth).

This works fairly well, but I would really, really like to tap into the map services' inherent abilities to pipe-in data for consumption, like serving-up on-demand features based on map extent/zoom level, etc. If you can imagine, sending large JSON objects can be problematic, and it leads to performance issues in the users browser, like when trying to show tens of thousands of points.

I can make connections to SQL through ArcGIS to access a table with the data I need - x/y coordinates are also available for spatial representation (I need to show points on the map). I came across this thread that would seem to do what I need:

Auto-updating map service via REST in ArcGIS for Server? - Geographic Information Systems Stack Exch...

It would probably work well if I had spatial datatypes enabled, but I since I do not, would I just create a Python process to kick-off at set intervals that would pull data from the table, then create a feature class and overwrite the map service on the server?

Any ideas would be appreciated!

1 Solution

Accepted Solutions
JakeSkinner
Esri Esteemed Contributor

Hi Chris,

If you are only working with point data, you can create an XY Event Layer from the SQL table.  You can then publish the XY Event Layer as a ArcGIS Server Map Service.  As the SQL table is updated on the back end, the service will automatically update.

Adding x,y coordinate data as a layer—Help | ArcGIS for Desktop

View solution in original post

5 Replies
JakeSkinner
Esri Esteemed Contributor

Hi Chris,

If you are only working with point data, you can create an XY Event Layer from the SQL table.  You can then publish the XY Event Layer as a ArcGIS Server Map Service.  As the SQL table is updated on the back end, the service will automatically update.

Adding x,y coordinate data as a layer—Help | ArcGIS for Desktop

ChrisSmith7
Frequent Contributor

Ahh! This looks promising! Thanks - I'll let you know how it goes!

ChrisSmith7
Frequent Contributor

Had a chance to get back to this task - your suggestion worked beautifully! I also had a good response when I floated spatial datatypes again with our DBAs, too! Now if only it were Friday...

0 Kudos
ThomasColson
MVP Frequent Contributor

I'd take a hard look at why you can't use the SQL spatial types. Is that an organizational administrative requirement? Esri binary is pretty limited in functionality outside of the Esri stack, whereas with the spatial types (Geography or Geometry), the opportunities for backend DB updating and re-packaging of data are pretty limitless. For example, using SQL spatial, I'm able to pull raw GPS Collar data from several species of animals, perform some post-processing, build some time/distance tracks, and delivery a daily movement map of each animal to users via Portal, with no "human" intervention at all. No way I could pull that off with Binary storage. The same could be done with GeoJSON as your output, there are several examples out there of folks converting SQL Spatial to GeoJSON.

As an alternative, you might want to look at CartoDB, which has a very feature-rich API and more elegantly supports real-time updating via JSON (both directions), and IMHO, is far easier to code against than the ArcGIS REST API.

ChrisSmith7
Frequent Contributor

It's a unique set-up... Maps are really a small aspect of what we do, which I'm trying to change! I'm really trying to push spatial types through, now, which was my eventual intention when going with ArcGIS Server, but things were complicated with scale and also software version (e.g. SQL 2005) at the time. So, I developed a solution that would work without a need for spatial types, happily (or unhappily?) isolated from SQL, but now it's starting to hurt!

We looked at CartoDB, but at the time, it was still pretty nascent, and it used PostgreSQL as its back-end, which nixed it from consideration. It does seem quite capable.

0 Kudos