Help

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

Available with Standard or Advanced license.

Complexity:
Intermediate
Data Requirement:
Use your own data
Goal:
Create both spatial and nonspatial tables using SQL, then register them 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 ArcGIS and geodatabase functionality, you can register the table with 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

  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.

  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.

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

    Oracle

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

    PostgreSQL

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

    DB2

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

    Informix

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

Adding data to a spatial table using SQL

Insert three records into the outbreaks table.

  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, loc)
VALUES (1, 
'1420 kirchestrasse', 
43, 
sde.st_geometry (0.00003, 0.00051, null, null, 4326)
);

INSERT INTO OUTBREAKS (OID, address, city_code, loc)
VALUES (2, 
'638 villa arbol', 
10, 
sde.st_geometry (0.00020, -0.00029, null, null, 4326)
);

INSERT INTO OUTBREAKS (OID, address, city_code, loc)
VALUES (3, 
'5579 riverview dr', 
96, 
sde.st_geometry (-0.00048, -0.00009, null, null, 4326)
);

    PostgreSQL

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

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

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

    DB2

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

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

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

    Informix

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

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

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

Registering the tables with the geodatabase

You can use ArcGIS for Desktop to register a table with the geodatabase.

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

  1. Start ArcMap and open the Catalog window or start ArcCatalog.
  2. Connect to the enterprise 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 and click Register with Geodatabase.
    4. The PID field was defined as a not null integer column, so it is used as the ObjectID for your registered table.
  4. Right-click the outbreaks table and click Register with Geodatabase.
    5. A spatial reference was defined for the fields you inserted and only one entity type was inserted (points), so ArcGIS detects this upon registration and uses that information to define the feature class. The OID field, which was defined as a not null integer column, is used as the ObjectID for your registered feature class.