Updating SRID values used by Feature Classes
I have noticed since upgrading to ArcGIS 10.4, the SRID values of our feature classes have changed. All of our production feature classes use NAD 1983 (2011) Iowa State Plane North and are managed in SQL Server 2014 Standard using Geometry as the shape. The SRID for this spatial reference in 10.3 and prior is 103045. Starting at 10.4 the SRID is now 6463. Granted, our end users will never know the difference, and all the properties of the spatial reference are identical. However, I notice a huge difference when trying to run the same spatial queries within SQL Server that use multiple tables, most notably, for those feature classes that are created in 10.4, trying to apply the same spatial query doesn't work at all because the SRID value of the feature class created in 10.4 is different from those created in previous versions.
Below are examples trying to execute an intersect spatial query using a point feature class created in 10.3 (103045) and one created in 10.4 (6463) against an underlying polygon feature class created in 10.3 or prior (103045).
Is there any way of updating the SRID of a feature class without having to re-create the feature class in a current version? This wouldn't be too terrible except any relationships based on Esri system fields, for example, the GlobalID field when using attachments, would be very difficult to re-create because they would be re-generated for the new feature class.
One of the benefits of us maintaining our data in SQL Server is the ability to do these type of spatial queries within the database. By having multiple SRID values for the same coordinate system, we are losing the ability to run these types of queries.
Both feature classes (point shown below and underlying polygon) both have 103045 as SRID --- values are returned in the spatial query where features intersect
Same point feature class, however, created in 10.4 with SRID equal to 6463 and underlying polygon in 103045...no records are returned
Hopefully, someone from Esri can chime in on this...but I found the following workflow seems to work in changing the SRID stored for a feature class from one that was prior to 10.4 to one that is current. I'm sure if someone from Esri does chime in, they will mention that this workflow is not supported since you're monkeying around with back-end tables, but I don't see any other solution that doesn't involve re-creating the feature class, and loading data into it. And, while this may work for some, we use a fair amount of attachments and related tables which often use a GlobalID field as the parent key. These GlobalID values will get assigned new values when loading into a new feature class, so then you are stuck with trying to create a cross-reference between old and new features, personally, not an ideal scenario.
In our situation, most of our enterprise feature classes were created prior to 10.4 and have a SRID value of 103045. Starting at 10.4, the same coordinate system (NAD 1983 (2011) Iowa State Plane North) now has a SRID value of 6463. To our end users, they will likely never notice the difference, however, as an admin and someone that uses spatial queries within Sql Server for a fair amount of analysis and querying, I notice this as a big issue, because queries involving geometries within Sql Server only return values when the tables have the same SRID. So...it is very important for us to have our feature classes using the same SRID values across the board.
With that being said...here is the workflow I found that seems to work (I apologize for the length, but for anyone who may stumble across this when searching for a similar issue, I wanted to be as detailed as possible)...
- I created a test feature class (using Desktop 10.3) that uses the old SRID for NAD 1983 (2011) Iowa State Plane North ... 103045. I created a few points in order to demonstrate that the feature class and features are indeed using the SRID equal to 103045. I registered the feature class as versioned, and enabled archiving to mimic our production environment. I did this to make sure all bases are covered when it comes time to create/edit new features (delta table inserts/updates) and archive data.
- If I query Sql Server to expose the SRID used by the features, I can also confirm that the SRID being used is 103045.
- So...this is how I went about changing the SRID being used by the feature class from 103045 to 6463...first of all, you need to do a little research on the SDE tables within your database. These are the tables you need to make a few notes on:
- SDE_table_registry: only needed if your feature class is versioned. This table will tell you numbers of the associated delta (a, d) tables. In this example, the delta tables associated with my test feature class are 'a15059' and 'd15059'.
- SDE_spatial_references: this table will tell you the spatial references used within the database. The 'srid' field is the primary key on the table which is a foreign key in tables below. The 'auth_srid' is the SRID that is used by the feature class. The 'srtext' field is a text description of the SRID. In this example, the srid values of '3' and '4' are the two entries for the different NAD 1983 (2011) Iowa State Plane North coordinate systems, with '3' being the 103045 SRID and '4' being the 6463 SRID.
- SDE_layers: this table contains which SRID is being used for the base table and archive table within the 'srid' column (the layer_id column value will correspond to the SDE_GEOMETRY*** table). Note the layer_id. This is the primary key for the table and can be used later when updating to key on the rows.
- SDE_geometry_columns: this table also contains which SRID is being used for the base table and archive table within the 'srid' column.
- Now that you have the following: delta table names (the 'a' table is all that's needed), srid values (3 (old), 4 (new) in this example), and what tables store these SRID values (SDE_layers, SDE_geometry_columns), you can start updating tables to migrate to the desired SRID value (6463 (4) in this example).
- The first thing to do is modify the constraint on the base table, 'a' delta table (if applicable), and archive table (if applicable). The constraint will only allow features to contain a SRID value equal to what was defined when the feature class was created, in this case 103045. On each of the base table, 'a' delta table, and archive table:
- Expand the table properties to view the constraints. Right-click on the constraint and select 'Modify'.
- Change the highlighted properties from 'Yes' to 'No'. Also, note the constraint value is forcing any records within the table to have a SRID value equal to the SRID defined when creating the feature class. Change the SRID value to the new value (second screenshot). Close and save the table. Do the same for the 'a' delta table (if applicable) and archive table (if applicable).
- With the constraint modified to NOT check existing data, and NOT enforce on inserts and updates, you can run update queries to update the SRID values within the base table, archive table (if applicable), and SRID key values within the SDE_layers and SDE_geometry_columns tables. You can run an series of update queries like what is shown below.
- Update the 'srid' value within the SDE_layers table to the new SRID value referenced in the SDE_spatial_references table using a where clause based on the layer_id values found from the SDE_layers table.
- Update the 'srid' value within the SDE_geometry_columns table to the new SRID value referenced in the SDE_spatial_references table using a where clause based on the table names.
- Update the SRID value being stored by all features within the base table to the new SRID value (actual wkid)
- Update the SRID value being stored by all features within the archive table to the new SRID value (actual wkid) --- if applicable.
- Check the affected table to verify the updates:
- Base table SRID values are updated from 103045 to 6463
- Archive table SRID values are updated from 103045 to 6463
- SDE_geometry_columns table 'srid' values are updated from 3 (103045) to 4 (6463) based on SDE_spatial_references table
- SDE_layers table 'srid' values are updated from 3 (103045) to 4 (6463) based on SDE_spatial_references table
- Change the constraints values back to 'Yes' on the base table, 'a' delta table (if applicable), and archive table (if applicable). Make sure the SRID value has been updated to the new srid. Close and save the table.
- Check your feature class properties. The feature class should now show that the Geometry being stored is in the new SRID.
- Try adding / updating / deleting features within ArcMap. You shouldn't encounter and constraint errors. Query your delta table (if applicable) and archive table (if applicable). You should see that the features are using the new SRID value.
This seems to have worked for me in migrating existing features from a previous SRID to the current SRID. This is pretty hacky in that you are messing with back-end tables, but this seems to prevent having to try and load features from one feature class to another. If any Esri folks want to chime in and tell me of a better way, I'm all ears ... or if this will cause some sort of catastrophe within our geodatabase, I'd appreciate that as well.
