工作流：对现有要素类使用 SQL

此主题仅适用于 ArcEditor 和 ArcInfo。

复杂性:中级 数据要求:ArcGIS 教程数据安装程序

将空间类型与 ArcGIS 结合使用的一个主要功能是能够通过 SQL 添加和编辑几何图形。本主题中的示例将介绍如何使用 ArcGIS Desktop 创建要素类，然后使用 SQL 对该要素类进行编辑。此外，还分别使用 SQL 和 ArcGIS Desktop 对要素类执行空间选择以验证结果是一样的。

在 ArcCatalog 中创建空要素类

此示例将介绍如何在 ArcCatalog 中创建空要素类。

步骤:
  1. 启动 ArcCatalog，然后展开目录树中的数据库连接文件夹。
  2. 连接到将要在其中创建包含 ST_Geometry 列的空要素类的地理数据库。
  3. 右键单击该地理数据库，指向新建，然后单击要素类。
  4. 在名称文本框中输入 buildings。
  5. 在本示例中，要素类的类型为多边形，因此确保在类型中选择了多边形要素，然后单击下一步。
  6. 选择 NAD_1983_UTM_Zone_13N 作为坐标系。为此，依次展开 Projected Coordinate Systems 文件夹、UTM 文件夹以及 NAD 1983 文件夹，然后单击 NAD 1983 UTM Zone 13N。

    浏览至该坐标系。

  7. 单击下一步。
  8. 单击下一步接受默认 x,y 容差。
  9. 使用的配置关键字取决于在 DBTUNE 表中对 DEFAULTS 关键字的 GEOMETRY_STORAGE 参数所做的设置。
    • 如果是在 IBM DB2 或 Informix 地理数据库中创建要素类，或是在使用 ST_Geometry 作为其默认几何存储的 Oracle 或 PostgreSQL 地理数据库中创建要素类，则可接受默认配置关键字，然后单击下一步。
    • 如果是在默认几何存储类型不是 ST_Geometry 的 Oracle 或 PostgreSQL 地理数据库中创建要素类，则请选择使用配置关键字，从下拉列表中选择存储了 GEOMETRY_STORAGE 类型 ST_GEOMETRY 的配置关键字，然后单击下一步。
  10. 添加以下具有这些定义的属性字段，方法是：在字段名称文本框中输入字段名称，从数据类型下拉列表中选择数据类型，然后在必要时输入长度。
    注意注意:

    OBJECTID 和 SHAPE 字段自动显示在要素类中。您只需要添加其他字段。请确保以列出的顺序进行添加。

    字段名

    数据类型

    字段属性

    FID

    Long Integer

    允许空值

    TYPE

    Text

    长度 = 3

    允许空值

    SUBTYPE

    Text

    长度 = 6

    允许空值

  11. 单击完成。

现有您就拥有了名为 buildings 的空要素类。接下来，使用 SQL 确定空间参考 ID 并向该要素类添加要素。

确定要素类的 SRID

必须先确定要素类的 SRID 值，才能向该要素类添加要素。SRID 是在地理数据库中创建要素类时分配的，其存储在各种 ArcSDE 地理数据库系统表中。

为确定所创建要素类的 ST_Geometry SRID，可使用 SQL SELECT 语句从 Oracle 的 ST_GEOMETRY_COLUMNS 表、Informix 和 DB2 的 geometry_columns 表以及 PostgreSQL 的 sde_geometry_columns 表中选择 SRID 值。

步骤:
  1. 打开 SQL 编辑器（例如 SQL*Plus for Oracle 或 psql for PostgreSQL）。

    请确保登录时使用的用户身份与在 ArcGIS Desktop 中创建 buildings 要素类时登录所使用的用户身份相同。

  2. 执行以下 SQL 语句以确定要素类的 SRID。
    • 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';

当在此要素类中插入记录时，会使用这些查询返回的 SRID。

确定要素类的 REGISTRATION_ID

在使用 SQL 向要素类中插入记录之前，必须查询 TABLE_REGISTRY 表以获取该要素类的 REGISTRATION_ID。

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

您将使用从此查询返回的数值来获取 objectID 值。

所有要素类都包含 objectID 列。该列的值由地理数据库生成。因此，使用 SQL 向现有要素类中插入记录时，必须执行一个工具（作为 SQL 命令的一部分）来获取下一个可用的 objectID 值并将其插入到 objectID 列中。

使用 SQL 向要素类中插入要素

使用 SQL 编辑器将记录添加到要素类的业务表中，然后在 ArcCatalog 中预览要素。

在向要素类中插入记录时，请使用在前两个部分中获得的 SRID 和 REGISTRATION_ID。

使用特定于 DBMS 的工具来获取对象 ID 值。在 Oracle 和 PostgreSQL 中，可在子查询中使用此工具获取对象 ID 值。对于 DB2 和 Informix，先执行此工具获取对象 ID 值，然后在 INSERT 语句中使用该值。

注意注意:

在 DB2 中的地理数据库内插入数据时，必须使用 db2gse. 修饰空间类型函数。例如，在下面的示例中，ST_Geometry 会变为 db2gse.ST_Geometry。对于其他 DBMS，使用 sde. 修饰空间类型函数。

步骤:
  1. 执行以下命令以在要素类中插入要素。

    请确保在工具查询中使用此要素类的 SRID 代替 INSERT 语句末尾的 17，使用此要素类所有者的用户名代替 tbl_owner，使用此要素类的 REGISTRATION_ID 代替 40。

    • 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

      使用创建 buildings 要素类时所用的用户名代替函数名称中的 gis。

      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

      对于每条插入的记录，都需要对象 ID 值。调用 next_row_id 过程来获取值。

      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

      对每个 INSERT 语句重复此操作。因为要插入 20 条记录，所以需要 20 个值。

      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

      对于每条插入的记录，都需要对象 ID 值。执行 next_row_id 函数来获取值。

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

ret_code 0
err_msg
rowid 31

1 row(s) retrieved.

      对每个 INSERT 语句重复此操作。因为要插入 20 条记录，所以需要 20 个值。

      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;
    2. 现在，启动 ArcMap 并预览刚刚插入的记录。
  2. 单击开始 > 所有程序 > ArcGIS > ArcMap 10 启动 ArcMap。
  3. 单击目录窗口按钮 Catalog 窗口 打开目录窗口。
  4. 连接到在其中创建了 buildings 要素类的地理数据库。
  5. 将 buildings 要素类拖动到 ArcMap 内容列表中。

    您会看到使用 SQL 在此要素类中插入的要素。

应用定义查询

您可以将定义查询应用到某个图层以显示具有某些属性的要素。例如，您可能只希望显示人口数超过某个阈值的城市。可以输入您自己的表达式，也可以使用“查询构建器”来帮助您构建查询表达式。要将所有要素重新添加回显示中，只需删除查询即可。

对于空间类型，在 ArcMap 中执行的定义查询可以包含空间组成部分。查询可以包括对存储过程的调用，存储过程可能也包含空间组成部分。此功能避免了客户端到服务器的处理过程，使您在管理系统资源时有更多选择余地。

空间查询通常比较来自两个不同数据集中的要素。因此，需要向地理数据库添加另外一个要素类以便将其与 buildings 要素类进行比较。

下面一组步骤介绍了导入第二个要素类，然后使用定义查询来显示建筑物要素（位于给水干管 50 米范围内）的子集的过程。

您必须通过运行 ArcGIS 教程安装程序来安装包含 water_lines 要素类的地理数据库。

步骤:
  1. 安装 ArcGIS 教程数据。
  2. 您可以选择完整安装或选择自定义安装，如果选择自定义安装，只安装“ArcGIS 教程数据”安装向导的选择要素 对话框中的应用程序列表下的 SQL 示例数据。

    名为 sql_examples 的地理数据库已安装在 ArcGIS > ArcTutor > SQL Examples 中。

  3. 单击开始 > 所有程序 > ArcGIS > ArcMap 10 启动 ArcMap。
  4. 单击“目录窗口”按钮 Catalog 窗口。
  5. 添加与 sql_example 地理数据库（位于 ArcGIS > ArcTutor > SQL Examples 中）的文件夹连接。
    1. 单击“连接到文件夹”按钮 连接到文件夹。
    2. 导航到 ArcGIS > ArcTutor > SQL Examples。
    3. 单击确定。

连接到 sql_example 地理数据库后，即可将 water_lines 要素类导入到 ArcSDE 地理数据库。

步骤:
  1. 在“目录”窗口中，右键单击在其中创建 buildings 要素类的 ArcSDE 地理数据库。
  2. 指向导入，然后单击要素类(单个)。

    将打开“要素类至要素类”地理处理工具。

  3. 单击输入要素文本框旁边的“浏览”按钮。

    将打开输入要素 对话框。

  4. 导航到 sql_example 地理数据库。
  5. 单击 water_lines 要素类，然后单击添加。
  6. 在输出要素类文本框中输入 water。
  7. 指定用于定义要素类存储的配置关键字。
    • 如果默认几何存储类型为 ST_Geometry，则无需更改地理数据库设置；将使用 DEFAULTS 关键字的值。
    • 如果地理数据库在 Oracle 或 PostgreSQL 中并且默认几何存储类型是 ST_Geometry 以外的类型，则请单击地理数据库设置(可选)，单击配置关键字(可选) 文本框旁边的箭头，然后从下拉列表中选择 ST_GEOMETRY（或者是为 ST_GEOMETRY 存储类型创建的任何自定义配置关键字）。
  8. 单击确定导入数据。

先决条件:

将 buildings 要素类和 water 要素类添加到 ArcMap，然后定义查询以显示给水干管长度小于 25 米的建筑物要素子集。

步骤:
  1. 将 buildings 要素类和 water 要素类添加到地图，方法是：在 ArcSDE 地理数据库连接中将其选中，然后拖入内容列表中。
  2. 在内容列表中右键单击 buildings 图层，然后单击属性。
  3. 单击定义查询选项卡。
  4. 创建表达式以标识图层中要显示的特定要素。以下查询将找出给水干管长度小于 25 米的所有建筑物。在图层属性 的定义查询框中输入适合 DBMS 的表达式。
    • 对于 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)
    • 对于 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')
    • 对于 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)
    • 对于 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)))
  5. 单击确定。

将定义查询的结果与“按属性选择”并“按位置选择”的结果进行比较

如果使用 ArcMap 的“按属性选择”工具查找所有给水干管线，然后使用“按位置选择”工具查找长度小于 25 的给水干管线，则将返回相同的结果。这与您在定义查询中使用指定的 SQL 命令的效果相同。

步骤:
  1. 如果关闭了 ArcMap，请将其重新启动。
  2. 删除在前一部分所创建的定义查询。
    1. 在内容列表中右键单击 buildings 图层，然后单击属性。
    2. 单击定义查询选项卡。
    3. 使定义查询文本框中的查询高亮显示，然后单击“删除”。
    4. 单击确定关闭图层属性 对话框。
    3. 按属性选择 对话框随即打开。
  3. 单击选择下拉菜单。
  4. 单击按属性选择。
  5. 从图层列表中选择 water 要素类。
  6. 请确保设置创建新选择内容方法。
  7. 在查询框中输入 WATERTYPE = 'MAIN'。
  8. 单击验证以确认 SQL 语句有效。
  9. 单击确定。
  10. 单击确定关闭按属性选择 对话框。
    11. 按位置选择 对话框随即打开。
  11. 单击选择下拉菜单。
  12. 单击按位置选择。
  13. 在目标图层框中，选中 buildings 要素类。
  14. 从源图层下拉列表中选择 water 要素类。
  15. 选中使用所选要素，这样将仅使用给水干管要素。
  16. 从空间选择方法下拉列表中选择目标图层要素与源图层要素相交。
  17. 选中应用搜索距离复选框。
  18. 输入 25，然后从下拉列表中选择米。
  19. 单击确定。

此时在所选集中显示的要素（来自 buildings 要素类）与使用定义查询时返回的结果相同。


版权所有 © 1995-2011 Esri. 保留所有权利。
3/7/2012