Workflow: Creating tables with SQL and registering them with ArcSDE and the geodatabase

This topic applies to ArcEditor and ArcInfo only.

Complexity: Intermediate Data Requirement: Use your own data Goal: Create both spatial and nonspatial tables using SQL, register the tables with ArcSDE, then register the tables with the geodatabase.

You can use SQL to create tables. If the table contains a spatial column, the table is considered to be a spatial table. You can use SQL to populate both nonspatial and spatial tables with data. Then, to use ArcSDE and geodatabase functionality, you can register the table with ArcSDE and the geodatabase.

You might do this if you are using a custom application to create spatial or nonspatial tables or receive database tables from another agency or contractor but want to take advantage of geodatabase and ArcGIS functionality.

Creating a nonspatial table with SQL

Steps:
  1. Open a SQL client and log in to the database as a user with permissions to create tables in the database.
  2. Use a CREATE TABLE SQL statement to create a nonspatial table, patients, that contains columns with the following names: PID, f_name, l_name, and dwel_id.

    Oracle

    CREATE TABLE patients 
(PID integer NOT NULL, f_name nvarchar2(25), l_name nvarchar2(38), dwel_id integer);

    PostgreSQL

    CREATE TABLE patients 
(PID integer NOT NULL UNIQUE, f_name varchar(25), l_name varchar(38), dwel_id integer);

    DB2

    CREATE TABLE patients 
(PID integer NOT NULL, f_name varchar(25), l_name varchar(38), dwel_id integer);

    Informix

    CREATE TABLE patients 
(PID integer NOT NULL, f_name varchar(25), l_name varchar(38), dwel_id integer);

Adding data to a nonspatial table using SQL

Use SQL INSERT statements to add records to the patients table using SQL.

Steps:
  1. Insert four records into the patients table.

    Oracle

    INSERT INTO PATIENTS (PID, f_name, l_name, dwel_id)
VALUES (1, 'wolfgang', 'bruker', 4301);

INSERT INTO PATIENTS (PID, f_name, l_name, dwel_id)
VALUES (2, 'ida', 'pastens', 4301);

INSERT INTO PATIENTS (PID, f_name, l_name, dwel_id)
VALUES (3, 'ricardo', 'montoya', 1001);

INSERT INTO PATIENTS (PID, f_name, l_name, dwel_id)
VALUES (4, 'tukufu', 'endel', 9601);

    PostgreSQL

    INSERT INTO patients (PID, f_name, l_name, dwel_id)
VALUES (1, 'wolfgang', 'bruker', 4301);

INSERT INTO patients (PID, f_name, l_name, dwel_id)
VALUES (2, 'ida', 'pastens', 4301);

INSERT INTO patients (PID, f_name, l_name, dwel_id)
VALUES (3, 'ricardo', 'montoya', 1001);

INSERT INTO patients (PID, f_name, l_name, dwel_id)
VALUES (4, 'tukufu', 'endel', 9601);

    DB2

    INSERT INTO PATIENTS (PID, f_name, l_name, dwel_id)
VALUES (1, 'wolfgang', 'bruker', 4301);

INSERT INTO PATIENTS (PID, f_name, l_name, dwel_id)
VALUES (2, 'ida', 'pastens', 4301);

INSERT INTO PATIENTS (PID, f_name, l_name, dwel_id)
VALUES (3, 'ricardo', 'montoya', 1001);

INSERT INTO PATIENTS (PID, f_name, l_name, dwel_id)
VALUES (4, 'tukufu', 'endel', 9601);

    Informix

    INSERT INTO patients (PID, f_name, l_name, dwel_id)
VALUES (1, 'wolfgang', 'bruker', 4301);

INSERT INTO patients (PID, f_name, l_name, dwel_id)
VALUES (2, 'ida', 'pastens', 4301);

INSERT INTO patients (PID, f_name, l_name, dwel_id)
VALUES (3, 'ricardo', 'montoya', 1001);

INSERT INTO patients (PID, f_name, l_name, dwel_id)
VALUES (4, 'tukufu', 'endel', 9601);

Creating a spatial table with SQL

You can also use a CREATE TABLE statement to create a table with a spatial column.

Steps:
  1. Create a table, outbreaks, that contains an integer OID, integer dwel_id, string address, integer city_code, and ST_Geometry location column.

    Oracle

    CREATE TABLE outbreaks 
(OID integer NOT NULL, address nvarchar2(120), city_code integer, location sde.st_geometry);

    PostgreSQL

    CREATE TABLE outbreaks 
(OID integer NOT NULL UNIQUE, address varchar(120), city_code integer, location sde.st_geometry);

    DB2

    CREATE TABLE outbreaks 
(OID integer NOT NULL, address nvarchar2(120), city_code integer, location st_geometry);

    Informix

    CREATE TABLE outbreaks 
(OID integer NOT NULL UNIQUE, address varchar(120), city_code integer, location st_geometry);

Adding data to a spatial table using SQL

Insert three records into the outbreaks table.

NoteNote:

The 0 in these sample statements is the SRID. You would specify a different number.

Steps:
  1. Use INSERT SQL statements and the ST_Point function to add records to the outbreaks table.

    Oracle

    INSERT INTO OUTBREAKS (OID, address, city_code, location)
VALUES (1, 
'1420 kirchestrasse', 
43, 
sde.st_point (0.00003, 0.00051, 0)
);

INSERT INTO OUTBREAKS (OID, address, city_code, location)
VALUES (2, 
'638 villa arbol', 
10, 
sde.st_point (0.00020, -0.00029, 0)
);

INSERT INTO OUTBREAKS (OID, address, city_code, location)
VALUES (3, 
'5579 riverview dr', 
96, 
sde.st_point (-0.00048, -0.00009, 0)
);

    PostgreSQL

    INSERT INTO outbreaks (OID, address, city_code, location)
VALUES (1, 
'1420 kirchestrasse', 
43, 
sde.st_point (0.00003, 0.00051, 0)
);

INSERT INTO outbreaks (OID, address, city_code, location)
VALUES (2, 
'638 villa arbol', 
10, 
sde.st_point (0.00020, -0.00029, 0)
);

INSERT INTO outbreaks (OID, address, city_code, location)
VALUES (3, 
'5579 riverview dr', 
96, 
sde.st_point (-0.00048, -0.00009, 0)
);

    DB2

    INSERT INTO OUTBREAKS (OID, address, city_code, location)
VALUES (1, 
'1420 kirchestrasse', 
43, 
st_point (0.00003, 0.00051, 0)
);

INSERT INTO OUTBREAKS (OID, address, city_code, location)
VALUES (2, 
'638 villa arbol', 
10, 
st_point (0.00020, -0.00029, 0)
);

INSERT INTO OUTBREAKS (OID, address, city_code, location)
VALUES (3, 
'5579 riverview dr', 
96, 
st_point (-0.00048, -0.00009, 0)
);

    Informix

    INSERT INTO outbreaks (OID, address, city_code, location)
VALUES (1, 
'1420 kirchestrasse', 
43, 
st_point (0.00003, 0.00051, 0)
);

INSERT INTO outbreaks (OID, address, city_code, location)
VALUES (2, 
'638 villa arbol', 
10, 
st_point (0.00020, -0.00029, 0)
);

INSERT INTO outbreaks (OID, address, city_code, location)
VALUES (3, 
'5579 riverview dr', 
96, 
st_point (-0.00048, -0.00009, 0)
);

Registering nonspatial tables with ArcSDE

Nonspatial tables are registered with ArcSDE using the sdetable command. Once registered, a record is added for the table to the TABLE_REGISTRY system table. Records are also added to the COLUMN_REGISTRY system table for each column in the table.

The options shown in these examples indicate the following:

Steps:
  1. Change directories to the SDEHOME > bin.
  2. Issue the sdetable command with the register operation to register the patients table with ArcSDE.

    Oracle

    sdetable -o register -t patients -c PID -C SDE -i sde:oracle11g 
-s server1 -u creator -p powerup

    PostgreSQL

    sdetable -o register -t patients -c PID -C SDE -i sde:postgresql:server1 
-D postdb -s server1 -u creator -p powerup

    DB2

    sdetable -o register -t patients -c PID -C SDE -i sde:db2 
-D db2db -s server1 -u creator -p powerup

    Informix

    sdetable -o register -t patients -c PID -C SDE -i sde:informix 
-D idsdb -s server1 -u creator -p powerup

Registering spatial tables with ArcSDE

Spatial tables are registered with ArcSDE using the sdelayer command. Once registered, a record is added for the table to the LAYERS and TABLE_REGISTRY system tables. Records are also added to the COLUMN_REGISTRY system table for each column in the table, and a record is added to the GEOMETRY_COLUMNS system table for the spatial column.

The options shown in these examples indicate the following:

Steps:
  1. Use the sdelayer command with the register operation to register the outbreaks table with ArcSDE.

    Oracle

    sdelayer -o register -l outbreaks,location -e p -t ST_GEOMETRY -C RID,SDE
-E -498618.389,-243045.144,498618.389,243045.144 -R 0 -i sde:oracle11g 
-s server1 -u creator -p powerup

    PostgreSQL

    sdelayer -o register -l outbreaks,location -e p -t ST_GEOMETRY -C RID,SDE
-E -498618.389,-243045.144,498618.389,243045.144 -R 0 -i sde:postgresql:server1 
-s server1 -D postdb -u creator -p powerup

    DB2

    sdelayer -o register -l outbreaks,location -e p -t ST_GEOMETRY -C RID,SDE
-E -498618.389,-243045.144,498618.389,243045.144 -R 0 -i sde:db2 -s server1 
-D db2db -u creator -p powerup

    Informix

    sdelayer -o register -l outbreaks,location -e p -t ST_GEOMETRY -C RID,SDE
-E -498618.389,-243045.144,498618.389,243045.144 -R 0 -i sde:informix 
-s server1 -D idsdb -u creator -p powerup

Registering the table and the layer with the geodatabase

You can use ArcGIS Desktop or ArcObjects to register a table or layer with the geodatabase.

Use RegisterAsObjectClass on the IClassSchemaEdit3 ArcObjects method to register a table or layer with the geodatabase. See the ArcObjects Library Reference for syntax and usage information.

The following set of steps describes registering with the geodatabase in ArcGIS Desktop.

In the Catalog tree, right-click the table or layer you want to register with the geodatabase and click Register with Geodatabase.

Steps:
  1. Start ArcMap and open the Catalog window or start ArcCatalog.
  2. Connect to the ArcSDE geodatabase that contains the table you want to register.

    This connection is made under the Database Connections node of the Catalog tree. Be sure you connect as the owner of the table.

  3. Right-click the patients table.
  4. Click Register with Geodatabase.
  5. You specified the OID field as the ObjectID when you registered the table with ArcSDE; therefore, choose that field to use as the geodatabase ObjectID field.
  6. Click OK.
  7. Right-click the outbreaks layer.
  8. Click Register with Geodatabase.
  9. Click OK.

Copyright © 1995-2013 Esri. All rights reserved.
2/5/2013