One of the key features of using spatial types with ArcGIS is the capability to add and edit geometries via SQL. This example shows you how to insert data via SQL and display the features immediately in ArcGIS. First, create an empty feature class in ArcCatalog. Then populate the feature class with records using SQL. To complete this example, you need to use the Editing GIS Features tutorial data. If you did not install the tutorial data when you installed ArcGIS Desktop, do so now.

Creating a feature class

1. Start ArcCatalog and connect to the database in which you will create an empty spatial type feature class.
2. Right-click the geodatabase, point to New, then click Feature Class.
3. Type "buildings" in the Name text box.
4. The feature class type in this case is polygon, so ensure Polygon Features is chosen for the Type and click Next.
5. For the coordinate system, import the coordinate system from one of the ArcGIS tutorial datasets. Click Import and navigate to the SimpleEdits feature dataset in the EditorTutorial file geodatabase, choose the SimpleBuildings feature class, then click Add (for example, C:\ArcGIS\ArcTutor\Editor\ExerciseData\EditorTutorial.gdb\SimpleEdits).
6. Click Next.
7. Click Next to accept the default x,y tolerance.
8. If you are creating the feature class in a geodatabase in DB2 or Informix, or if you are creating the feature class in a geodatabase in Oracle or PostgreSQL that uses the spatial type as its default geometry storage, you can accept the Default configuration keyword and click Next. If you are creating the feature class in a geodatabase in Oracle or PostgreSQL that has a default geometry storage type other than ST_Geometry, choose Use configuration keyword, choose ST_GEOMETRY from the drop-down list, then click Next.
9. Import the field definitions from the SimpleBuildings feature class inside the SimpleEdits dataset. Click Import, navigate to the SimpleBuildings feature class as you did when you imported the coordinate system, then click Add.
10. Click Finish.

You now have an empty feature class called buildings. Next, use SQL to determine the spatial reference ID and add features to the feature class.

Determining the SRID of a feature class

Before you can add features to the feature class, you must determine the SRID value for the feature class. An ST_Geometry SRID value of 3 is used in the SQL INSERT examples in the next section. This was specific to the test database used developing these examples, and yours may be different. To determine the ST_Geometry SRID of your feature class, use a SQL SELECT statement to choose the SRID value from the ST_GEOMETRY_COLUMNS table in Oracle, the geometry_columns table in Informix and DB2, and the sde_geometry_columns table in PostgreSQL.

Oracle

SELECT table_name, srid 
FROM sde.st_geometry_columns 
WHERE table_name = 'buildings';

PostgreSQL

SELECT f_table_name, srid
FROM sde.sde_geometry_columns
WHERE f_table_name = 'buildings';

DB2

SELECT layer_table, srid
FROM db2gse.geometry_columns
WHERE layer_table = 'buildings';

Informix

SELECT f_table_name, srid
FROM sde.geometry_columns
WHERE f_table_name = 'buildings';

The SRID returned from these queries will be used when inserting records to the feature class, as shown in the next section.

Inserting features to a feature class

Use a SQL editor to add records to the feature class business table, then preview the features in ArcCatalog.

1. Open a SQL editor, such as SQL Plus for Oracle or psql for PostgreSQL, and issue the following commands to insert features to the feature class. Be sure to use the SRID for your feature class in place of the 3 at the end of the INSERT statements.

NOTE: When inserting data to geodatabases in DB2, you need to qualify the spatial type functions with "db2gse.". For instance, in the following examples, ST_Geometry becomes db2gse.ST_Geometry. For the other DBMSs, you can qualify the spatial type functions with "sde.".

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(1, 0, 12972, 18907, '22JSM0000023946', 'BLD', 'RES', 6, 0, 0, 1, 
st_geometry ('polygon (( 2219520.56768649 387051.66985716, 2219525.34823696 387079.52399077, 
2219536.03133855 387077.71905252, 2219539.05578917 387095.47546386, 2219528.17754562 387097.32910505, 
2219528.61661291 387099.81695550, 2219489.00622816 387106.54876471, 2219480.81097279 387058.40167483, 
2219520.56768649 387051.66985716))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(2, 0, 13090, 19053, '22JSM0000024089', 'BLD', 'RES', 6, 0, 0, 1, 
st_geometry ('polygon (( 2219612.86639158 386903.72709265, 2219612.86832175 386907.20474822, 
2219619.82528792 386906.03131444, 2219624.05814397 386930.50637511, 2219602.30717225 386934.19443199, 
2219602.68435556 386936.33176596, 2219595.81121637 386937.54715132, 2219595.51783041 386935.61930861, 
2219582.14872687 386937.88243384, 2219577.95779702 386913.07208642, 2219595.22446985 386910.09649113, 
2219593.59000886 386900.45735373, 2219612.86269632 386897.06148069, 2219612.86639158 386903.72709265))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(3, 0, 13165, 19136, '22JSM0000024169', 'BLD', 'BLD', 14, 0, 0, 1, 
st_geoometry ('polygon (( 2219733.93687411 386826.88586815, 2219735.30274506 386834.88599003, 
2219725.20502702 386836.59337847, 2219723.83915606 386828.59325658, 2219733.93687411 386826.88586815))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(4, 0, 12898, 18822, '22JSM0000023861', 'BLD', 'BLD', 14, 0, 0, 1, 
st_geometry ('polygon (( 2219318.56450844 387185.37926723, 2219320.27185454 387197.62335210, 
2219311.29614139 387198.94049048, 2219309.58880798 387186.69635058, 2219318.56450844 387185.37926723))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(5, 0, 13129, 19095, '22JSM0000024131', 'BLD', 'BLD', 14, 0, 0, 1, 
st_geometry('polygon (( 2219357.88220142 386887.66730143, 2219360.46761861 386898.54553227, 
2219350.56500020 386900.98462474, 2219347.97961264 386890.10638120, 2219357.88220142 386887.66730143))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(6, 0, 12933, 18863, '22JSM0000023902', 'BLD', 'RES', 6, 0, 0, 1, 
st_geometry ('polygon (( 2219640.86224883 387097.71935934, 2219658.37473060 387147.67138324, 
2219620.66681275 387160.89111018, 2219609.69104055 387129.67108043, 2219619.00825848 387126.35393804, 
2219612.47155737 387107.67078229, 2219640.86224883 387097.71935934))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(7, 0, 13080, 19038, '22JSM0000024074', 'BLD', 'RES', 6, 0, 0, 1, 
st_geometry ('polygon (( 2219498.86004627 386911.32623002, 2219505.93331369 386953.22930633, 
2219453.63980640 386962.05871170, 2219448.56655992 386931.86309469, 2219465.10339963 386929.08257787, 
2219463.15216206 386917.37511856, 2219498.86004627 386911.32623002))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(8, 0, 12929, 18859, '22PWS0000020029', 'BLD', 'RES', 6, 0, 0, 1, 
st_geometry ('polygon (( 2219537.93380545 387110.93908628, 2219546.81201112 387161.33014361,
2219498.76248799 387169.86682333, 2219493.68920765 387140.93957403, 2219516.07980240 387136.98828165, 
2219512.32366468 387115.52454135, 2219537.93380545 387110.93908628))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(9, 0, 12985, 18921, '22JSM0000023960', 'BLD', 'RES', 6, 0, 0, 1, 
st_geometry ('polygon (( 2219630.32549736 387032.49884228, 2219638.42319022 387080.93859854, 
2219602.27627682 387087.03625775, 2219599.44695969 387070.06042272, 2219594.95910946 387070.84088050, 
2219589.69069987 387039.32824786, 2219630.32549736 387032.49884228))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(10, 0, 13075, 0, '22PWS0000020046', 'BLD', 'RES', 6, 0, 0, 1, 
st_geometry ('polygon (( 2219428.55884565 386927.35910468, 2219434.90911597 386965.59318031, 
2219412.50672431 386969.25340210, 2219412.90361568 386971.59064420, 2219405.49494299 386972.82544978, 
2219405.18625535 386970.70870430, 2219391.47137188 386973.00185724, 2219387.14966448 386946.93921840,
2219404.70113486 386944.07272009, 2219402.67258040 386931.63676100, 2219428.55884565 386927.35910468))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(11, 0, 13149, 19116, '22PWS0000020056', 'BLD', 'BLD', 14, 0, 0, 1, 
st_geometry ('polygon (( 2219672.66761980 386847.66674281, 2219676.37499955 386866.54504475, 
2219663.35040187 386869.13042807, 2219659.64303058 386850.25207534, 2219672.66761980 386847.66674281))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(12, 0, 13013, 18962, '22JSM0000024001', 'BLD', 'BLD', 14, 0, 0, 1, 
st_geometry ('polygon (( 2219556.03164537 387046.25513130, 2219557.49509154 387055.03576599, 
2219547.05591105 387056.74309940, 2219545.59246912 387047.96251973, 2219556.03164537 387046.25513130))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(13, 0, 12905, 18833, '22JSM0000023872', 'BLD', 'RES', 6, 0, 0, 1, 
st_geometry ('polygon (( 2219383.93139678 387137.86633157, 2219389.05343086 387190.74523511, 
2219359.00421054 387193.57452260, 2219353.93090903 387140.69568256, 2219383.93139678 387137.86633157))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(14, 0, 12951, 18884, '22JSM0000023923', 'BLD', 'RES', 6, 0, 0, 1, 
st_geometry ('polygon (( 2219373.44344985 387075.37756489, 2219382.32162166 387124.54907598, 
2219362.80911894 387128.11010561, 2219359.44323973 387109.62200293, 2219339.54046156 387113.28058238, 
2219333.97942791 387082.54840752, 2219373.44344985 387075.37756489))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(15, 0, 13107, 19071, '22JSM0000024107', 'BLD', 'RES', 6, 0, 0, 1, 
st_geometry ('polygon (( 2219682.28129249 386891.68291590, 2219686.92111827 386918.49082923, 
2219640.56580254 386926.48163888, 2219635.92597252 386899.67372556, 2219682.28129249 386891.68291590))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(16, 0, 13086, 19044, '22JSM0000024080', 'BLD', 'RES', 6, 0, 0, 1, 
st_geometry ('polygon (( 2219553.98285375 386902.15533258, 2219555.78774544 386913.03356343, 
2219560.95856289 386912.15551350, 2219565.78790520 386940.83886287, 2219517.98226930 386948.93658960, 
2219513.15292276 386920.20440606, 2219516.17736068 386919.66779319, 2219514.37246900 386908.88714178, 
2219553.98285375 386902.15533258))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(17, 0, 13072, 19027, '22PWS0000020045', 'BLD', 'RES', 6, 0, 0, 1, 
st_geometry ('polygon (( 2219363.11822986 386945.42381000, 2219367.93141545 386973.32232908, 
2219320.06718025 386981.52254956, 2219315.29857060 386953.62397969, 2219363.11822986 386945.42381000))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(18, 0, 13118, 19082, '22JSM0000024118', 'BLD', 'RES', 6, 0, 0, 1, 
st_geometry ('polygon (( 2219744.18548833 386871.29585958, 2219746.55761318 386884.95597445, 
2219743.44931865 386885.52860025, 2219747.49828784 386908.59539393, 2219704.96369012 386916.03897901, 
2219700.42393269 386890.23190579, 2219729.62557524 386885.11957759, 2219727.74423440 386874.19963643, 
2219744.18548833 386871.29585958))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(19, 0, 13138, 19105, '22JSM0000024141', 'BLD', 'BLD', 14, 0, 0, 1, 
st_geometry ('polygon (( 2219424.32229434 386872.05730772, 2219426.12719873 386882.05749711, 
2219416.56607240 386883.81360119, 2219414.76116801 386873.81342026, 2219424.32229434 386872.05730772))', 3));

INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype, 
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(20, 0, 13151, 19120, '22JSM0000024154', 'BLD', 'BLD', 14, 0, 0, 1, 
st_geometry ('polygon (( 2219553.73895382 386851.52038802, 2219555.25115373 386859.91077266, 
2219545.25100667 386861.81323532, 2219543.69000222 386853.42285069, 2219553.73895382 386851.52038802))', 3));
COMMIT;



2. If you closed ArcCatalog, restart it.
3. Connect to the geodatabase in which you created the buildings feature class. If you left ArcCatalog open after creating the buildings feature class, right-click the geodatabase and click Refresh.
4. Click the buildings feature class to choose it.
5. Click the Preview tab in ArcCatalog to view the features you added to the buildings feature class.

If you only want to display features with certain attributes, you can apply a definition query to a layer. For example, you may want to display only cities with a population above a certain threshold. You can type your own expression or you can use the Query Builder to help you set up your query expression. To add all the features back to the display, simply delete the query.

With spatial types, definition queries executed in ArcMap can contain a spatial component. Queries can include calls to stored procedures, which may also contain a spatial component. This capability off-loads the processing from the client to the server, giving you an option for managing system resources. This example shows you how to do this.

Spatial queries usually compare features from one dataset in relation to features from another dataset. Therefore, you need to add another feature class to your geodatabase to compare it to the buildings features you added in the last example.

1. Start ArcCatalog and connect to the geodatabase in which you stored the buildings feature class.
2. In ArcCatalog, navigate to the SimpleEdits feature dataset in the EditorTutorial file geodatabase. This is usually located at C:\ArcGIS\ArcTutor\Editor\ExerciseData\EditorTutorial.gdb.
3. Right-click the Water feature class in the SimpleEdits feature dataset and click Copy.
4. Right-click the geodatabase to which you want to add this feature class and click Paste. If your default geometry storage type is ST_Geometry, you can leave the configuration keyword set to DEFAULTS. If your geodatabase is in Oracle or PostgreSQL and the default geometry storage is something other than ST_Geometry, click in the Config. Keyword field, and choose ST_GEOMETRY (or whichever custom configuration keyword you created for ST_Geometry storage in PostgreSQL) from the drop-down list. Click OK to paste the data.

Now that you have another spatial type feature class in your geodatabase, you can use a definition query to display a subset of building features that are within 50 meters of water mains by following these steps:

1. Start ArcMap.
2. Add the buildings and water feature classes to ArcMap.
3. Right-click the buildings layer in the table of contents and click Properties.
4. Click the Definition Query tab.
5. Query Builder lets you create an expression to identify the particular features in the layer you want to display. This query will find all of the buildings within 50 meters of a water main. Type the expression appropriate to your DBMS in the Layer Properties definition Query box.
For Oracle:

objectid IN (SELECT b.objectid FROM buildings b, water w
WHERE w.watertype = 'MAIN' 
AND sde.st_overlaps (b.shape, sde.st_buffer (w.shape, 50)) = 1)



For PostgreSQL:

objectid IN (SELECT b.objectid FROM buildings b, water w
WHERE w.watertype = 'MAIN' 
AND sde.st_overlaps (b.shape, sde.st_buffer (w.shape, 50)) = 't')



For DB2:

objectid IN (SELECT b.objectid FROM buildings b, water w
WHERE w.watertype = 'MAIN' 
AND db2gse.st_overlaps (b.shape, db2gse.st_buffer (w.shape, 50)) = 1)



For Informix:

objectid IN (SELECT b.objectid FROM buildings b, water w
WHERE w.watertype = 'MAIN' 
AND sde.st_overlaps (b.shape, sde.st_buffer (w.shape, 50)))



6. Click OK.

For comparison, you can see the same results are returned if you use the ArcMap Select By Location tool:

1. Delete the definition query you created in the last set of steps.
2. Choose the water feature class in the table of contents.
3. Click the Selection drop-down menu.
4. Click Select By Attributes.
5. Create or enter this filter: watertype = 'MAIN'.
6. Click OK.
7. Click the Selection drop-down menu.
8. Click Select By Location.
9. In the first box, check the buildings feature class.
10. Choose the "are within a distance of" option from the "that" drop-down list.
11. Choose the water feature class from the "the features in this layer" drop-down list.
12. Specify 50 meters for your buffer value.
13. Click OK.

The same buildings features will be displayed in the selected set.

ESRI recommends creating your feature classes via ArcGIS. Creating ST_Geometry feature classes via ArcGIS provides you with a full suite of tools for properly creating a geodatabase feature class. ESRI understands, though, that some SQL users and application developers want to have another option when working with feature classes. ESRI supports registering external feature classes created via SQL or a third-party application.

You can use the ArcSDE administration command sdelayer –o register to manually register a table as a feature class. Registering tables as feature classes with the sdelayer command gives you control over how a table is registered.

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). Geometry can be multipart.
• It must have an integer, unique, non-NULL column suitable as a registered row ID column.
• All records in the table must have the same 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: Autoregistration of feature classes in PostgreSQL or feature classes in Oracle with an ST_Geometry column is not supported. To use autoregistration with DB2, Informix, or Oracle Spatial feature classes, set the SERVER_CONFIG parameter DISABLEAUTOREG to FALSE. See ArcSDE initialization parameters for more information about DISABLEAUTOREG.

The following is an example of registering a table called comm_bldgs containing polygon geometries (–e a) in a spatial column called SHAPE. The table has an integer column called OBJECTID that will be used as a unique feature identifier column maintained by ArcSDE (–C OBJECTID,SDE). This feature class table is first created from an existing table via SQL and then registered with ArcSDE.

1. In SQL, create the comm_bldgs table from the existing buildings feature class you created in the first example. This causes the comm_bldgs table to be created 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. Register the feature class with ArcSDE using the sdelayer command.

sdelayer –o register –l comm_bldgs,shape –e a –C OBJECTID,SDE –R <SRID> –u <user> –p <pw>



For this example, use the SRID value for the buildings feature class. You can find this value by using sdelayer –o describe_long or directly querying the LAYERS (Oracle, DB2, or Informix) or sde_layers (PostgreSQL) table using SQL.

NOTE: If the data in your table uses multiple SRIDs, registration will fail. ArcSDE does not support multiple SRIDs in the same table.

If the table does not contain any data and you register the table with ArcSDE but do not specify the –R option with a valid SRID, the default SRID will be 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 to the table that have a valid SRID other than 0, it will fail because the SRIDs will not be the same. Once an SRID is assigned to a table, it cannot be changed; you have to delete the feature class 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.

This has registered your table with ArcSDE, making it a feature class. 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. You can also register it as versioned.

If you need the feature class to participate in geodatabase functionality, such as relationship classes, topology, geometric networks, cadastral fabrics, terrains, and schemas, or have subtypes, default values, domains, or validation rules, it must also be registered with the geodatabase.

Registering a feature class with the geodatabase adds an ObjectID field to the table. This field will be called ObjectID. If an ObjectID field already exists in the dataset, a field named ID is added to the business table. This ID field's data type is ObjectID.

Registering the feature class with the geodatabase also adds a record to the GDB_OBJECTCLASSES and GDB_FEATURECLASSES system tables.

You can register the datasets in ArcCatalog as follows:

1. Start ArcCatalog.
2. Right-click the feature class you want to register with the geodatabase.
3. Click Register with Geodatabase.

Tip You can also register nonspatial tables with ArcSDE and the geodatabase. For details on this, see Registering tables to be used by ArcGIS Desktop.