Tips on registering existing tables with an ST_Geometry column
Using existing ST_Geometry tables
ArcSDE can successfully use tables containing ST_Geometry columns created using SQL (sometimes referred to as third-party tables) as long as the tables meet the following prerequisites:
- 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). The geometries can be multipart.
- Geometry should be valid; otherwise, accessing these geometries may have unexpected results.
Performing registration of third-party tables containing ST_Geometry columns
The ArcSDE administration command sdelayer –o register registers a spatial table as a feature class. To register tables containing an ST_Geometry column, the prerequisites described in the previous section must be met.
The tables you register can be empty or can already contain data.
Registering tables that already contain data
When you register a table that already contains data, the data has a spatial reference system assigned to it. That means when you register the table with ArcSDE, you do not have to specify the spatial reference ID (SRID) with the –R option. ArcSDE checks the defined SRID of the first record in the table and uses that.
Be aware, though, that the SRID must exist in the ST_SPATIAL_REFERENCES table for it to be accepted. If the spatial reference system you need is not already in the ST_SPATIAL_REFERENCES table, the recommended way to get it there is to start ArcCatalog, connect to the geodatabase, then create a new feature class that uses the spatial reference system you want and ST_Geometry storage. When you do this, the ST_SPATIAL_REFERENCES table is populated with the SRID you need, and ArcCatalog calculates the x,y, z-, and m-offsets and units and the layer extent for you. It is also possible to use SQL to insert the spatial reference, but when you do so, you must be sure to calculate the correct x,y, z-, and m-offsets and units yourself.
In the following example, a table, TOWERS, containing records of point geometries (–e p) in a spatial column, SHAPE, is registered. Since the spatial reference is already known, it does not have to be specified when registering the table with sdelayer. Using the –C option, a unique, not-NULL column, FID, is added to the table to be used as a row ID column.
sdelayer –o register –l towers,shape –e p –C fid,SDE –u brooke –p pwd19
For tables that contain a large number of records, registration may take less time if you register the row ID column as user maintained.
sdelayer –o register –l towers,shape –e p –C fid,USER –u brooke –p pwd19
However, if you register the feature identifier column as user maintained and subsequently register the feature class with the geodatabase, ArcGIS will add an additional feature identifier column, object_ID. ArcGIS maintains the values of this column. If the table contains a large number of records, adding this additional object_ID column may take some time.
Registering spatial tables that do not contain data
If the table does not contain data and does not have an assigned SRID, you have to specify an SRID when the table is registered. This is done with the –R option. The SRID specified at this time must already be present in the SPATIAL_REFERENCES table in the geodatabase and correspond to the SRID defined by ArcSDE.
As mentioned in the previous section, to populate the SPATIAL_REFERENCES table with the spatial reference system you want to use, start ArcCatalog, connect to the geodatabase, then create a new feature class that uses the spatial reference system you want and ST_Geometry storage. Then query the SPATIAL_REFERENCES system to discover the SRID that was assigned to the spatial reference system you specified for the feature class. Use this SRID when registering the spatial table.
In the following example, an empty table, DRAINAGE, is registered with ArcSDE. The table already contains an integer, unique, not-NULL column, ID, that will be used for the row ID column. The SRID is specified with the –R option.
sdelayer –o register –l drainage,line –e l+ –C id,SDE –R 6 –u hortence –p topsecret
If the table does not contain data or have an assigned SRID, and you do not specify one when the table is registered with ArcSDE, it will be assigned the default SRID value of 0. This SRID exists for sample and documentation purposes only and is not recommended for use with production data. The specifications for this SRID are as follows:
|
FalseX |
- 400 |
|
FalseY |
- 400 |
|
XYUnits |
1000000000 |
|
FalseZ |
- 100000 |
|
ZUnits |
10000 |
|
FalseM |
- 100000 |
|
MUnits |
10000 |
|
XYCluster_Tol |
0.000000008983153 |
|
ZCluster_Tol |
.001 |
|
MCluster_Tol |
.001 |
|
Projection |
GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137.0,298.257223563]], PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]] |
|
Auth_srid |
4326 |
|
Auth_name |
EPSG |
For further information on using the sdelayer command, consult the ArcSDE Administration Command Reference provided with the ArcSDE component of ArcGIS Server at the Enterprise level.
The sdelayer command only adds the table to the ArcSDE system tables. To be able to use ArcGIS Desktop geodatabase functionality, such as topology, versioning, and networks, you must also register the table with the geodatabase. See Registering a table with the geodatabase for more information.