Registering a table containing an ST_Geometry column with ArcSDE

You can use the ArcSDE administration command sdelayer –o register to manually register a spatial table with ArcSDE. Once it is registered with ArcSDE, the spatial table is called a layer.

Note:

ESRI recommends you create feature classes in ArcGIS. Creating feature classes in ArcGIS ensures they are properly created. ESRI understands, though, that some SQL users and application developers want to have another option when working with spatial tables. Therefore, ESRI supports registering external spatial tables created using SQL or a third-party application.

Keep in mind that for a table to be registered with ArcSDE, it must meet at least the following criteria:

• It must be owned by the user registering the table.
• It must have a single ST_Geometry column.
• It must have no other columns of a user-defined type.
• It must have a single type of geometry (points, lines, or polygons)
• It must have an integer, unique, non-NULL column suitable as a registered object ID column.
• All records in the table must have the same spatial reference ID (SRID) or no SRID.
• If you specify an SRID at the time of registration, the SRID must be present in the geodatabase.
• Its name cannot be mixed case, start with a number, or exceed 128 characters (160 characters in Oracle).

Note:

If the table does not contain any data and you register the table with ArcSDE but do not specify a valid SRID, the default SRID is used. For PostgreSQL and Oracle tables with ST_Geometry columns, the default is 0. The SRID 0 is present mainly for testing purposes; it does not actually assign a spatial reference. If you later try to add features that have a valid SRID other than 0, it will fail because the SRIDs are not the same. Once an SRID is assigned to a table, it cannot be changed; you have to delete the layer and re-create it with the correct SRID.

For additional information on using the sdelayer command, consult the ArcSDE Administration Command Reference provided with the ArcSDE component of ArcGIS Server Enterprise.

Steps:

1. In SQL, create a table, comm_bldgs, from the existing buildings feature class you created in Workflow: Using SQL with existing feature classes. This creates the comm_bldgs table with the same definition (such as the same columns and data types) as the buildings feature class.

   CREATE TABLE comm_bldgs 
   AS SELECT * FROM buildings
   WHERE subtype = 'COM';

2. When you register the comm_bldgs feature class with ArcSDE, you will use the same SRID as was used for the buildings feature class. To find out what that SRID is, use sdelayer –o describe_long.
   Tip:

   You could also query the ST_GEOMETRY_COLUMNS table in Oracle, the geometry_columns table in Informix and DB2, and the sde_geometry_columns table in PostgreSQL to get the SRID.

   sdelayer –o describe_long –l buildings,shape 
   –u <user> –p <pw>  –s <server> [–D <database>] 
   –i <service_or_direct_connect_string>
   
   Layer        Administration Utility
   
   Layer Description : <None>
   Table Owner : me
   Table Name : buildings
   Spatial Column : shape
   Layer Id : 15
   SRID : 3

3. Register the feature class with ArcSDE using the sdelayer command. Specify the SRID from the last step using the –R option. You can also specify the extent of the layer using the -E option.

   sdelayer –o register –l comm_bldgs,shape –e a -t ST_GEOMETRY
   –C OBJECTID,SDE –R 3 -E -690.389,-512.144,610.389,600.144
   –u <user> –p <pw> –s <server> [–D <database>] 
   –i <service_or_direct_connect_string>  

   Note:

   If the GEOMETRY_STORAGE parameter under the DEFAULTS keyword in the DBTUNE table is not set to ST_GEOMETRY, you must also provide a keyword with the -k option.

This has registered your table with ArcSDE, making it a layer. This adds a record to the LAYERS, GEOMETRY_COLUMNS, COLUMN_REGISTRY, and TABLE_REGISTRY system tables in Oracle, DB2, or Informix or the sde_layers, sde_geometry_columns, sde_column_registry, and sde_table_registry tables in PostgreSQL. At this point, you are able to view, select, and perform nonversioned edits on the feature class in ArcGIS Desktop.

To use geodatabase functionality in the feature class, register it with the geodatabase.