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

本主题仅适用于 ArcGIS for Desktop Standard 和 ArcGIS for Desktop Advanced。

复杂程度:中级 数据要求:ArcGIS Tutorial Data for Desktop

在 ArcGIS 中使用空间类型的一个主要特点是能够通过 SQL 添加和编辑几何。本主题中的示例将说明如何使用 ArcGIS for Desktop 创建要素类，然后如何使用 SQL 对要素类进行编辑。还可使用 SQL 和 ArcGIS for Desktop 在要素类中进行空间选择，以显示结果相同。

在 ArcCatalog 中创建空要素类

本例介绍如何在 ArcCatalog 中创建空要素类。

步骤:
  1. 启动 ArcCatalog，然后展开目录树中的数据库连接文件夹。
  2. 连接到要在其中创建空要素类（包含 ST_Geometry 列）的地理数据库。

    请确保以可以在数据库中创建数据的用户身份进行连接。

  3. 右键单击此地理数据库，指向新建，然后单击要素类。
  4. 在名称文本框中输入 buildings。
  5. 在本例中，要素类类型为面，因此请确保为类型选择了面要素，然后单击下一步。
  6. 为坐标系选择 NAD_1983_UTM_Zone_13N。要执行此操作，请依次展开“投影坐标系”文件夹、“UTM”文件夹、“NAD 1983”文件夹，然后单击 NAD 1983 UTM 带 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 于在地理数据库中创建要素类时指定，并且 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 for Desktop 中创建 buildings 要素类时的用户身份进行登录。

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

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

确定要素类的 REGISTRATION_ID

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

Oracle

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

REGISTRATION_ID
40

PostgreSQL

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

REGISTRATION_ID
40

DB2

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

REGISTRATION_ID
40

Informix

SELECT registration_id
FROM sde.table_registry
WHERE table_name = 'buildings'
AND owner = '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 语句末尾的 26913，使用此要素类所有者的用户名代替 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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

COMMIT;
    • PostgreSQL

      使用创建 buildings 要素类时所用的用户名代替函数名称中的 gis。另外，请确保使用此要素类的 SRID 代替 INSERT 语句末尾的 26913，使用此要素类的 REGISTRATION_ID 代替 get_ids 函数名中的 40。

      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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);

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))', 
26913)
);
    • 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 个值。

      请确保使用要素类的 SRID 代替以下 INSERT 语句末尾的 26913。

      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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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 个值。

      请确保使用要素类的 SRID 代替以下 INSERT 语句末尾的 26913。

      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))', 
26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));

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))', 26913));
COMMIT;
    2. 现在，启动 ArcMap 并预览刚刚插入的记录。
  2. 单击开始 > 所有程序 > ArcGIS > ArcMap 10.1 启动 ArcMap。
  3. 单击目录窗口按钮 目录 打开目录 窗口。
  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.1 启动 ArcMap。
  4. 单击目录窗口按钮 目录。
  5. 添加到 sql_example 地理数据库（位于 ArcGIS > ArcTutor > SQL Examples）的文件夹连接。
    1. 单击连接到文件夹按钮 连接到文件夹。
    2. 导航到 ArcGIS > ArcTutor > SQL Examples。
    3. 单击确定。

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

步骤:
  1. 在目录 窗口中，右键单击在其中创建了 buildings 要素类的企业级地理数据库。
  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 要素类添加到地图，方法是在地理数据库连接中将其选中，然后拖入内容列表中。
  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 要素类）与使用定义查询时返回的结果相同。

Copyright © 1995-2013 Esri. All rights reserved.
9/15/2013