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  

引用块内容