Home » Server Options » Spatial » how to create spatial index
| how to create spatial index [message #511377] |
Sun, 12 June 2011 08:40  |
 |
assa9009
Messages: 7
Registered: June 2011
Location: australia
|
Junior Member |
|
|
Hi guys
i have problem in creation R-tree in oracle 11g release 2
I'm not experience in that ,
basically i started in longin by SYSTEM and password
then
CREATE TABLE cola_markets (
mkt_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape SDO_GEOMETRY);
then insert some points
INSERT INTO cola_markets VALUES(
90,
'point_only',
SDO_GEOMETRY(
2001,
NULL,
NULL,
NULL));
INSERT INTO cola_markets VALUES(
90,
'point_only',
SDO_GEOMETRY(
2001,
NULL,
SDO_POINT_TYPE(13, 14, NULL),
NULL,
NULL));
then create the index
CREATE INDEX cola_spatial_idx
ON cola_markets(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
error
but not workingggggg , please can anyone give me an easy example and illustrate for me
step by step please
, something wrong with the database ? what should i do ?
|
|
|
|
|
|
|
|
| Re: how to create spatial index [message #511412 is a reply to message #511377] |
Sun, 12 June 2011 14:27  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_11gR2> CREATE TABLE cola_markets
2 (mkt_id NUMBER PRIMARY KEY,
3 name VARCHAR2 (32),
4 shape SDO_GEOMETRY)
5 /
Table created.
SCOTT@orcl_11gR2> INSERT INTO cola_markets VALUES
2 (90,
3 'point_only',
4 SDO_GEOMETRY
5 (2001,
6 NULL,
7 SDO_POINT_TYPE (13, 14, NULL),
8 NULL,
9 NULL))
10 /
1 row created.
SCOTT@orcl_11gR2> INSERT INTO user_sdo_geom_metadata VALUES
2 ('cola_markets',
3 'shape',
4 SDO_DIM_ARRAY
5 (SDO_DIM_ELEMENT ('X', 0, 20, 0.5),
6 SDO_DIM_ELEMENT ('Y', 0, 20, 0.5)),
7 NULL)
8 /
1 row created.
SCOTT@orcl_11gR2> CREATE INDEX cola_spatial_idx
2 ON cola_markets (shape)
3 INDEXTYPE IS MDSYS.SPATIAL_INDEX
4 /
Index created.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
|
|
| Re: how to create spatial index [message #511502 is a reply to message #511478] |
Mon, 13 June 2011 08:41 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You should not be using any user such as sys or system or mdsys that is part of Oracle. You should be creating your own user, such as the user test in the example below. Notice that you have to insert into user_sdo_geom_metadata in order to be able to create the index. You did not do that in what you posted and maybe you didn't notice that I did in the example that I posted. If that does not work for you, then you need to post a copy and paste of a run from SQL*Plus with the results, just as I have done below.
SCOTT@orcl_11gR2> CREATE USER test IDENTIFIED BY test
2 /
User created.
SCOTT@orcl_11gR2> GRANT CONNECT, RESOURCE TO test
2 /
Grant succeeded.
SCOTT@orcl_11gR2> CONNECT test/test
Connected.
TEST@orcl_11gR2> CREATE TABLE cola_markets
2 (mkt_id NUMBER PRIMARY KEY,
3 name VARCHAR2 (32),
4 shape SDO_GEOMETRY)
5 /
Table created.
TEST@orcl_11gR2> INSERT INTO cola_markets VALUES
2 (90,
3 'point_only',
4 SDO_GEOMETRY
5 (2001,
6 NULL,
7 SDO_POINT_TYPE (13, 14, NULL),
8 NULL,
9 NULL))
10 /
1 row created.
TEST@orcl_11gR2> INSERT INTO user_sdo_geom_metadata VALUES
2 ('cola_markets',
3 'shape',
4 SDO_DIM_ARRAY
5 (SDO_DIM_ELEMENT ('X', 0, 20, 0.5),
6 SDO_DIM_ELEMENT ('Y', 0, 20, 0.5)),
7 NULL)
8 /
1 row created.
TEST@orcl_11gR2> CREATE INDEX cola_spatial_idx
2 ON cola_markets (shape)
3 INDEXTYPE IS MDSYS.SPATIAL_INDEX
4 /
Index created.
TEST@orcl_11gR2>
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Dec 22 02:32:06 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
