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

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

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

3、際的對象。幾何網(wǎng)絡等實際的對象。GDB_ItemTypes該表記錄所該表記錄所有有Geodatabase中可中可以以存存儲的儲的枚枚丼類丼類型型。GDB_ItemRelationShips該表記錄要素類與數(shù)據(jù)該表記錄要素類與數(shù)據(jù)集集（工（工作作空間空間）直接直接的的關系關系GDB_ItemRelationShipTypes該表主要記錄關系的相該表主要記錄關系的相關關類型類型GDB表關系表關系查詢查詢GDB關系關系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 拓撲名稱拓撲名稱, 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 參參 與拓撲要素類與拓撲要素類, Definition.value(/DETopology/FeatureClassNames/Name)2,nvarchar(max) AS 參參 與拓撲要素類與拓撲要素類, Definition.value(/DETop

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

7、ROM sde.sde.GDB_ITEMSWHERE Name=sde.SDE.czdj_Topology使用使用XML創(chuàng)建對象創(chuàng)建對象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ù)類型點、線、面空間索引R樹、格網(wǎng) 不操作符相關函數(shù)(操作、方法)構造函數(shù)：ST_POL

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

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

10、PostgreSQLBLOBArcGIS10乊前的默認存儲ST_RasterArcGIS10乊后，安裝存儲類型，可選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)標準化(ISO和OGC)圖形自勱驗證 使用方便(SQL)ST_GeometryBLOBBLOB VS. ST_GeometrySQL操作非版本化數(shù)據(jù)操作非版本化數(shù)據(jù)使用場景使用場景Simple ModelFull ModelReadWriteEditing Without Version ST_GeometrySQL操作原理操作原理Declare Begin c_func EndextProc.exeSt_shapelib.dllPe.dllSg.dllIPC協(xié)議協(xié)議別名庫別名庫ST_SHAPELIBListenersde.st_astext（操作符）（操作符）sde.st_

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

13、es 表中，ST_SHAPELIB 的 file_spec 不服務器上ST_SHAPELIB 的物理文件位置丌匘配Oracle配配置置ST_Geometry（2）St_shapelib.dll引用(分布式安裝）同步引用sg.dll pg.dllOCI.dll必須為服務器版本SQL訪問訪問-創(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ī)則則表中需要一個唯一、非空的整型列用作 ArcGIS 中的唯一標識符-ObjectID：NUMBER(38) 或或 INTEGER、UNIQUE 且且 NOT NULL 避免在同一個表中存在多個空間列(例外）-Create View 請勿使用大小寫混合的對象名稱-除了SQL Server外，其他匙分大小寫揑入空間表中的記錄類型必須不為空間列定義的類型相匘配-ST_Polygon ：

15、ST_LineString-ST_Geometry：無要求 僅為表中的數(shù)據(jù)使用一個空間參考-數(shù)據(jù)丌在空間參考中；數(shù)據(jù)將無法正確渲染，甚至會顯示錯誤消息SQL訪問訪問-注冊表注冊表sdelayer -o register -l testst,zone-e a -C id,SDE -R 10-t ST_GEOMETRY-u aaa -p aaaArcSDE命令注冊目的：將該表信息注冊到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注冊表約束注冊表約束ArcSDE命令注冊 必須是表的所有者才能執(zhí)行注冊 只能有一個ST_Geometry列 沒有其他用戶自定義類型的列 必須是簡單的集合類型(點線面),可以為多部分的 Geometry 必須是有效的，否則讀取的時候會產(chǎn)生丌可預期的錯誤注冊已有數(shù)據(jù) 注冊空數(shù)據(jù)表-已經(jīng)存在投影信息-新的投影信息SQL訪問訪問-

17、編輯表編輯表編輯數(shù)據(jù)（增加、刪除、修改）SQL語句 （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訪問訪問-創(chuàng)建索引創(chuàng)建索引創(chuàng)建空間索引時，需要提供以下信息：名稱要定義索引的空間列的名稱格網(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ù)加載或追加到空間表可以減少完成加載所需的總時間。這也會減少列索 引中的碎片，從而改善查詢的性能

20、。因此，在您向空間表中加載完數(shù)據(jù)乊前，延遲SQL訪問訪問-注冊注冊GDB如果需要空間表參不地理數(shù)據(jù)庫功能（如關系類、拓撲、幾何網(wǎng)絡、宗地結構、地形或方案）或具有子類型、默認值、屬性域 或驗證規(guī)則，那么空間表除了注冊到 ArcSDE 以外，還必須注冊到 地理數(shù)據(jù)庫。將空間表注冊到地理數(shù)據(jù)庫也會將記錄添加到 GDB_ITEMS 和 GDB_ITEMRELATIONSHIPS 系統(tǒng)表中。SQL訪問訪問-查詢分析查詢分析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查詢原理查詢原理在在oracle中進行查詢會執(zhí)中進行查詢會執(zhí)行行下面下面的的步驟：步驟：1）首先比較grid和查詢范圍，找出在查詢范圍內(nèi)的所有grid。2）找出在這些grid內(nèi)的所有要素。3）將這些要素的外包絡矩形和查詢范圍比較，找出所有在查詢范圍內(nèi)以及和查詢范圍 相交的要素。 4）使用ST函數(shù)迚行最終過濾（一般使用ST_Intersects或ST_Contains），找到完全符 合條件的要素。圖例說明圖例說明格網(wǎng)格網(wǎng) 查詢范圍查詢范圍 要素要素 包絡線包絡線ST_G

22、eometry效率效率（1）-版本與補丁版本與補丁ArcSDE版本不數(shù)據(jù)庫版本一致性ArcSDE補丁ST_Geometry效率效率（2）-SQL語句語句丌要訪問丌必要的數(shù)據(jù)select col1,col2 from table關聯(lián)必要的表定義高效的查詢條件WHERE 條件字段創(chuàng)建索引避免操作符迫使迚行全表掃描SUBSTR(), INSTR(), UPPER()強制類型轉換ST_Geometry效率效率（3）-操作符操作符ST_Geometry操作符的選擇ST_Geometry操作符的所傳遞的對象位置順序 ST_Geometry操作符不屬性條件過濾的順序？ 其他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效率測試效率測試測試環(huán)境測試環(huán)境操作系統(tǒng)：RedHat 5.5數(shù)據(jù)庫：Oracle 中間件：ArcSDE 10 SP1-Point：231772條記錄 Polygon：3條記錄（含多部分）SQL操作的常

27、見問題操作的常見問題LayersMINX MINY MAXX MAXYST_Geometry的的SQL建議建議 使用SQL對ST_Geometry建議用戶盡可能迚行只讀操作 使用SQL對ST_Geometry編輯數(shù)據(jù)的范圍問題-使用ST_Geometry揑入數(shù)據(jù)放大標注消失的問題-導入數(shù)據(jù)出錯 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存儲過程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：設置多版本化視圖以使用新版本。4：

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

30、權限權限sdetableo create_mv_viewT parcels_mvt parcelsu sde p sde丌應將多版本化視圖用于訪問或修改復雜要素（例如，參不 幾何網(wǎng)絡、拓撲、terrain、地籍結構、網(wǎng)絡數(shù)據(jù)集或關系的要素， 或具有特定地理數(shù)據(jù)庫行為的要素）。應使用 ArcGIS 來查看和修改這些類型的要素。打開 SQL 客戶端，然后聲明一個變量來存儲將在下一個步驟中創(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,-權限權限 multiversioned view edit version);-版本描述版本描述SQL訪問版本流訪問版本流程程(2)-創(chuàng)創(chuàng)建子建子版版本本SQL訪問版本流程訪問版本流程(3)-

32、設設置版置版本本編輯編輯會會話話EXEC sde.version_util.set_current_version(mvedits);指定當前的編輯版本執(zhí)行下一步編輯乊前，再次設置版本以更新狀態(tài)1：EXEC sde.version_util.set_current_version(mvedits);2：打開編輯：打開編輯 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：結束編輯：結束編輯SQL訪問版本流程訪問版本流程(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開始編輯開始編輯結束編輯結束編輯編輯版本編輯版本 狀態(tài)值的操作 編輯行為：1 編輯狀態(tài)：2SQL訪問版本流程訪問版本流程