3

At work we are using ArcGIS 10.1 for fairly simple spatial data. We don't use the more advanced items such as topology that are available in Arc. We have a couple SQL Server databases for other kinds of data and have built some tools for taking that data and visualizing it in Arc. There is some data that we use a File Geodatabase for that I want to put into SQL Server, so that it can be more closely tied to the other data we have, and so that when we have 2 or 3 people working in different parts of the database, it doesn't lock the whole database from editing (like the File Geodatabase we are using now does).

However, at the moment we don't have an Arc Server license, so although I can connect to the data, I can't edit it through Arc. I have looked at other topics on here about SQL Server Spatial and Arc (this one for example), but I haven't found out how to successfully edit SQL Server data without a license.

My guess is the process would look something like this

  • create a local copy of the features I want to edit (maybe as shp files, or a file geodatabase)
  • edit as necessary
  • use external python libraries to delete the original features on the server
  • use shp2sql to upload the edited features

I feel like there must be tools that do this already, but maybe not for the price we want to pay. An Arc Server license has been talked about here, but I have no idea when it will actually be implemented, so I don't want to hold my breath. I know that there are other platforms that would be perfect for this, like QGIS, but that would be harder to convince people of than getting an Arc Server License.

Can someone point me in the right direction?

  • possible duplicate of Do I need ArcGIS for server to edit my MS SQL Server database? – Conor Feb 2 '15 at 17:12
  • The only suggestion there is GIS Squirrel, or an Arc License. I would have thought there were other ways... – cndnflyr Feb 2 '15 at 17:15
  • What is your end-users' workflow for editing/viewing data? How often do they need to see updates to their data in production? Are they pretty used to ArcGIS or are they open to learning new platforms? These things affect the viability of alternative answers such as the ones you suggest. – Conor Feb 2 '15 at 17:24
  • Editing is done by the 3 of us in the GIS group. We have about 25 co-workers who would only be viewing the data. As long as the 3 of us are able to edit/use/view the data comfortably, that is all we need. – cndnflyr Feb 2 '15 at 17:27
  • The data consists of maybe a few million line segments. Sometimes we have a major update where we add many thousands of lines at time, but usually it's 100 here, or a hundred there that need a rotation, or replacement. – cndnflyr Feb 2 '15 at 17:29
2

We use SQL Spatial native data for 90% of our spatial analysis.

Our catch is we have to edit the data in a Geodatabase then use FME Workbench (through our Data Interoperability Extension that comes with our ESRI enterprise license agreement) to move the data into SQL Server as native spatial data.

[EDIT] The ability to edit spatial data within ArcMap gives all the editing capabilities of that software. This to me is important as keeping the SQL Server / Microsoft / ArcGIS technologies together works best for consistency, support, etc.

FME simply bridges that gap, and gives us Spatial ETL solutions that neither Microsoft nor ArcGIS provide on their own.

I started looking at how to use a server solution, but it wasn't going to work basically due to the fact that I needed Server to edit native SQL spatial data. Another consideration at the time, and I don't know where this ended up because I stopped looking, was if the 'feature data service' was going away or not...

I wrote some more info on how we use FME to update our SDE, but this can easily apply to updating a production SQL environment as well: What is the best way to update a production ArcSDE database from staging?

  • Do you recall what the server solution limitations were that stopped you from pursuing it? – cndnflyr Feb 2 '15 at 17:17
  • Basically the fact that I needed Server to edit native SQL spatial data, and was the 'feature data service' going away or not, etc. etc... – DPSSpatial Feb 2 '15 at 18:36
  • I'm going to need to look into the Interoperability extension further. I've definitely heard of FME a lot, but I've never thought it was necessary since there are so many other tools that can convert data to what is needed. But it sounds like it's the seemless integration with the various parties that makes it stand out. Thanks for the tips! – cndnflyr Feb 2 '15 at 19:31
  • @cndnflyr your welcome, and feel free to get in touch if you have more questions! – DPSSpatial Feb 2 '15 at 19:47
1

It appears that you are asking if it is possible to use ArcGIS middleware to edit a SQL database without purchasing ArcGIS server. With your existing set of tools, it is possible if you are fairly experienced with SQL Server. The cleanest way to go about this is to manage it almost all through SQL. You will want your own homegrown versioning because you don't get versioned geodatabases without buying ArcGIS Server.

  1. With a standard or advanced ArcGIS desktop license you can set up a SQL Server Express instance on your desktop (no ArcGIS Server license needed). Each of your 3 editors gets his/her own SQL Server Express instance.

  2. Use Editor Tracking or there are several add-ins to record each time you edit your personal ArcSDE instance. Easiest way I think would be to record the user and the time of their change in a column somewhere. There are several ArcGIS for Desktop addins that accomplish this as well as editor tracking.

  3. Use linked servers and some fancy scheduled SQL to compare both the schema and the data between your editor ArcSDE instances (for the data itself, you'll need to use equals for nonspatial columns and stequals for spatial columns). For your quick and easy homegrown versioning, taking the last person to update a row may be sufficient, but you can write more elegant versioning rules in your SQL compare scripts.

  4. Using the results of #3, update every one of your editors' personal ArcSDE instance with the latest "version" of the data

  5. Use a scheduled SQL dump script to push out of the personal desktop ArcSDE instances of your editors and into your SQL Server Native production instance.

Really though, cooking up something like this would cost way more labor and time than just buying a squirrel license or teaching your editors to use QGIS.

EDIT to address mapBaker's questions:

Is the SQL Server Express instance you're referring to running hosting / containing a Geodatabase?

Yes, in this paradigm each desktop runs its own SQL Server Express instance with a geodatabase installed on it. This is permitted with an ArcGIS desktop license (As long as it is an ArcEditor or ArcInfo license!).

Editor tracking is a is a function of the Geodatabase.

You can leverage this functionality with the ArcSDE 'single use' instance discussed above. You set it to store editor tracking data in SQL tables, which you then use to manage a rudimentary homebuilt "version" to spit out your end product using pure SQL.

What considerations are there for a versioned Geodatabase compared to what SQL Server can see on the back-end, which is what you're suggesting would be used to compare schemas, etc.?

ArcGIS has pretty solid versioning built in to ArcSDE. They use alpha and delta tables and a versioned view as well as difference cursors to manage it. For insight on this check out some of user Ragi Yaser Burham's posts on this site, he offers good explanations on it.

A small shop like OP's may not need something that sophisticated. You can use SQL by looking at unique ID's, editor time and date, attribute values, and row/column schema compares and counts to find out how tables change between versions. It's more difficult in this way and requires more upfront coding, but it is manageable and possible.

  • is the SQL Server Express instance you're referring to running hosting / containing a Geodatabase? Editor tracking is a Geodatabase is a function of the Geodatabase. What considerations are there for a versioned Geodatabase compared to what SQL Server can see on the back-end, which is what you're suggesting would be used to compare schemas, etc.? – DPSSpatial Feb 2 '15 at 18:43
  • @mapBaker I added some answers to your questions. Hope this clarifies a bit – Conor Feb 2 '15 at 19:29
  • @Conor that would be pretty smooth, but you're right, it's probably out of the realm of what I can do in a reasonable time and budget. However, I think I'll look into what a simplified version would entail where the data that I want to edit is brought down into a File Geodatabase and then updated in SQL Server from that when the editing is done. I'm already getting my hands dirty with plugins to interface with the other databases we use, so this might not be much of a stretch. It's always the validation that can prove difficult though... – cndnflyr Feb 2 '15 at 19:37
  • Maybe a column that has the username of who has brought it down for editing, and then when that user uploads it, the username is taken out and remains empty when it's available to be edited. – cndnflyr Feb 2 '15 at 19:42
  • @cndnflyr FME would let you carry that username back to SQL if need be... – DPSSpatial Feb 2 '15 at 19:48
0

I asked this a long time ago, but since 1000 views is a special occasion I'll post how we ended up doing it.

I ended up writing a number of tools in Python which used pypyodbc to INSERT, and UPDATE our products on SQL Server. When it was time to Edit a specific set of polylines, they would be brought down as a local SHP file, deleted in SQL Server, edited, then uploaded.

The main reasons for going this route was because we were already committed to using ArcGIS Desktop and we didn't want to spend all the money for a full ESRI solution. It's one of those things that could be done much better, but this seemed to hit the right spot of simplicity, cost and speed of implementation.

  • Thanks for updating. Seems like a simple and reasonable approach and has the added benefit of performance gain on the vector edits because all edit work is done entirely locally. Do you do the delete operation on "check out" or "check in"? On check out seems it would risk the loss of data. – Conor Sep 25 '16 at 17:02
  • Also I find it slightly amusing that your solution is quite similar to how Git works. At some point you should consider forgoing the database thing entirely for your edit workflow and just have your main database as a Git repository with shapefiles which is then through a build deployed to a database through ogr2ogr and SQL load scripts to a SQL database which is then served to the end user. You would then get the super robust conflict resolution that Git offers, all the edit tracking you would ever want, and the great ability to walk back to your data at any point in time. – Conor Sep 25 '16 at 17:17
  • If the delete is necessary, it's done after checkout. I had to depend on there just being 3 people who work side by side, so it's pretty easy to avoid conflicts. This wouldn't scale up well. I also put in an option where we can download, edit, upload and it will look for the features with the same metadata and just do an UPDATE on the geometry. I have been intrigued with Git for awhile and have been using it for code, and would like to use it more extensively. It has it's own methodology that I didn't understand at the time, I had to stick with what I knew (ArcGIS, Python, SQL Server) – cndnflyr Sep 27 '16 at 2:05

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.