Enhancing ArcGIS functionality using spatial types (ArcInfo and ArcEditor only) |
|
Release 9.3 |
NOTE: If the owner of a dataset stored in Oracle changes the dataset schema using SQL by doing such things as adding a field or redefining a field length, that user must open the dataset in ArcGIS Desktop to update the information in the system tables. If the owner does not do this, the next time another user tries to view the data, he or she will get a permissions error and will not be able to access the data.
SELECT table_name, srid
FROM sde.st_geometry_columns
WHERE table_name = 'buildings';
SELECT f_table_name, srid
FROM sde.sde_geometry_columns
WHERE f_table_name = 'buildings';
SELECT layer_table, srid
FROM db2gse.geometry_columns
WHERE layer_table = 'buildings';
SELECT f_table_name, srid
FROM sde.geometry_columns
WHERE f_table_name = 'buildings';
NOTE: When inserting data to geodatabases in DB2, you need to qualify the spatial type functions with "db2gse.". For instance, in the following examples, ST_Geometry becomes db2gse.ST_Geometry. For the other DBMSs, you can qualify the spatial type functions with "sde.".
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(1, 0, 12972, 18907, '22JSM0000023946', 'BLD', 'RES', 6, 0, 0, 1,
st_geometry ('polygon (( 2219520.56768649 387051.66985716, 2219525.34823696 387079.52399077,
2219536.03133855 387077.71905252, 2219539.05578917 387095.47546386, 2219528.17754562 387097.32910505,
2219528.61661291 387099.81695550, 2219489.00622816 387106.54876471, 2219480.81097279 387058.40167483,
2219520.56768649 387051.66985716))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(2, 0, 13090, 19053, '22JSM0000024089', 'BLD', 'RES', 6, 0, 0, 1,
st_geometry ('polygon (( 2219612.86639158 386903.72709265, 2219612.86832175 386907.20474822,
2219619.82528792 386906.03131444, 2219624.05814397 386930.50637511, 2219602.30717225 386934.19443199,
2219602.68435556 386936.33176596, 2219595.81121637 386937.54715132, 2219595.51783041 386935.61930861,
2219582.14872687 386937.88243384, 2219577.95779702 386913.07208642, 2219595.22446985 386910.09649113,
2219593.59000886 386900.45735373, 2219612.86269632 386897.06148069, 2219612.86639158 386903.72709265))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(3, 0, 13165, 19136, '22JSM0000024169', 'BLD', 'BLD', 14, 0, 0, 1,
st_geoometry ('polygon (( 2219733.93687411 386826.88586815, 2219735.30274506 386834.88599003,
2219725.20502702 386836.59337847, 2219723.83915606 386828.59325658, 2219733.93687411 386826.88586815))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(4, 0, 12898, 18822, '22JSM0000023861', 'BLD', 'BLD', 14, 0, 0, 1,
st_geometry ('polygon (( 2219318.56450844 387185.37926723, 2219320.27185454 387197.62335210,
2219311.29614139 387198.94049048, 2219309.58880798 387186.69635058, 2219318.56450844 387185.37926723))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(5, 0, 13129, 19095, '22JSM0000024131', 'BLD', 'BLD', 14, 0, 0, 1,
st_geometry('polygon (( 2219357.88220142 386887.66730143, 2219360.46761861 386898.54553227,
2219350.56500020 386900.98462474, 2219347.97961264 386890.10638120, 2219357.88220142 386887.66730143))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(6, 0, 12933, 18863, '22JSM0000023902', 'BLD', 'RES', 6, 0, 0, 1,
st_geometry ('polygon (( 2219640.86224883 387097.71935934, 2219658.37473060 387147.67138324,
2219620.66681275 387160.89111018, 2219609.69104055 387129.67108043, 2219619.00825848 387126.35393804,
2219612.47155737 387107.67078229, 2219640.86224883 387097.71935934))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(7, 0, 13080, 19038, '22JSM0000024074', 'BLD', 'RES', 6, 0, 0, 1,
st_geometry ('polygon (( 2219498.86004627 386911.32623002, 2219505.93331369 386953.22930633,
2219453.63980640 386962.05871170, 2219448.56655992 386931.86309469, 2219465.10339963 386929.08257787,
2219463.15216206 386917.37511856, 2219498.86004627 386911.32623002))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(8, 0, 12929, 18859, '22PWS0000020029', 'BLD', 'RES', 6, 0, 0, 1,
st_geometry ('polygon (( 2219537.93380545 387110.93908628, 2219546.81201112 387161.33014361,
2219498.76248799 387169.86682333, 2219493.68920765 387140.93957403, 2219516.07980240 387136.98828165,
2219512.32366468 387115.52454135, 2219537.93380545 387110.93908628))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(9, 0, 12985, 18921, '22JSM0000023960', 'BLD', 'RES', 6, 0, 0, 1,
st_geometry ('polygon (( 2219630.32549736 387032.49884228, 2219638.42319022 387080.93859854,
2219602.27627682 387087.03625775, 2219599.44695969 387070.06042272, 2219594.95910946 387070.84088050,
2219589.69069987 387039.32824786, 2219630.32549736 387032.49884228))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(10, 0, 13075, 0, '22PWS0000020046', 'BLD', 'RES', 6, 0, 0, 1,
st_geometry ('polygon (( 2219428.55884565 386927.35910468, 2219434.90911597 386965.59318031,
2219412.50672431 386969.25340210, 2219412.90361568 386971.59064420, 2219405.49494299 386972.82544978,
2219405.18625535 386970.70870430, 2219391.47137188 386973.00185724, 2219387.14966448 386946.93921840,
2219404.70113486 386944.07272009, 2219402.67258040 386931.63676100, 2219428.55884565 386927.35910468))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(11, 0, 13149, 19116, '22PWS0000020056', 'BLD', 'BLD', 14, 0, 0, 1,
st_geometry ('polygon (( 2219672.66761980 386847.66674281, 2219676.37499955 386866.54504475,
2219663.35040187 386869.13042807, 2219659.64303058 386850.25207534, 2219672.66761980 386847.66674281))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(12, 0, 13013, 18962, '22JSM0000024001', 'BLD', 'BLD', 14, 0, 0, 1,
st_geometry ('polygon (( 2219556.03164537 387046.25513130, 2219557.49509154 387055.03576599,
2219547.05591105 387056.74309940, 2219545.59246912 387047.96251973, 2219556.03164537 387046.25513130))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(13, 0, 12905, 18833, '22JSM0000023872', 'BLD', 'RES', 6, 0, 0, 1,
st_geometry ('polygon (( 2219383.93139678 387137.86633157, 2219389.05343086 387190.74523511,
2219359.00421054 387193.57452260, 2219353.93090903 387140.69568256, 2219383.93139678 387137.86633157))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(14, 0, 12951, 18884, '22JSM0000023923', 'BLD', 'RES', 6, 0, 0, 1,
st_geometry ('polygon (( 2219373.44344985 387075.37756489, 2219382.32162166 387124.54907598,
2219362.80911894 387128.11010561, 2219359.44323973 387109.62200293, 2219339.54046156 387113.28058238,
2219333.97942791 387082.54840752, 2219373.44344985 387075.37756489))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(15, 0, 13107, 19071, '22JSM0000024107', 'BLD', 'RES', 6, 0, 0, 1,
st_geometry ('polygon (( 2219682.28129249 386891.68291590, 2219686.92111827 386918.49082923,
2219640.56580254 386926.48163888, 2219635.92597252 386899.67372556, 2219682.28129249 386891.68291590))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(16, 0, 13086, 19044, '22JSM0000024080', 'BLD', 'RES', 6, 0, 0, 1,
st_geometry ('polygon (( 2219553.98285375 386902.15533258, 2219555.78774544 386913.03356343,
2219560.95856289 386912.15551350, 2219565.78790520 386940.83886287, 2219517.98226930 386948.93658960,
2219513.15292276 386920.20440606, 2219516.17736068 386919.66779319, 2219514.37246900 386908.88714178,
2219553.98285375 386902.15533258))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(17, 0, 13072, 19027, '22PWS0000020045', 'BLD', 'RES', 6, 0, 0, 1,
st_geometry ('polygon (( 2219363.11822986 386945.42381000, 2219367.93141545 386973.32232908,
2219320.06718025 386981.52254956, 2219315.29857060 386953.62397969, 2219363.11822986 386945.42381000))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(18, 0, 13118, 19082, '22JSM0000024118', 'BLD', 'RES', 6, 0, 0, 1,
st_geometry ('polygon (( 2219744.18548833 386871.29585958, 2219746.55761318 386884.95597445,
2219743.44931865 386885.52860025, 2219747.49828784 386908.59539393, 2219704.96369012 386916.03897901,
2219700.42393269 386890.23190579, 2219729.62557524 386885.11957759, 2219727.74423440 386874.19963643,
2219744.18548833 386871.29585958))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(19, 0, 13138, 19105, '22JSM0000024141', 'BLD', 'BLD', 14, 0, 0, 1,
st_geometry ('polygon (( 2219424.32229434 386872.05730772, 2219426.12719873 386882.05749711,
2219416.56607240 386883.81360119, 2219414.76116801 386873.81342026, 2219424.32229434 386872.05730772))', 3));
INSERT INTO buildings
(objectid, fid, building_, building_i, tag, type, subtype,
symbol, lock__id, phase__id, zoom_symbo, shape)
VALUES
(20, 0, 13151, 19120, '22JSM0000024154', 'BLD', 'BLD', 14, 0, 0, 1,
st_geometry ('polygon (( 2219553.73895382 386851.52038802, 2219555.25115373 386859.91077266,
2219545.25100667 386861.81323532, 2219543.69000222 386853.42285069, 2219553.73895382 386851.52038802))', 3));
COMMIT;
objectid IN (SELECT b.objectid FROM buildings b, water w
WHERE w.watertype = 'MAIN'
AND sde.st_overlaps (b.shape, sde.st_buffer (w.shape, 50)) = 1)
objectid IN (SELECT b.objectid FROM buildings b, water w
WHERE w.watertype = 'MAIN'
AND sde.st_overlaps (b.shape, sde.st_buffer (w.shape, 50)) = 't')
objectid IN (SELECT b.objectid FROM buildings b, water w
WHERE w.watertype = 'MAIN'
AND db2gse.st_overlaps (b.shape, db2gse.st_buffer (w.shape, 50)) = 1)
objectid IN (SELECT b.objectid FROM buildings b, water w
WHERE w.watertype = 'MAIN'
AND sde.st_overlaps (b.shape, sde.st_buffer (w.shape, 50)))
NOTE: Autoregistration of feature classes in PostgreSQL or feature classes in Oracle with an ST_Geometry column is not supported. To use autoregistration with DB2, Informix, or Oracle Spatial feature classes, set the SERVER_CONFIG parameter DISABLEAUTOREG to FALSE. See ArcSDE initialization parameters for more information about DISABLEAUTOREG.
The following is an example of registering a table called comm_bldgs containing polygon geometries (–e a) in a spatial column called SHAPE. The table has an integer column called OBJECTID that will be used as a unique feature identifier column maintained by ArcSDE (–C OBJECTID,SDE). This feature class table is first created from an existing table via SQL and then registered with ArcSDE.CREATE TABLE comm_bldgs AS SELECT * FROM buildings
WHERE subtype = 'COM';
sdelayer –o register –l comm_bldgs,shape –e a –C OBJECTID,SDE –R <SRID> –u <user> –p <pw>
NOTE: If the data in your table uses multiple SRIDs, registration will fail. ArcSDE does not support multiple SRIDs in the same table.
If the table does not contain any data and you register the table with ArcSDE but do not specify the –R option with a valid SRID, the default SRID will be used. For PostgreSQL and Oracle tables with ST_Geometry columns, the default is 0. The SRID 0 is present mainly for testing purposes; it does not actually assign a spatial reference. If you later try to add features to the table that have a valid SRID other than 0, it will fail because the SRIDs will not be the same. Once an SRID is assigned to a table, it cannot be changed; you have to delete the feature class and re-create it with the correct SRID. For additional information on using the sdelayer command, consult the ArcSDE Administration Command Reference provided with the ArcSDE component of ArcGIS Server Enterprise. This has registered your table with ArcSDE, making it a feature class. This adds a record to the LAYERS, GEOMETRY_COLUMNS, COLUMN_REGISTRY, and TABLE_REGISTRY system tables in Oracle, DB2, or Informix or the sde_layers, sde_geometry_columns, sde_column_registry, and sde_table_registry tables in PostgreSQL. At this point, you are able to view, select, and perform nonversioned edits on the feature class in ArcGIS Desktop. You can also register it as versioned. If you need the feature class to participate in geodatabase functionality, such as relationship classes, topology, geometric networks, cadastral fabrics, terrains, and schemas, or have subtypes, default values, domains, or validation rules, it must also be registered with the geodatabase. Registering a feature class with the geodatabase adds an ObjectID field to the table. This field will be called ObjectID. If an ObjectID field already exists in the dataset, a field named ID is added to the business table. This ID field's data type is ObjectID. Registering the feature class with the geodatabase also adds a record to the GDB_OBJECTCLASSES and GDB_FEATURECLASSES system tables. You can register the datasets in ArcCatalog as follows:
Tip
|