利用SQL訪問(wèn)地理信息數(shù)據(jù)庫(kù)Geodatabase的方法

1、利用利用SQL訪問(wèn)地理信息數(shù)據(jù)庫(kù)訪問(wèn)地理信息數(shù)據(jù)庫(kù)Geodatabase的方法的方法內(nèi)容概要內(nèi)容概要GDB10 Schame介紹 SQL查詢GDB Schema關(guān)系 SQL查詢XML值SQL操作操作Geodatabase DataSQL操作非版本數(shù)據(jù) ST_Geometry效率解析SQL操作版本數(shù)據(jù)SQL操作影像數(shù)據(jù)SQL操作操作Geodatabase SchemaArcGIS一直在進(jìn)步一直在進(jìn)步ArcObjectsSchemaDatabinarybinaryArcObjectsSQLSchemaDatabinarySQL typeArcObjectsSQLSchemaDataXMLSQL t

2、ypeArcGIS9.2之前之前ArcGISArcGIS10效率的雙刃劍效率的雙刃劍時(shí)間就是金錢，效率就是生時(shí)間就是金錢，效率就是生命命高利潤(rùn)與高風(fēng)險(xiǎn)并存高利潤(rùn)與高風(fēng)險(xiǎn)并存遵循游戲規(guī)則遵循游戲規(guī)則SQLArcObjectDBMSArcSDE technologyGeodatabaseArcGISSQL操作操作GeodatabaseGeodatabase10 SchemaGDB表精簡(jiǎn) XML存儲(chǔ) 支持三種GDBGeodatabase表介紹表介紹GDB_Items該表記錄所有該表記錄所有在在Geodatabase中存中存儲(chǔ)儲(chǔ)的相的相關(guān)關(guān)表、表、要要素類素類、拓?fù)?、拓?fù)?、幾何網(wǎng)絡(luò)等實(shí)

3、際的對(duì)象。幾何網(wǎng)絡(luò)等實(shí)際的對(duì)象。GDB_ItemTypes該表記錄所該表記錄所有有Geodatabase中可中可以以存存儲(chǔ)的儲(chǔ)的枚枚丼類丼類型型。GDB_ItemRelationShips該表記錄要素類與數(shù)據(jù)該表記錄要素類與數(shù)據(jù)集集（工（工作作空間空間）直接直接的的關(guān)系關(guān)系GDB_ItemRelationShipTypes該表主要記錄關(guān)系的相該表主要記錄關(guān)系的相關(guān)關(guān)類型類型GDB表關(guān)系表關(guān)系查詢查詢GDB關(guān)系關(guān)系select aa.origineName, as DesName,aa.ShipTypeName from (select a.*, as ShipType

4、Name, as origineName from gdb_itemrelationships a,gdb_itemrelationshiptypes c,gdb_items b where a.type=c.uuid and a.originid=b.uuid ) aa ,gdb_items dwhere aa.destid=d.uuidSQL查詢查詢XML的值的值SELECTDefinition.value(/DETopology/Name)1,nvarchar(max) AS 拓?fù)涿Q拓?fù)涿Q, Definition.value(/DETopology/DatasetType

5、)1,nvarchar(max) AS 數(shù)據(jù)類型數(shù)據(jù)類型, Definition.value(/DETopology/ClusterTolerance)1,nvarchar(max) AS 容差容差, Definition.value(/DETopology/FeatureClassNames/Name)1,nvarchar(max) AS 參參 與拓?fù)湟仡惻c拓?fù)湟仡? Definition.value(/DETopology/FeatureClassNames/Name)2,nvarchar(max) AS 參參 與拓?fù)湟仡惻c拓?fù)湟仡? Definition.value(/DETop

6、ology/TopologyRules/TopologyRule/TopologyRuleType) 1,nvarchar(max) AS 參與拓?fù)湟?guī)則參與拓?fù)湟?guī)則1, Definition.value(/DETopology/TopologyRules/TopologyRule/TopologyRuleType) 2,nvarchar(max) AS 參與拓?fù)湟?guī)則參與拓?fù)湟?guī)則2, Definition.value(/DETopology/TopologyRules/TopologyRule/TopologyRuleType) 3,nvarchar(max) AS 參與拓?fù)湟?guī)則參與拓?fù)湟?guī)則3F

7、ROM sde.sde.GDB_ITEMSWHERE Name=sde.SDE.czdj_Topology使用使用XML創(chuàng)建對(duì)象創(chuàng)建對(duì)象File GDB APIC+ librarySDK/XMLspecaaesriFieldTypeStringtrue400daynightafternoonaaDEMO1：Geodatabase10 Schema 2：Geodatabase Schema RelationShip 3：Accessing XML Values空間數(shù)據(jù)類空間數(shù)據(jù)類型型空間數(shù)據(jù)類型空間數(shù)據(jù)類型數(shù)據(jù)類型點(diǎn)、線、面空間索引R樹(shù)、格網(wǎng) 不操作符相關(guān)函數(shù)(操作、方法)構(gòu)造函數(shù)：ST_POL

8、YGONE 訪問(wèn)函數(shù)：ST_ASTEXT 關(guān)系操作符：ST_INTERSECTS 返回圖形： ST_BUFFER空間存儲(chǔ)類型空間存儲(chǔ)類型-矢量矢量數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)類型類型描述描述OracleLong Rawv9.3乊前的默認(rèn)存儲(chǔ)BLOBOGC WKB存儲(chǔ)，只支持2維 GeometryST_Geometryv9.3后默認(rèn)存儲(chǔ)SDO_Geometry為Oracle Spatial和Oracle Locator用戶提供SQL ServerBLOBv2008前的默認(rèn)存儲(chǔ)Geometryv2008 存儲(chǔ)平面坐標(biāo)的空間數(shù)據(jù)Geographyv2008 存儲(chǔ)地理坐標(biāo)的空間數(shù)據(jù)PostgreSQLST_Geome

9、tryEsri提供的存儲(chǔ)GeometryPostGIS V8.3.8 & 8.4.1DB2ST_GeometryEsri IBM合作開(kāi)發(fā) Spatial ExtenderInformixST_GeometryEsri IBM合作開(kāi)發(fā) Spatial DataBlade空間存儲(chǔ)類型空間存儲(chǔ)類型-柵格柵格數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)類型類型描述描述OracleBLOBArcGIS10乊前的默認(rèn)存儲(chǔ)ST_RasterArcGIS10乊后，安裝存儲(chǔ)類型，可選SDO_GeoRasterOracle存儲(chǔ)影像SQL ServerBLOBArcGIS10乊前的默認(rèn)存儲(chǔ)ST_RasterArcGIS10乊后，安裝存儲(chǔ)類型，可選

10、PostgreSQLBLOBArcGIS10乊前的默認(rèn)存儲(chǔ)ST_RasterArcGIS10乊后，安裝存儲(chǔ)類型，可選ST_GeometryNameNull?Type- - -ENTITY NUMPTS MINX MINY MAXX MAXY MINZ MAXZ MINM MAXM AREA LEN SRID POINTSNUMBER(38) NUMBER(38) FLOAT(64) FLOAT(64) FLOAT(64)FLOAT(64) FLOAT(64) FLOAT(64) FLOAT(64)FLOAT(64) FLOAT(64) FLOAT(64) NUMBER(38)BLOB高性能(S

11、DEBINARY)標(biāo)準(zhǔn)化(ISO和OGC)圖形自勱驗(yàn)證 使用方便(SQL)ST_GeometryBLOBBLOB VS. ST_GeometrySQL操作非版本化數(shù)據(jù)操作非版本化數(shù)據(jù)使用場(chǎng)景使用場(chǎng)景Simple ModelFull ModelReadWriteEditing Without Version ST_GeometrySQL操作原理操作原理Declare Begin c_func EndextProc.exeSt_shapelib.dllPe.dllSg.dllIPC協(xié)議協(xié)議別名庫(kù)別名庫(kù)ST_SHAPELIBListenersde.st_astext（操作符）（操作符）sde.st_

12、geometry_operator.st_astext_f（函數(shù)）（函數(shù)）SDE.st_geometry_shapelib_pkg.astext（存儲(chǔ)過(guò)程）（存儲(chǔ)過(guò)程）Oracle配配置置ST_Geometry（1） 以ST_Geometry存儲(chǔ)（從9.2乊前升級(jí)配置） Oracle監(jiān)聽(tīng)器對(duì)數(shù)據(jù)庫(kù)的注冊(cè)為靜態(tài)監(jiān)聽(tīng)（Unknown) Oracle監(jiān)聽(tīng)文件引用st_shapelib.dll(非Windows環(huán)境變量） Oracle的Listener文件不TNSNames文件Key一致 extproc（Oracle用戶）必須對(duì)庫(kù)文件的位置具有讀取權(quán)限，并且對(duì)文件具有執(zhí)行權(quán)限 user_librari

13、es 表中，ST_SHAPELIB 的 file_spec 不服務(wù)器上ST_SHAPELIB 的物理文件位置丌匘配Oracle配配置置ST_Geometry（2）St_shapelib.dll引用(分布式安裝）同步引用sg.dll pg.dllOCI.dll必須為服務(wù)器版本SQL訪問(wèn)訪問(wèn)-創(chuàng)建表創(chuàng)建表SQL創(chuàng)建表CREATE TABLE sensitive_areas (area_id integer, name varchar(128),area_size float, type varchar(10), zone sde.st_geometry);CREATE TABLE hazardou

14、s_sites (row_id integer NOT NULL, site_idinteger, name varchar(40), location sde.st_geometry);創(chuàng)建空間表的規(guī)創(chuàng)建空間表的規(guī)則則表中需要一個(gè)唯一、非空的整型列用作 ArcGIS 中的唯一標(biāo)識(shí)符-ObjectID：NUMBER(38) 或或 INTEGER、UNIQUE 且且 NOT NULL 避免在同一個(gè)表中存在多個(gè)空間列(例外）-Create View 請(qǐng)勿使用大小寫(xiě)混合的對(duì)象名稱-除了SQL Server外，其他匙分大小寫(xiě)揑入空間表中的記錄類型必須不為空間列定義的類型相匘配-ST_Polygon ：

15、ST_LineString-ST_Geometry：無(wú)要求 僅為表中的數(shù)據(jù)使用一個(gè)空間參考-數(shù)據(jù)丌在空間參考中；數(shù)據(jù)將無(wú)法正確渲染，甚至?xí)@示錯(cuò)誤消息SQL訪問(wèn)訪問(wèn)-注冊(cè)表注冊(cè)表sdelayer -o register -l testst,zone-e a -C id,SDE -R 10-t ST_GEOMETRY-u aaa -p aaaArcSDE命令注冊(cè)目的：將該表信息注冊(cè)到ArcSDE的SchemaTable_RegistryColumn_RegistryLayersST_Geometry_Columns投影系列表投影系列表Geometry_ColumnsLayers Table_Na

16、me SRIDSpatial_References SRID AUTH_SRID SRTEXTST_Geometry_IndexST_Geometry_Columns Table_NameSRIDST_Spatial_ReferencesSR_NAME SRIDCS_NAME注冊(cè)表約束注冊(cè)表約束ArcSDE命令注冊(cè) 必須是表的所有者才能執(zhí)行注冊(cè) 只能有一個(gè)ST_Geometry列 沒(méi)有其他用戶自定義類型的列 必須是簡(jiǎn)單的集合類型(點(diǎn)線面),可以為多部分的 Geometry 必須是有效的，否則讀取的時(shí)候會(huì)產(chǎn)生丌可預(yù)期的錯(cuò)誤注冊(cè)已有數(shù)據(jù) 注冊(cè)空數(shù)據(jù)表-已經(jīng)存在投影信息-新的投影信息SQL訪問(wèn)訪問(wèn)-

17、編輯表編輯表編輯數(shù)據(jù)（增加、刪除、修改）SQL語(yǔ)句 （AUTH_SRID)1）使用WKT編碼 ST_PolyFromText() 2）使用WKB編碼 ST_PointFromWKB()Auth_SRIDINSERT INTO SENSITIVE_AREAS (area_id, name, area_size, type, zone) VALUES (1, Summerhill Elementary School, 67920.64, school,sde.ST_PolyFromText(polygon (52 28,58 28,58 23,52 23,52 28), 1);INSERT INT

18、O HAZARDOUS_SITES (row_id, site_id, name, location) VALUES (1, 102, W. H. Kleenare Chemical Repository, sde.ST_PointFromText(point (52 24),1);UPDATE hazardous_sites SET location = sde.st_pointfromtext(point(18 57), 1)WHERE site_id = 102;SQL訪問(wèn)訪問(wèn)-創(chuàng)建索引創(chuàng)建索引創(chuàng)建空間索引時(shí)，需要提供以下信息：名稱要定義索引的空間列的名稱格網(wǎng)大?。?Oracle 或 D

19、B2 ）CREATE INDEX sa_idx ON sensitive_areas(zone) INDEXTYPE ISsde.st_spatial_index PARAMETERS(st_grids=1,3,0 st_srid=1);CREATE INDEX hs_idx ON hazardous_sites(location) INDEXTYPE IS sde.st_spatial_index PARAMETERS(st_grids=1,0,0 st_srid=1);創(chuàng)建空間索引非常有用。將數(shù)據(jù)加載或追加到空間表可以減少完成加載所需的總時(shí)間。這也會(huì)減少列索 引中的碎片，從而改善查詢的性能

20、。因此，在您向空間表中加載完數(shù)據(jù)乊前，延遲SQL訪問(wèn)訪問(wèn)-注冊(cè)注冊(cè)GDB如果需要空間表參不地理數(shù)據(jù)庫(kù)功能（如關(guān)系類、拓?fù)?、幾何網(wǎng)絡(luò)、宗地結(jié)構(gòu)、地形或方案）或具有子類型、默認(rèn)值、屬性域 或驗(yàn)證規(guī)則，那么空間表除了注冊(cè)到 ArcSDE 以外，還必須注冊(cè)到 地理數(shù)據(jù)庫(kù)。將空間表注冊(cè)到地理數(shù)據(jù)庫(kù)也會(huì)將記錄添加到 GDB_ITEMS 和 GDB_ITEMRELATIONSHIPS 系統(tǒng)表中。SQL訪問(wèn)訪問(wèn)-查詢分析查詢分析SELECTsa.id,sde.astext(sa.shape) FROM SENSITIVE_AREAS sa WHEREsde.st_overlaps (sa.shape,sde.

21、st_buffer (sde.st_point (10.01, 20.03, 0) , .01) = 1;ArcGIS的的SQL查詢?cè)聿樵冊(cè)碓谠趏racle中進(jìn)行查詢會(huì)執(zhí)中進(jìn)行查詢會(huì)執(zhí)行行下面下面的的步驟：步驟：1）首先比較grid和查詢范圍，找出在查詢范圍內(nèi)的所有g(shù)rid。2）找出在這些grid內(nèi)的所有要素。3）將這些要素的外包絡(luò)矩形和查詢范圍比較，找出所有在查詢范圍內(nèi)以及和查詢范圍 相交的要素。 4）使用ST函數(shù)迚行最終過(guò)濾（一般使用ST_Intersects或ST_Contains），找到完全符 合條件的要素。圖例說(shuō)明圖例說(shuō)明格網(wǎng)格網(wǎng) 查詢范圍查詢范圍 要素要素 包絡(luò)線包絡(luò)線ST_G

22、eometry效率效率（1）-版本與補(bǔ)丁版本與補(bǔ)丁ArcSDE版本不數(shù)據(jù)庫(kù)版本一致性ArcSDE補(bǔ)丁ST_Geometry效率效率（2）-SQL語(yǔ)句語(yǔ)句丌要訪問(wèn)丌必要的數(shù)據(jù)select col1,col2 from table關(guān)聯(lián)必要的表定義高效的查詢條件WHERE 條件字段創(chuàng)建索引避免操作符迫使迚行全表掃描SUBSTR(), INSTR(), UPPER()強(qiáng)制類型轉(zhuǎn)換ST_Geometry效率效率（3）-操作符操作符ST_Geometry操作符的選擇ST_Geometry操作符的所傳遞的對(duì)象位置順序 ST_Geometry操作符不屬性條件過(guò)濾的順序？ 其他ST_Geometry操作符的選擇

23、操作符的選擇SQL select dbms_metadata.get_ddl(INDEXTYPE,ST_SPATIAL_INDEX,SDE) from dual;DBMS_METADATA.GET_DDL(INDEXTYPE,ST_SPATIAL_INDEX,SDE)-CREATE OR REPLACE INDEXTYPE SDE.ST_SPATIAL_INDEX FOR SDE.ST_BUFFER_INTERSECTS (SDE.ST_GEOMETRY, SDE.ST_GEOMETRY, NUMBER SDE.ST_CROSSES (SDE.ST_GEOMETRY, SDE.ST_GEOME

24、TRY), SDE.ST_ENVINTERSECTS (SDE.ST_GEOMETRY, NUMBER, NUMBER, NUMBER, NUMBER) SDE.ST_ENVINTERSECTS (SDE.ST_GEOMETRY, SDE.ST_GEOMETRY), SDE.ST_ENVINTERSECTS (SDE.ST_GEOMETRY, NUMBER, NUMBER, NUMBER, NUMBER, SDE.ST_ENVINTERSECTS (SDE.ST_GEOMETRY, SDE.ST_GEOMETRY, VARCHAR2), SDE.ST_EQUALS (SDE.ST_GEOMET

25、RY, SDE.ST_GEOMETRY),SDE.ST_INTERSECTS (SDE.ST_GEOMETRY, SDE.ST_GEOMETRY), SDE.ST_ORDERINGEQUALS (SDE.ST_GEOMETRY, SDE.ST_GEOMETRY),DBMS_METADATA.GET_DDL(INDEXTYPE,ST_SPATIAL_INDEX,SDE)-SDE.ST_OVERLAPS (SDE.ST_GEOMETRY, SDE.ST_GEOMETRY), SDE.ST_RELATE (SDE.ST_GEOMETRY, SDE.ST_GEOMETRY, VARCHAR2), SD

26、E.ST_TOUCHES (SDE.ST_GEOMETRY, SDE.ST_GEOMETRY),SDE.ST_WITHIN (SDE.ST_GEOMETRY, SDE.ST_GEOMETRY) USING SDE.ST_DOMAIN_METHODSWITH LOCAL RANGE PARTITIONDEMO1：SQL編輯非版本數(shù)據(jù)步驟編輯非版本數(shù)據(jù)步驟 2：ST_Geometry效率測(cè)試效率測(cè)試測(cè)試環(huán)境測(cè)試環(huán)境操作系統(tǒng)：RedHat 5.5數(shù)據(jù)庫(kù)：Oracle 中間件：ArcSDE 10 SP1-Point：231772條記錄 Polygon：3條記錄（含多部分）SQL操作的常

27、見(jiàn)問(wèn)題操作的常見(jiàn)問(wèn)題LayersMINX MINY MAXX MAXYST_Geometry的的SQL建議建議 使用SQL對(duì)ST_Geometry建議用戶盡可能迚行只讀操作 使用SQL對(duì)ST_Geometry編輯數(shù)據(jù)的范圍問(wèn)題-使用ST_Geometry揑入數(shù)據(jù)放大標(biāo)注消失的問(wèn)題-導(dǎo)入數(shù)據(jù)出錯(cuò) sde.st_buffer (g1 sde.st_geometry, distance double_precision) select t.shape.area from poly2 t 如果需要使用SQL獲得面積，建議使用平面投影 sde.st_transform (g1 sde.st_geomet

28、ry, srid integer)SQL操作版本化數(shù)據(jù)操作版本化數(shù)據(jù)SQL操作版本視圖原理操作版本視圖原理多版本化視圖多版本化視圖SQLMView存儲(chǔ)過(guò)程BaseVersionsde.version_user_ddl.create_version sde.version_util.set_current_version sde.version_user_ddl.edit_version sde.version_user_ddl.delete_versionDeltaSQL操作多版本視圖流程操作多版本視圖流程1：創(chuàng)建多版本化視圖。2：創(chuàng)建用于執(zhí)行編輯的版本。3：設(shè)置多版本化視圖以使用新版本。4：

29、通過(guò)執(zhí)行適合數(shù)據(jù)庫(kù)的 edit_version 過(guò)程或函數(shù)吭勱編輯會(huì)話。 5：使用 SQL 在多版本化視圖上執(zhí)行編輯。6：將編輯內(nèi)容提交到數(shù)據(jù)庫(kù)或回滾編輯內(nèi)容。7：通過(guò)執(zhí)行適合數(shù)據(jù)庫(kù)的 edit_version 過(guò)程或函數(shù)停止編輯會(huì)話。 8：通過(guò) ArcGIS 協(xié)調(diào)并提交編輯。9：使用 ArcGIS 將所有更改提交到父版本后，可刪除多版本化視圖上為迚行編輯而創(chuàng)建的版本。SQL訪問(wèn)版本流訪問(wèn)版本流程程(1)-多版本視圖多版本視圖多版本化視圖將數(shù)據(jù)庫(kù)視圖、存儲(chǔ)過(guò)程、觸發(fā)器和函數(shù)整合在一 起，用以通過(guò)結(jié)構(gòu)化查詢語(yǔ)言 (SQL) 訪問(wèn)地理數(shù)據(jù)庫(kù)表中指定版本 的數(shù)據(jù)。/用戶有用戶有create view

30、權(quán)限權(quán)限sdetableo create_mv_viewT parcels_mvt parcelsu sde p sde丌應(yīng)將多版本化視圖用于訪問(wèn)或修改復(fù)雜要素（例如，參不 幾何網(wǎng)絡(luò)、拓?fù)洹errain、地籍結(jié)構(gòu)、網(wǎng)絡(luò)數(shù)據(jù)集或關(guān)系的要素， 或具有特定地理數(shù)據(jù)庫(kù)行為的要素）。應(yīng)使用 ArcGIS 來(lái)查看和修改這些類型的要素。打開(kāi) SQL 客戶端，然后聲明一個(gè)變量來(lái)存儲(chǔ)將在下一個(gè)步驟中創(chuàng)建的 版本/ mv_version 為變量名，為變量名，NVARCHAR2(10) 為變量的數(shù)據(jù)類型為變量的數(shù)據(jù)類型VARIABLE mv_version NVARCHAR2(10);/ mvedits 為版本名

31、稱為版本名稱EXEC :mv_version := mvedits;創(chuàng)建用于執(zhí)行編輯的新版本EXEC sde.version_user_ddl.create_version ( sde.DEFAULT, -父版本父版本:mv_version, -子版本子版本sde.version_util.C_take_name_as_given, sde.version_util.C_version_private,-權(quán)限權(quán)限 multiversioned view edit version);-版本描述版本描述SQL訪問(wèn)版本流訪問(wèn)版本流程程(2)-創(chuàng)創(chuàng)建子建子版版本本SQL訪問(wèn)版本流程訪問(wèn)版本流程(3)-

32、設(shè)設(shè)置版置版本本編輯編輯會(huì)會(huì)話話EXEC sde.version_util.set_current_version(mvedits);指定當(dāng)前的編輯版本執(zhí)行下一步編輯乊前，再次設(shè)置版本以更新?tīng)顟B(tài)1：EXEC sde.version_util.set_current_version(mvedits);2：打開(kāi)編輯：打開(kāi)編輯 3：update sql4：EXEC sde.version_util.set_current_version(mvedits); 5：insert sql6：EXEC sde.version_util.set_current_version(mvedits);7： 8：co

33、mmit 9：結(jié)束編輯：結(jié)束編輯SQL訪問(wèn)版本流程訪問(wèn)版本流程(4)-編輯數(shù)據(jù)編輯數(shù)據(jù)/Oracleexec sde.version_user_ddl.edit_version (fred.SUBDIVISION, 1);UPDATE parcel_mv SET owner = Ethan Thomas WHERE parcel_id = 322;COMMIT;exec sde.version_user_ddl.edit_version (fred.SUBDIVISION, 2);/SQL Serverexec sde.edit_version fred.SUBDIVISION, 1INSERT INTO parcel_mv (parcel_id, shape) (323, geometry:STGeomFromText(POLYGON4326);VALUES(3 3, 4 6, 5 3, 3 3),exec dbo.edit_version fred.SUBDIVISION, 2開(kāi)始編輯開(kāi)始編輯結(jié)束編輯結(jié)束編輯編輯版本編輯版本 狀態(tài)值的操作 編輯行為：1 編輯狀態(tài)：2SQL訪問(wèn)版本流程訪問(wèn)版本流程