Can Geoprocessing be done using SQL Server?

[Mark_Hotz]

I remember years ago (before spatial databases were readily available) that one could not perform any geoprocessing on spatial data stored in a database that was using ArcSDE.  All spatial operations had to be performed only using the GIS (in our case ArcGIS Pro).  However, with newer versions of Oracle Spatial and SQL Server Spatial has this since changed?  Can one perform geoprocessing functions within SQL Server now, or does one still need to use ArcGIS?

What is one restricted to do solely in SQL Server, and what should be the sole domain of ArcGIS?  I know we can go into SQL Server and update tabular data that is in a geodatabase, but what happens if one deletes a GDB record in SQL Server?  Does the point/line/polygon also delete?  Or is one's data now corrupted like it was in the old days?

SQL Server Spatial can store spatial data better than it could in the past (actually I don't think it could store spatial data in the past), but where does data storage end and data manipulation begin with spatial data being stored in SQL Server?  I'm just trying to set up ground-rules for our spatial data stored in SQL Server to hopefully prevent the wrong people from making regrettable edits in spatial data...to avoid all of the usual headaches that will undoubtedly occur.

Thanks

[KoryKramer]

Sounds related to ArcGIS Pro Roadmap - July 2019 

Geoprocessing Leveraging Spatial Databases - New options to run certain geoprocessing tools like Buffer, Spatial Join, Select, or Intersect as queries in databases that support these operations, which will result in improved performance.

[Mark_Hotz]

Thanks Kory...appreciated.  My thinking now then is that there are some spatial analysis tools available in SQL Server spatial (like you pointed out), each of which will result in a new feature class.  But if one tries to delete a record from a file geodatabase using only SQL Server then should I assume that the spatial data will probably be corrupted?

Would you know if these basic spatial analytical tools are native to SQL Server, or does SQL Server use ArcGIS in the background to perform these tasks?  I guess another way to ask this would be, "can one perform these analytical tasks within SQL Server if there is no ArcGIS on that machine?"

[JoshuaBixby]

Mark Hotz‌, regarding your font, are you purposely picking a larger font or is it just something happening with your browser/client?  I can't speak for others, but when I read your posts the font is large, almost too large to read easily.

Back to the content of your post, it almost sounds like you are asking about SQL access to enterprise geodatabase data—Help | ArcGIS Desktop 

[Mark_Hotz]

Thanks Joshua...I was choosing a larger font, but in my browser it doesn't look too bad at all.  However, I'll use a smaller font here.

I'm not quite sure I'm asking about SQL access to enterprise geodatabase data, at least after reading that web page on the subject.  In that page there are references to avoid certain actions though, so this tells me that I am on the right track though.  Perhaps I should rephrase the question. 

What we have tried successfully here is because our Enterprise Geodatabase is integrated with our SQL Server we are able to see, open and calculate those geodatabase records from within SQL Server itself.  So only opening SQL Server we are able to update "some" things (like attributes) in the geodatabase, but these changes are not altering the spatial data, just the attribute data.

What concerns me here though is whether our SQL Server DBA can accidentally delete a record in that geodatabase from within SQL Server, which if possible in my mind would mean that the data will be corrupted.  But I want to make sure before I try to document this in a governance document regarding spatial data.

Have you ever tried to edit and/or delete an enterprise geodatabase record just from within SQL Server?  I would be impressed if one could delete an Enterprise geodatabase record in this manner as that would be altering the spatial data itself...and who would need ArcGIS (or ArcGIS Pro) if this type of work can be done in SQL Server?

[Asrujit_SenGupta]

What type of data can be edited using SQL?—Help | ArcGIS Desktop 

This clearly defines what can be edited and what can be...using SQL...

[Mark_Hotz]

Asrujit:

Thanks for the link...this looks like something I need to read, and will do so today.

[AndreaB_]

Hi @KoryKramer ,

Since the Roadmaps from the past get taken down and replaced with the new roadmaps (apparently?) this link is broken. Do you have more info regarding this option it was talking about? Where the info is in the help documentation etc? I would like to read about this.

Thanks!

[ThomasColson]

Attribute rules mimic the functionality of the native spatial functions in SQL. I used to do extensive geoprocessing through the use of triggers, but the Pro requirement to enable archiving in order to support offline editing rendered all of that obsolete. I recently discovered how to use Introduction to attribute rules—Geodatabases | ArcGIS Desktop  to do spatial data management operations on the fly, and am quite happy with the functionality. Regarding editing the data in SQL directly, while supported by ESRI, strongly discouraged due to the interdependence on other parts of the enterprise architecture. I only do it when needing to perform surgery on a DB, and actually have a case open right now and am waiting ESRI approval to edit a SQL table directly. 

How To: Insert geometry from XY coordinates using SQL  is a good example of how to to bulk inserts into SDE

[Mark_Hotz]

Thomas:

Thanks for this info...this really helps.  I'm not a SQL Server person, but our SQL Server people here are also not GIS trained or experienced.  So I guess the rule that will be documented then is to refrain from spatial editing using SQL Server, and if necessary only work with attributes in that environment.

We're converting a lot of data and the resulting feature class is routinely missing records (but we know what the reason is and there is no way around this during the conversion process), and it would be so much easier if the SQL Server DBA could just automate a SQL process to fill in the blanks.  However, if they cannot do this then it will require a Python script to perform that task in ArcGIS Pro...or perhaps even doing this in FME (if possible).  It just seems to work faster in SQL Server though.