ORACLE SPATIAL空间数据库建立

来源：互联网发布：fc2手机视频新域名编辑：程序博客网时间：2024/05/29 04:06
空间图
这里写图片描述
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
引用块内容
阅读全文
0 0