Oracle Spatial空间几何对象的处理

博客首页 » Oracle Spatial空间几何对象的处理

发布于 29 Aug 2013 08:39
标签 blog lbs oracle spatial
Oracle Spatial 空间几何对象的处理需要一下这些步骤:
生成表空间
生成带有空间几何对象的表
插入空间几何对象(声明对象,指定维度数,图形类型,对象位置,图形描述,顶点集合)
对空间对象进行查询

下面是测试的log:

lbstest(SYS)> create tablespace tbs_lbs datafile '/u02/oradata/lbstest/tbs_lbs01.dbf' size 100m autoextend on next 100m maxsize unlimitedTablespace created.
 
Elapsed: 00:00:00.44
lbstest(SYS)> create user lbs_usr identified by lbs_usr default tablespace tbs_lbs;
 
User created.
 
Elapsed: 00:00:00.02
lbstest(SYS)> grant connect, resource, select any table, select any dictionary, select_catalog_role to lbs_usr
 
Grant succeeded.
 
Elapsed: 00:00:00.02
lbstest(SYS)> conn lbs_usr/lbs_usr
 
Elapsed: 00:00:00.02
lbstest(LBS_USR)> CREATE TABLE customers (
  2    customer_id NUMBER,
  3    last_name VARCHAR2(30),
  4    first_name VARCHAR2(30),
  5    street_address VARCHAR2(40),
  6    city VARCHAR2(30),
  7    state_province_code VARCHAR2(2),
  8    postal_code VARCHAR2(9),
  9    cust_geo_location SDO_GEOMETRY);
 
Table created.
 
Elapsed: 00:00:00.06
lbstest(LBS_USR)> CREATE TABLE stores (
  2    store_id NUMBER,
  3    description VARCHAR2(100),
  4    street_address VARCHAR2(40),
  5    city VARCHAR2(30),
  6    state_province_code VARCHAR2(2),
  7    postal_code VARCHAR2(9),
  8    store_geo_location SDO_GEOMETRY);
 
Table created.
 
Elapsed: 00:00:00.03
lbstest(LBS_USR)> INSERT INTO customers VALUES
  2    (1001,'Nichols', 'Alexandra',
  3    '17 Maple Drive', 'Nashua', 'NH','03062',
  4     SDO_GEOMETRY(2001, 8307,
  5       SDO_POINT_TYPE (-71.48923,42.72347,NULL), NULL, NULL));
 
1 row created.
 
Elapsed: 00:00:00.01
lbstest(LBS_USR)> INSERT INTO customers VALUES
  2    (1002,'Harris', 'Melvin',
  3    '5543 Harrison Blvd', 'Reston', 'VA', '20190',
  4    SDO_GEOMETRY(2001, 8307,
  5      SDO_POINT_TYPE(-70.120133,44.795766,NULL), NULL, NULL));
 
1 row created.
 
Elapsed: 00:00:00.01
lbstest(LBS_USR)> INSERT INTO customers VALUES
  2    (1003,'Chang', 'Marian',
  3    '294 Main St', 'Concord', 'MA','01742',
  4     SDO_GEOMETRY(2001, 8307,
  5       SDO_POINT_TYPE (-71.351,42.4598,NULL), NULL, NULL));
 
1 row created.
 
Elapsed: 00:00:00.01
lbstest(LBS_USR)>
lbstest(LBS_USR)> INSERT INTO customers VALUES
  2    (1004,'Williams', 'Thomas',
  3    '84 Hayward Rd', 'Acton', 'MA','01720',
  4     SDO_GEOMETRY(2001, 8307,
  5       SDO_POINT_TYPE (-71.4559,42.4748,NULL), NULL, NULL));
 
1 row created.
 
Elapsed: 00:00:00.00
lbstest(LBS_USR)>
lbstest(LBS_USR)> INSERT INTO customers VALUES
  2    (1005,'Rodriguez', 'Carla',
  3    '9876 Pine Lane', 'Sudbury', 'MA','01776',
  4     SDO_GEOMETRY(2001, 8307,
  5       SDO_POINT_TYPE (-71.4242,42.3826,NULL), NULL, NULL));
 
1 row created.
 
Elapsed: 00:00:00.00
lbstest(LBS_USR)>
lbstest(LBS_USR)> INSERT INTO customers VALUES
  2    (1006,'Adnani', 'Ramesh',
  3    '1357 Appletree Ct', 'Falls Church', 'VA','22042 ',
  4     SDO_GEOMETRY(2001, 8307,
  5       SDO_POINT_TYPE (-77.1745,38.88505,NULL),NULL,NULL));
 
1 row created.
 
Elapsed: 00:00:00.00
lbstest(LBS_USR)> INSERT INTO stores VALUES
  2    (101,'Nashua megastore',
  3    '123 Commercial Way', 'Nashua', 'NH','03062',
  4     SDO_GEOMETRY(2001, 8307,
  5       SDO_POINT_TYPE (-71.49074,42.7229,NULL),NULL,NULL));
 
1 row created.
 
Elapsed: 00:00:00.01
lbstest(LBS_USR)>
lbstest(LBS_USR)> INSERT INTO stores VALUES
  2    (102,'Reston store',
  3    '99 Main Blvd', 'Reston', 'VA','22070',
  4     SDO_GEOMETRY(2001, 8307,
  5       SDO_POINT_TYPE (-77.34511,38.9521,NULL),NULL,NULL));
 
1 row created.
 
Elapsed: 00:00:00.01
lbstest(LBS_USR)> INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
  2     VALUES ('CUSTOMERS', 'CUST_GEO_LOCATION',
  3     SDO_DIM_ARRAY
  4       (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5),
  5       SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)),
  6     8307);
 
1 row created.
 
Elapsed: 00:00:00.02
lbstest(LBS_USR)> INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
  2     VALUES ('STORES', 'STORE_GEO_LOCATION',
  3     SDO_DIM_ARRAY
  4       (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5),
  5       SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)),
  6     8307);
 
1 row created.
 
Elapsed: 00:00:00.00
lbstest(LBS_USR)> CREATE INDEX customers_sidx ON customers(cust_geo_location)
  2    INDEXTYPE IS mdLBS_USR.spatial_index;
 
Index created.
 
Elapsed: 00:00:00.71
lbstest(LBS_USR)> CREATE INDEX stores_sidx ON stores(store_geo_location)
  2    INDEXTYPE IS mdLBS_USR.spatial_index;
 
Index created.
 
Elapsed: 00:00:00.24
lbstest(LBS_USR)>
lbstest(LBS_USR)> SELECT /*+ordered*/
  2     c.customer_id,
  3     c.first_name,
  4     c.last_name
  5  FROM stores s,
  6     customers c
  7  WHERE s.store_id = 101
  8  AND sdo_nn (c.cust_geo_location, s.store_geo_location, 'sdo_num_res=3')
  9     = 'TRUE';
 
CUSTOMER_ID FIRST_NAME                     LAST_NAME
----------- ------------------------------ ------------------------------
       1001 Alexandra                      Nichols
       1003 Marian                         Chang
       1004 Thomas                         Williams
 
Elapsed: 00:00:00.16

Reference:
http://docs.oracle.com/cd/B25329_01/doc/appdev.102/b28004/xe_locator.htm

这个文章里有一个通过sdo_geometry构造带坐标系的多边形查询的例子。
http://www.linuxidc.com/Linux/2012-11/74215.htm

select *
  from TBSVRC_RESPUBLISHITEMS t
 where SDO_ANYINTERACT(F_SPATIALEXTENT,sdo_geometry('POLYGON((80.83422302246095
                                                          20.518481140136714,
                                                          120.4135076904297
                                                          20.518481140136714,
                                                          120.4135076904297
                                                          50.314989929199214,
                                                          80.83422302246095
                                                          50.314989929199214,
                                                          80.83422302246095
                                                          20.518481140136714))',8307))='TRUE'
 
//相当于
select *
  from TBSVRC_RESPUBLISHITEMS t
 where SDO_ANYINTERACT(F_SPATIALEXTENT,SDO_UTIL.from_wktgeometry('POLYGON((80.83422302246095
                                                          20.518481140136714,
                                                          120.4135076904297
                                                          20.518481140136714,
                                                          120.4135076904297
                                                          50.314989929199214,
                                                          80.83422302246095
                                                          50.314989929199214,
                                                          80.83422302246095
                                                          20.518481140136714))') )='TRUE'

本页面的文字允许在知识共享 署名-相同方式共享 3.0协议和GNU自由文档许可证下修改和再使用,仅有一个特殊要求,请用链接方式注明文章引用出处及作者。请协助维护作者合法权益。


系列文章

文章列表

  • Oracle Spatial空间几何对象的处理

这篇文章对你有帮助吗,投个票吧?

rating: 0+x

留下你的评论

Add a New Comment