ArcMap

Workflow: Create tables with SQL and register 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. Once the table exists, you can register it with the geodatabase so it can participate in geodatabase functionality.

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.

Similarly, you can use SQL to define views on spatial and nonspatial tables. If the view definition includes an integer column that contains unique values that can be used as an ObjectID, you can register the view with the geodatabase. Registering a view allows you to store information about the contents of the view—such as the extent and geometry type—and allows you to define metadata on the view.

This workflow takes you through creating both a nonspatial and spatial table using a SQL client, adding data to the tables, and registering the tables with the geodatabase.

Create 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);

Add 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);

Create 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);

Add 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)
);

Register the tables with the geodatabase

You can register a table with the geodatabase in ArcGIS Desktop.

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

  1. Open the Register with Geodatabase tool.

    You can either search for the tool, open it in the Geodatabase Administration toolset of the Data Management toolbox, or, in the Catalog tree in ArcMap, right-click the table or view under the database connection and click Register with Geodatabase.

  2. Start an ArcGIS Desktop client and connect to the enterprise geodatabase that contains the table or view you want to register.

    Connect as the owner of the table or view, as only the owner can register it with the geodatabase.

  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.