Home » Server Options » Spatial » how to create spatial index
how to create spatial index [message #511377] Sun, 12 June 2011 08:40 Go to next message
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 #511378 is a reply to message #511377] Sun, 12 June 2011 09:07 Go to previous messageGo to next message
BlackSwan
Messages: 22500
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: how to create spatial index [message #511388 is a reply to message #511377] Sun, 12 June 2011 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 58573
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
basically i started in longin by SYSTEM and password


Basically this is your first error.

Regards
Michel
Re: how to create spatial index [message #511412 is a reply to message #511377] Sun, 12 June 2011 14:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7936
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 #511475 is a reply to message #511412] Mon, 13 June 2011 04:31 Go to previous messageGo to next message
assa9009
Messages: 7
Registered: June 2011
Location: australia
Junior Member


OK , so which user i have to use ?

why it worked whit Barbara Boehmer ?


Re: how to create spatial index [message #511478 is a reply to message #511475] Mon, 13 June 2011 05:02 Go to previous messageGo to next message
assa9009
Messages: 7
Registered: June 2011
Location: australia
Junior Member
also i used MDSYS user , but same problem

any suggestions please ?
Re: how to create spatial index [message #511502 is a reply to message #511478] Mon, 13 June 2011 08:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7936
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>

Re: how to create spatial index [message #511589 is a reply to message #511502] Tue, 14 June 2011 03:26 Go to previous message
assa9009
Messages: 7
Registered: June 2011
Location: australia
Junior Member
thanks Barbara Boehmer

you are right , i did not notice that , i must insert into user_sdo_geom_metadata

now it worked thanks
Previous Topic: oracle spatial coordinates to relational (2 threads merged by bb)
Next Topic: shifting of coordiantes by adding offset value
Goto Forum:
  


Current Time: Fri Jul 25 15:54:47 CDT 2014

Total time taken to generate the page: 0.24722 seconds