Desktop Help 10.0 - Workflow: Using SQL with existing feature classes

This topic applies to ArcEditor and ArcInfo only.

Complexity: Intermediate Data Requirement: ArcGIS Tutorial Data Setup

One of the key features of using spatial types with ArcGIS is the capability to add and edit geometries via SQL. The examples in this topic show you how to create a feature class using ArcGIS Desktop, then edit it using SQL. A spatial selection is also made from the feature class using SQL and ArcGIS Desktop to show that the results are the same.

Creating an empty feature class in ArcCatalog

This example shows you how to create an empty feature class in ArcCatalog.

Steps:

Start ArcCatalog and expand the Database Connections folder in the Catalog tree.
Connect to the geodatabase in which you will create an empty feature class containing an ST_Geometry column.
Right-click the geodatabase, point to New, then click Feature Class.
Type buildings in the Name text box.
The feature class type in this case is polygon, so ensure that Polygon Features is chosen for the Type and click Next.
Choose NAD_1983_UTM_Zone_13N for the coordinate system. To do this, expand the Projected Coordinate Systems folder, expand the UTM folder, expand the NAD 1983 folder, then click NAD 1983 UTM Zone 13N.
Click Next.
Click Next to accept the default x,y tolerance.
Which configuration keyword you use depends on what you have set for the GEOMETRY_STORAGE parameter of the DEFAULTS keyword in the DBTUNE table.
- If you are creating the feature class in a geodatabase in IBM DB2 or Informix, or if you are creating the feature class in a geodatabase in Oracle or PostgreSQL that uses ST_Geometry 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 the configuration keyword that stores a GEOMETRY_STORAGE tpe of ST_GEOMETRY from the drop-down list, then click Next.
Add the following attribute fields with these definitions by typing the field name in the Field Name text box, choosing the data type from the Data Type drop-down list, then typing a length, when necessary.

Note:

The OBJECTID and SHAPE fields are automatically present in the feature class. You need to add the other fields. Be sure to add them in the order listed.

Field Name

Data Type

Field Properties

FID

Long Integer

Allow Nulls

TYPE

Text

Length = 3

Allow Nulls

SUBTYPE

Text

Length = 6

Allow Nulls
Click Finish.

Field Name	Data Type	Field Properties
FID	Long Integer	Allow Nulls
TYPE	Text	Length = 3 Allow Nulls
SUBTYPE	Text	Length = 6 Allow Nulls

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 the feature class

Before you can add features to the feature class, you must determine the SRID value for the feature class. The SRID is assigned when the feature class is created in the geodatabase and is stored in various ArcSDE geodatabase system tables.

To determine the ST_Geometry SRID of the feature class you created, 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.

Steps:

Open a SQL editor, such as SQL*Plus for Oracle or psql for PostgreSQL.
Be sure to log in as the same user as you did when you created the buildings feature class in ArcGIS Desktop.

Execute the following SQL statements to determine the SRID of the feature class.

Oracle

SELECT table_name, srid 
FROM sde.st_geometry_columns 
WHERE table_name = 'BUILDINGS'
AND owner_name = 'NEYES';

PostgreSQL

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

DB2

SELECT layer_table, srid
FROM db2gse.geometry_columns
WHERE layer_table = 'BUILDINGS'
AND owner_name = 'NEYES';

Informix

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

The SRID returned from these queries is used when inserting records in the feature class.

Determining the REGISTRATION_ID of the feature class.

Also, before inserting records in your feature class using SQL, you must query the TABLE_REGISTRY table to obtain the REGISTRATION_ID of the feature class.

Oracle

SELECT registration_id
FROM sde.table_registry
WHERE table_name = 'BUILDINGS'
AND owner_name = 'NEYES';

REGISTRATION_ID
40

PostgreSQL

SELECT registration_id
FROM sde.sde_table_registry
WHERE table_name = 'buildings'
AND owner_name = 'neyes';

REGISTRATION_ID
40

DB2

SELECT registration_id
FROM sde.table_registry
WHERE table_name = 'BUILDINGS'
AND owner_name = 'NEYES';

REGISTRATION_ID
40

Informix

SELECT registration_id
FROM sde.table_registry
WHERE table_name = 'buildings'
AND owner_name = 'neyes';

REGISTRATION_ID
40

You will use the number returned from this query to get an objectID value.

All feature classes contain an objectID column. The values for this column are generated by the geodatabase. Therefore, when you insert records into an existing feature class using SQL, you must execute a utility as part of the SQL command to get and insert the next available objectID value to the objectID column.

Inserting features into the feature class using SQL

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

Use the SRID and REGISTRATION_ID you obtained in the last two sections when inserting records to the feature class.

You will use the utility specific to your DBMS to obtain an object ID value. In Oracle and PostgreSQL, you can use the utility in a subquery to get an object ID value. For DB2 and Informix, you execute the utility to get an object ID value, then use that value in the INSERT statement.

Note:

You must qualify the spatial type functions with db2gse. when inserting data in geodatabases in DB2. For instance, in the following examples, ST_Geometry becomes db2gse.ST_Geometry. Qualify the spatial type functions with sde. for the other DBMSs.

Steps:

Issue the following commands to insert features in the feature class.

Be sure to use the SRID for your feature class in place of the 17 at the end of the INSERT statements, the user name of the feature class owner in place of tbl_owner, and the REGISTRATION_ID of your feature class in place of the 40 in the utility query.

Oracle

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('tbl_owner', 40), 
18907, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('tbl_owner', 40), 
19053, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('tbl_owner', 40), 
19136, 'BLD', 'BLD', sde.st_geoometry 
('polygon (( 2219733.93687411 386826.88586815, 
2219735.30274506 386834.88599003, 2219725.20502702 386836.59337847, 
2219723.83915606 386828.59325658, 2219733.93687411 386826.88586815))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('tbl_owner', 40), 
18822, 'BLD', 'BLD', sde.st_geometry 
('polygon (( 2219318.56450844 387185.37926723, 
2219320.27185454 387197.62335210, 2219311.29614139 387198.94049048, 
2219309.58880798 387186.69635058, 2219318.56450844 387185.37926723))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
sde.version_user_ddl.next_row_id('tbl_owner', 40), 
19095, 'BLD', 'BLD', sde.st_geometry
('polygon (( 2219357.88220142 386887.66730143, 
2219360.46761861 386898.54553227, 2219350.56500020 386900.98462474, 
2219347.97961264 386890.10638120, 2219357.88220142 386887.66730143))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
sde.version_user_ddl.next_row_id('tbl_owner', 40), 
18863, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
sde.version_user_ddl.next_row_id('tbl_owner', 40), 
19038, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('tbl_owner', 40),  
18859, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('tbl_owner', 40),  
18921, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('tbl_owner', 40),  
18000, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('tbl_owner', 40), 
19116, 'BLD', 'BLD', sde.st_geometry ('polygon (( 2219672.66761980 386847.66674281, 
2219676.37499955 386866.54504475, 2219663.35040187 386869.13042807, 
2219659.64303058 386850.25207534, 2219672.66761980 386847.66674281))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('tbl_owner', 40),  
18962, 'BLD', 'BLD', sde.st_geometry 
('polygon (( 2219556.03164537 387046.25513130, 
2219557.49509154 387055.03576599, 2219547.05591105 387056.74309940, 
2219545.59246912 387047.96251973, 2219556.03164537 387046.25513130))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('tbl_owner', 40), 
18833, 'BLD', 'RES', sde.st_geometry 
('polygon (( 2219383.93139678 387137.86633157, 
2219389.05343086 387190.74523511, 2219359.00421054 387193.57452260, 
2219353.93090903 387140.69568256, 2219383.93139678 387137.86633157))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('tbl_owner', 40), 
18884, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('tbl_owner', 40), 
19071, 'BLD', 'RES', sde.st_geometry 
('polygon (( 2219682.28129249 386891.68291590, 
2219686.92111827 386918.49082923, 2219640.56580254 386926.48163888, 
2219635.92597252 386899.67372556, 2219682.28129249 386891.68291590))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('tbl_owner', 40), 
19044, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('tbl_owner', 40), 
19027, 'BLD', 'RES', sde.st_geometry 
('polygon (( 2219363.11822986 386945.42381000, 
2219367.93141545 386973.32232908, 2219320.06718025 386981.52254956, 
2219315.29857060 386953.62397969, 2219363.11822986 386945.42381000))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('tbl_owner', 40), 
19082, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('tbl_owner', 40), 
19105, 'BLD', 'BLD', sde.st_geometry 
('polygon (( 2219424.32229434 386872.05730772, 
2219426.12719873 386882.05749711, 2219416.56607240 386883.81360119, 
2219414.76116801 386873.81342026, 2219424.32229434 386872.05730772))', 
17)
);

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('tbl_owner', 40), 
19120, 'BLD', 'BLD', sde.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;

PostgreSQL

Replace gis in the function name with the user name with which you created the buildings feature class.

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
18907, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
19053, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
19136, 'BLD', 'BLD', sde.st_geoometry 
('polygon (( 2219733.93687411 386826.88586815, 
2219735.30274506 386834.88599003, 2219725.20502702 386836.59337847, 
2219723.83915606 386828.59325658, 2219733.93687411 386826.88586815))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
18822, 'BLD', 'BLD', sde.st_geometry 
('polygon (( 2219318.56450844 387185.37926723, 
2219320.27185454 387197.62335210, 2219311.29614139 387198.94049048, 
2219309.58880798 387186.69635058, 2219318.56450844 387185.37926723))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
19095, 'BLD', 'BLD', sde.st_geometry 
('polygon (( 2219357.88220142 386887.66730143, 
2219360.46761861 386898.54553227, 2219350.56500020 386900.98462474, 
2219347.97961264 386890.10638120, 2219357.88220142 386887.66730143))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
18863, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
19038, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
18859, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
18921, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
18000, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
19116, 'BLD', 'BLD', sde.st_geometry 
('polygon (( 2219672.66761980 386847.66674281, 
2219676.37499955 386866.54504475, 2219663.35040187 386869.13042807, 
2219659.64303058 386850.25207534, 2219672.66761980 386847.66674281))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
18962, 'BLD', 'BLD', sde.st_geometry 
('polygon (( 2219556.03164537 387046.25513130, 
2219557.49509154 387055.03576599, 2219547.05591105 387056.74309940, 
2219545.59246912 387047.96251973, 2219556.03164537 387046.25513130))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
18833, 'BLD', 'RES', sde.st_geometry 
('polygon (( 2219383.93139678 387137.86633157, 
2219389.05343086 387190.74523511, 2219359.00421054 387193.57452260, 
2219353.93090903 387140.69568256, 2219383.93139678 387137.86633157))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
18884, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
19071, 'BLD', 'RES', sde.st_geometry 
('polygon (( 2219682.28129249 386891.68291590, 
2219686.92111827 386918.49082923, 2219640.56580254 386926.48163888, 
2219635.92597252 386899.67372556, 2219682.28129249 386891.68291590))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
19044, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
19027, 'BLD', 'RES', sde.st_geometry 
('polygon (( 2219363.11822986 386945.42381000, 
2219367.93141545 386973.32232908, 2219320.06718025 386981.52254956, 
2219315.29857060 386953.62397969, 2219363.11822986 386945.42381000))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
19082, 'BLD', 'RES', sde.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))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
19105, 'BLD', 'BLD', sde.st_geometry 
('polygon (( 2219424.32229434 386872.05730772, 
2219426.12719873 386882.05749711, 2219416.56607240 386883.81360119, 
2219414.76116801 386873.81342026, 2219424.32229434 386872.05730772))', 
17)
);

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
((SELECT o_base_id FROM gis.i40_get_ids(2,1)), 
19120, 'BLD', 'BLD', sde.st_geometry 
('polygon (( 2219553.73895382 386851.52038802, 
2219555.25115373 386859.91077266, 2219545.25100667 386861.81323532, 
2219543.69000222 386853.42285069, 2219553.73895382 386851.52038802))', 
17)
);

DB2

For each record inserted, you need an object ID value. Call the next_row_id procedure to obtain a value.

CALL SDE.next_row_id('tbl_owner',40,?,?,?)
Value of output parameters

Parameter Name : O_ROWID
Parameter Value : 43

Parameter Name : O_MSGCODE
Parameter Value : 0

Parameter Name : O_MESSAGE
Parameter value : Procedure successfully completed.

Return status = 1

Repeat this for each INSERT statement. Since you are inserting 20 records, you need 20 values.

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(43, 18907, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(44, 19053, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(45, 19136, 'BLD', 'BLD', db2gse.st_geometry 
('polygon (( 2219733.93687411 386826.88586815, 2219735.30274506 386834.88599003, 
2219725.20502702 386836.59337847, 2219723.83915606 386828.59325658, 
2219733.93687411 386826.88586815))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(46, 18822, 'BLD', 'BLD', db2gse.st_geometry 
('polygon (( 2219318.56450844 387185.37926723, 2219320.27185454 387197.62335210, 
2219311.29614139 387198.94049048, 2219309.58880798 387186.69635058, 
2219318.56450844 387185.37926723))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(47, 19095, 'BLD', 'BLD', db2gse.st_geometry 
('polygon (( 2219357.88220142 386887.66730143, 2219360.46761861 386898.54553227, 
2219350.56500020 386900.98462474, 2219347.97961264 386890.10638120, 
2219357.88220142 386887.66730143))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(48, 18863, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(49, 19038, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(50, 18859, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(51, 18921, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO BUILDINGS
(objectid, fid, subtype, shape)
VALUES
(52, 18000, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(53, 19116, 'BLD', 'BLD', db2gse.st_geometry 
('polygon (( 2219672.66761980 386847.66674281, 2219676.37499955 386866.54504475, 
2219663.35040187 386869.13042807, 2219659.64303058 386850.25207534, 
2219672.66761980 386847.66674281))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(54, 18962, 'BLD', 'BLD', db2gse.st_geometry 
('polygon (( 2219556.03164537 387046.25513130, 2219557.49509154 387055.03576599, 
2219547.05591105 387056.74309940, 2219545.59246912 387047.96251973, 
2219556.03164537 387046.25513130))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(55, 18833, 'BLD', 'RES', db2gse.st_geometry 
('polygon (( 2219383.93139678 387137.86633157, 2219389.05343086 387190.74523511, 
2219359.00421054 387193.57452260, 2219353.93090903 387140.69568256, 
2219383.93139678 387137.86633157))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(56, 18884, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(57, 19071, 'BLD', 'RES', db2gse.st_geometry 
('polygon (( 2219682.28129249 386891.68291590, 2219686.92111827 386918.49082923, 
2219640.56580254 386926.48163888, 2219635.92597252 386899.67372556, 
2219682.28129249 386891.68291590))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(58, 19044, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(59, 19027, 'BLD', 'RES', db2gse.st_geometry 
('polygon (( 2219363.11822986 386945.42381000, 2219367.93141545 386973.32232908, 
2219320.06718025 386981.52254956, 2219315.29857060 386953.62397969, 
2219363.11822986 386945.42381000))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(60, 19082, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(61, 19105, 'BLD', 'BLD', db2gse.st_geometry 
('polygon (( 2219424.32229434 386872.05730772, 2219426.12719873 386882.05749711, 
2219416.56607240 386883.81360119, 2219414.76116801 386873.81342026, 
2219424.32229434 386872.05730772))', 17));

INSERT INTO BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(62, 19120, 'BLD', 'BLD', db2gse.st_geometry 
('polygon (( 2219553.73895382 386851.52038802, 2219555.25115373 386859.91077266, 
2219545.25100667 386861.81323532, 2219543.69000222 386853.42285069, 
2219553.73895382 386851.52038802))', 17));

COMMIT;

Informix

For each record inserted, you need an object ID value. Execute the next_row_id function to obtain a value.

EXECUTE FUNCTION "sde".next_row_id('tbl_owner',40);

ret_code 0
err_msg
rowid 31

1 row(s) retrieved.

Repeat this for each INSERT statement. Since you are inserting 20 records, you need 20 values.

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(31, 18907, 'BLD', 'RES', db2gse.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))', 
17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(32, 19053, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(33, 19136, 'BLD', 'BLD', db2gse.st_geoometry 
('polygon (( 2219733.93687411 386826.88586815, 2219735.30274506 386834.88599003, 
2219725.20502702 386836.59337847, 2219723.83915606 386828.59325658, 
2219733.93687411 386826.88586815))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(34, 18822, 'BLD', 'BLD', db2gse.st_geometry 
('polygon (( 2219318.56450844 387185.37926723, 2219320.27185454 387197.62335210, 
2219311.29614139 387198.94049048, 2219309.58880798 387186.69635058, 
2219318.56450844 387185.37926723))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(35, 19095, 'BLD', 'BLD', db2gse.st_geometry 
('polygon (( 2219357.88220142 386887.66730143, 2219360.46761861 386898.54553227, 
2219350.56500020 386900.98462474, 2219347.97961264 386890.10638120, 
2219357.88220142 386887.66730143))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(36, 18863, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(37, 19038, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(38, 18859, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(39, 18921, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(40, 18000, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(41, 19116, 'BLD', 'BLD', db2gse.st_geometry 
('polygon (( 2219672.66761980 386847.66674281, 2219676.37499955 386866.54504475, 
2219663.35040187 386869.13042807, 2219659.64303058 386850.25207534, 
2219672.66761980 386847.66674281))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(42, 18962, 'BLD', 'BLD', db2gse.st_geometry 
('polygon (( 2219556.03164537 387046.25513130, 2219557.49509154 387055.03576599, 
2219547.05591105 387056.74309940, 2219545.59246912 387047.96251973, 
2219556.03164537 387046.25513130))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(43, 18833, 'BLD', 'RES', db2gse.st_geometry 
('polygon (( 2219383.93139678 387137.86633157, 2219389.05343086 387190.74523511, 
2219359.00421054 387193.57452260, 2219353.93090903 387140.69568256, 
2219383.93139678 387137.86633157))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(44, 18884, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(45, 19071, 'BLD', 'RES', db2gse.st_geometry 
('polygon (( 2219682.28129249 386891.68291590, 2219686.92111827 386918.49082923, 
2219640.56580254 386926.48163888, 2219635.92597252 386899.67372556, 
2219682.28129249 386891.68291590))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(46, 19044, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(47, 19027, 'BLD', 'RES', db2gse.st_geometry 
('polygon (( 2219363.11822986 386945.42381000, 2219367.93141545 386973.32232908, 
2219320.06718025 386981.52254956, 2219315.29857060 386953.62397969, 
2219363.11822986 386945.42381000))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(48, 19082, 'BLD', 'RES', db2gse.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))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(49, 19105, 'BLD', 'BLD', db2gse.st_geometry 
('polygon (( 2219424.32229434 386872.05730772, 2219426.12719873 386882.05749711, 
2219416.56607240 386883.81360119, 2219414.76116801 386873.81342026, 
2219424.32229434 386872.05730772))', 17));

INSERT INTO buildings
(objectid, fid, type, subtype, shape)
VALUES
(50, 19120, 'BLD', 'BLD', db2gse.st_geometry 
('polygon (( 2219553.73895382 386851.52038802, 2219555.25115373 386859.91077266, 
2219545.25100667 386861.81323532, 2219543.69000222 386853.42285069, 
2219553.73895382 386851.52038802))', 17));
COMMIT;

Now, start ArcMap and preview the records you just inserted.

Start ArcMap by clicking Start > All Programs > ArcGIS > ArcMap 10.
Click the Catalog window button to open the Catalog window.
Connect to the geodatabase in which you created the buildings feature class.
Drag the buildings feature class into the ArcMap table of contents.

You can see the features you inserted into the feature class using SQL.

Applying a definition query

You can apply a definition query to a layer to display features with certain attributes. 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.

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 feature class.

The following set of steps has you import a second feature class, then use a definition query to display a subset of building features that are within 50 meters of water mains.

You must install a geodatabase containing the water_lines feature class by running the ArcGIS Tutorial installation.

Steps:

Install the ArcGIS Tutorial data.
You can choose a complete installation or choose a custom installation and only install the SQL Example data under the Applications list on the Select Features dialog box of the ArcGIS Tutorial Data installation wizard.
A geodatabase named sql_examples is installed in ArcGIS > ArcTutor > SQL Examples.
Start ArcMap by clicking Start > All Programs > ArcGIS > ArcMap 10.
Click the Catalog Window button .
Add a folder connection to the sql_example geodatabase in ArcGIS > ArcTutor > SQL Examples.
1. Click the Connect To Folder button .
2. Navigate to ArcGIS > ArcTutor > SQL Examples.
3. Click OK.

Now that you have a connected to the sql_example geodatabase, import the water_lines feature class to your ArcSDE geodatabase.

Steps:

In the Catalog window, right-click the ArcSDE geodatabase in which you created the buildings feature class.
Point to Import and click Feature Class (single).
The Feature Class To Feature Class geoprocessing tool opens.
Click the Browse button next to the Input Features text box.
The Input Features dialog box opens.
Navigate to the sql_example geodatabase.
Click the water_lines feature class and click Add.
Type water in the Output Feature Class text box.
Specify the configuration keyword to be used to define the feature class storage.
- If your default geometry storage type is ST_Geometry, you do not have to alter the geodatabase settings; the value for the DEFAULTS keyword will be used.
- If your geodatabase is in Oracle or PostgreSQL and the default geometry storage is something other than ST_Geometry, click Geodatabase Settings (optional), click the arrow next to the Configuration Keyword (optional) text box, then choose ST_GEOMETRY (or whichever custom configuration keyword you created for ST_GEOMETRY storage) from the drop-down list.
Click OK to import the data.

Prerequisite:

Add the buildings and water feature classes to ArcMap and define a query to display a subset of building features that are within 25 meters of water mains.

Steps:

Add the buildings and water feature classes to the map by selecting them from the ArcSDE geodatabase connection and dragging them into the table of contents.
Right-click the buildings layer in the table of contents and click Properties.
Click the Definition Query tab.

Create an expression to identify the particular features in the layer you want to display. The following queries find all the buildings within 25 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_intersects (b.shape, sde.st_buffer (w.shape, 25)) = 1)

For PostgreSQL:

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

For DB2:

objectid IN (SELECT b.objectid FROM BUILDINGS b, WATER w
WHERE w.watertype = 'MAIN' 
AND db2gse.st_intersects (b.shape, db2gse.st_buffer (w.shape, 25)) = 1)

For Informix:

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

Click OK.

Comparing the results of the definition query to Select By Attribute and Select By Location

You can see that the same results are returned if you use the ArcMap Select By Attribute tool to find all the main water lines, then use the Select By Location tool to find the buildings within 25 meters of a main water line. This is the same thing you did with the SQL you specified in the definition query.

Steps:

If you closed ArcMap, restart it.
Delete the definition query you created in the previous section.
1. Right-click the buildings layer in the table of contents and click Properties.
2. Click the Definition Query tab.
3. Highlight the query in the Definition Query text box and click Delete.
4. Click OK to close the Layer Properties dialog box.

Now open the Select By Attributes dialog box.

Click the Selection drop-down menu.
Click Select By Attributes.
Choose the water feature class from the Layer list.
Make sure the Create a new selection method is set.
Type WATERTYPE = 'MAIN' in the query box.
Click Verify to confirm the SQL statement is valid.
Click OK.
Click OK to close the Select By Attributes dialog box.

Now open the Select By Location dialog box.

Click the Selection drop-down menu.
Click Select By Location.
In the Target layer(s) box, check the buildings feature class.
Choose the water feature class from the Source layer drop-down list.
Check Use selected features so that you are only using the water main features.
Choose Target layer(s) features intersect the Source layer feature from the Spatial selection method drop-down list.
Check the Apply a search distance box.
Type 25 and choose meters from the drop-down list.
Click OK.

The same features from the buildings feature class will be displayed in the selected set as were returned when you used a definition query.

2/5/2013