Oracle Spatial空間資料庫建立

1.  code：  2.  SQL> create table Dot  3.    2  (DID number(1),  4.    3  DNAME varchar2(5),  5.    4  DX number(3,1),  6.    5  DY number(3,1)  7.    6  );  8.  表已建立。  9.  SQL> insert into Dot values(1,'1',0.1,1.1);  10. 已建立 1 行。  11. SQL> insert into Dot values(2,'2',2.1,1.1);  12. 已建立 1 行。  13. SQL> insert into Dot values(3,'3',2.1,3.1);  14. 已建立 1 行。  15. SQL> insert into Dot values(4,'4',0.1,3.1);  16. 已建立 1 行。  17. SQL> insert into Dot values(5,'5',4.1,4.1);  18. 已建立 1 行。  19. SQL> insert into Dot values(6,'6',5.1,2.1);  20. 已建立 1 行。  21. SQL> insert into Dot values(7,'7',4.1,0.1);  22. 已建立 1 行。  1.  code：  2.  SQL>  create table Line  3.    2  (LID number,  4.    3  LNAME varchar2(5)  5.    4  );  6.    7.  表已建立。  8.    9.    10. SQL> insert into Line (LID,LNAME)  11.   2  select 1,'1' from dual union all  12.   3  select 2,'2' from dual union all  13.   4  select 3,'3' from dual union all  14.   5  select 4,'4' from dual union all  15.   6  select 5,'5' from dual union all  16.   7  select 6,'6' from dual union all  17.   8  select 7,'7' from dual union all  18.   9  select 8,'8' from dual;  19.   20. 已建立 8 行。  21.   22. SQL> create table Poly  23.   2  (  24.   3  PolyID number,  25.   4  PolyNAME varchar2(5)  26.   5  );  27.   28. 表已建立。  29.   30.   31.   32. SQL> insert into Poly (PolyID,PolyNAME)  33.   2  select 1,'A' from dual union all  34.   3  select 2,'B ' from dual;  35.   36. 已建立 2 行。  37.   38. SQL> create table Zd  39.   2  (  40.   3  ZdID number,  41.   4  ZdNAME varchar2(5)  42.   5  );  43.   44. 表已建立。  45.   46. SQL>  insert into Zd values(1,'一');  47.   48. 已建立 1 行。  49.   50. SQL> insert into Zd values(2,'二');  51.   52. 已建立 1 行。  53.   54. SQL> create table DotLine  55.   2   (  56.   3  LineID number,  57.   4  DotID number,  58.   5  isstart varchar2(5)  59.   6  );  60.   61. 表已建立。  62.   63.   64. SQL> insert into Dotline (LineID,DotID,isstart)  65.   2  select 1,3,'n' from dual union all  66.   3  select 1,4,'y' from dual union all  67.   4  select 2,3,'y' from dual union all  68.   5  select 2,2,'n' from dual union all  69.   6  select 3,2,'y' from dual union all  70.   7  select 3,1,'n' from dual union all  71.   8  select 4,1,'y' from dual union all  72.   9  select 4,4,'n' from dual union all  73.  10  select 5,3,'y' from dual union all  74.  11  select 5,5,'n' from dual union all  75.  12  select 6,5,'y' from dual union all  76.  13  select 6,6,'n' from dual union all  77.  14  select 7,6,'y' from dual union all  78.  15  select 7,7,'n' from dual union all  79.  16  select 8,7,'y' from dual union all  80.  17  select 8,2,'n' from dual;  81.   82. 已建立 16 行。  83.   84. SQL> create table LinePoly  85.   2  (PolyID number,  86.   3  LineID number,  87.   4  isLIGHT varchar2(5)  88.   5  );  89.   90. 表已建立。  91.   92. SQL> insert into LinePoly(PolyID,LineID,isLIGHT)  93.   2  select 1,1,'n' from dual union all  94.   3  select 1,2,'n' from dual union all  95.   4  select 1,3,'n' from dual union all  96.   5  select 1,4,'n' from dual union all  97.   6  select 2,2,'y' from dual union all  98.   7  select 2,5,'n' from dual union all  99.   8  select 2,6,'n' from dual union all  100.      9  select 2,7,'n' from dual union all  101.     10  select 2,8,'n' from dual;  102.      103.    已建立 9 行。  104.      105.    SQL>  create table PolyZd  106.      2   (ZdID number,  107.      3  PolyID number,  108.      4  isWAI varchar2(5)  109.      5  );  110.      111.    表已建立。  112.      113.    SQL>  insert into PolyZd (ZdID,PolyID,isWAI)  114.      2  select 1,1,'n' from dual union all  115.      3  select 1,2,'y' from dual union all  116.      4  select 2,1,'y' from dual union all  117.      5  select 2,2,'n' from dual;  118.      119.    已建立 4 行。  120.      121.    SQL> select DID,DX,DY  122.      2   from Dot  123.      3  where DID in  124.      4  (  125.      5   select DotID  126.      6  from DotLine  127.      7  where LineID in  128.      8  (  129.      9  select LineID  130.     10  from LinePoly  131.     11  where PolyID=2  132.     12  ))  133.     13  order by DID;  134.      135.           DID         DX         DY  136.    ---------- ---------- ----------  137.             2        2.1        1.1  138.             3        2.1        3.1  139.             5        4.1        4.1  140.             6        5.1        2.1  141.             7        4.1         .1    142.      143.    SQL> insert into user_sdo_geom_metadata  144.      2  (table_name,column_name,diminfo)  145.      3   values  146.      4  ('lhzd',  147.      5   'shape',  148.      6  sdo_dim_array  149.      7   (  150.      8   sdo_dim_element  151.      9   ('x',  152.     10  -180,180,0.001),  153.     11   sdo_dim_element  154.     12   ('y',  155.     13   -90,90,0.001  156.     14  )));  157.      158.    已建立 1 行。  159.    SQL>  CREATE TABLE TEST_ZD  160.      2  (  161.      3  ZDNO NUMBER(5),  162.      4  ZDNAME VARCHAR2(5),  163.      5  ELEMENT MDSYS.SDO_GEOMETRY  164.      6  )  165.      7  ;  166.      167.    表已建立。  168.      169.    C:\Users\Administrator>sqlldr zhangyan/123 control=c:\test\zhangyan.ctl  170.      171.    SQL*Loader: Release 11.2.0.3.0 - Production on 星期五 9月 1 16:32:56 2017  172.      173.    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.  174.      175.    達到提交點 - 邏輯記錄計數 2  176.      177.    C:\Users\Administrator>sqlplus  178.      179.    SQL*Plus: Release 11.2.0.3.0 Production on 星期五 9月 1 16:33:19 2017  180.      181.    Copyright (c) 1982, 2011, Oracle.  All rights reserved.  182.      183.    請輸入使用者名稱:  zhangyan  184.    輸入口令:  185.      186.    串連到:  187.    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  188.    With the Partitioning, OLAP, Data Mining and Real Application Testing options  189.      190.    SQL>  select * from TEST_ZD  191.      2  ;  192.      193.          ZDNO ZDNAM  194.    ---------- -----  195.    ELEMENT(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)  196.    --------------------------------------------------------------------------------  197.             1 I  198.    SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR  199.    AY(.1, 1.1, 2.1, 1.1, 2.1, 3.1, .1, 3.1))  200.      201.             2 II  202.    SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR  203.    AY(2.1, 1.1, 4.1, .1, 5.1, 2.1, 4.1, 4.1, 2.1, 3.1))  204.      205.      206.    SQL> exit  

引用塊內容