Dangers of Migrating Spatial Storage Types

893
3
Jump to solution
02-01-2023 12:59 PM
Labels (2)
daniel__j
New Contributor II

I am attempting to update a FeatureClass's property using a SQL query. (DB version is 10.3.1) Such as:

UPDATE <versioned_view> SET <field> = <value> WHERE objectid = 1;

However, each time I receive the following error:
"User must call edit_version before editing the view."

 I have found that the reason for this is because the feature class in question stores it's spatial data in Esri's binary format (Thanks to this other forum post).

I know it is possible to migrate data formats, but I'm wondering how safe this operation is. New features are added to our database daily using ArcMap by our mapping team, and our ArcGIS rest service is constantly being queried. Making a breaking change to storage formats would be a big problem.

Is there a way to update a property using SQL without changing the spatial data storage type?

Note: This question is basically a continuation of my previous question.

Thanks in advance, this forum as been so incredibly help as I try and figure out how ArcGIS works.
- Daniel

0 Kudos
1 Solution

Accepted Solutions
MarceloMarques
Esri Regular Contributor

Question: Is there a way to update a property using SQL without changing the spatial data storage type?

Answer: No, there is not.

An overview of editing versioned data using SQL—ArcMap | Documentation (arcgis.com)

You need to follow the instructions in the link below to migrate the spatial data storage type.

Data migration from one storage type to another—ArcMap | Documentation (arcgis.com)

I suggest you create a backup of your database and restore the backup in another server and test the migration there first.

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov

View solution in original post

3 Replies
MarceloMarques
Esri Regular Contributor

Question: Is there a way to update a property using SQL without changing the spatial data storage type?

Answer: No, there is not.

An overview of editing versioned data using SQL—ArcMap | Documentation (arcgis.com)

You need to follow the instructions in the link below to migrate the spatial data storage type.

Data migration from one storage type to another—ArcMap | Documentation (arcgis.com)

I suggest you create a backup of your database and restore the backup in another server and test the migration there first.

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov
George_Thompson
Esri Frequent Contributor

Like Marcelo said, it is not possible to update with SQL.

As for the migration, it is safe to use. I would also highly recommend a backup before running the process.

One gotcha I have seen with that tool is space: make sure that you have enough space available in the RDBMS to expand during the process. The tool basically "copies" the table in memory then does the conversion finally deleting the SDEBINARY version.

I would HIGHLY RECOMMED this is done during a maintenance window to remove any locks from the tables in question.

--- George T.
daniel__j
New Contributor II

Thank you both for taking the time to help me out, I really appreciate it. We will to test, backup, and remove all locks before taking these changes to production.


0 Kudos