OraFAQ Forum: Spatial » create SDO_ORDINATE

Home » Server Options » Spatial » create SDO_ORDINATE_ARRAY from number array (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

create SDO_ORDINATE_ARRAY from number array [message #564464]

Fri, 24 August 2012 02:30

gis-man
Messages: 4
Registered: August 2012
Location: Wales

Junior Member

Hi

I'm an oracle novice and I have a number array in a procedure and I want to convert it to MDSYS.SDO_ORDINATE_ARRAY to a build a polygon.
Is there a quick way I can caste one to the other ?
If not could you show me how to modify the procedure to declare a new MDSYS.SDO_ORDINATE_ARRAY and populate it from the number array c1.
My attempt bellow returns "expression is of wrong type"

BTW : I doing this to get round the limit of 999 elements when using SDO_ORDINATE_ARRAY in a simple sql insert.

Thanks, as ever, in advance.

CREATE OR REPLACE PACKAGE ARRAYBINDPKG1 AS
TYPE ARRTYPE IS TABLE OF NUMBER(20) INDEX BY BINARY_INTEGER;
PROCEDURE iobind(c1 IN ARRTYPE);
END ARRAYBINDPKG1;
CREATE OR REPLACE PACKAGE BODY ARRAYBINDPKG1 AS";
PROCEDURE iobind(c1 IN ARRTYPE) IS";
BEGIN";
INSERT INTO bind_example (geo) VALUES (MDSYS.SDO_GEOMETRY(2003,81989,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(c1)));";
END iobind;";
END ARRAYBINDPKG1;

Steve Smith
Aberystwyth, Wales

Report message to a moderator

Re: create SDO_ORDINATE_ARRAY from number array [message #564477 is a reply to message #564464]

Fri, 24 August 2012 04:04

_jum
Messages: 577
Registered: February 2008

Senior Member

Here as a first clue a little example that converts a SDO_GEOMETRY into a NUMBER ARRAY (predefined SYS.odcinumberlist):

SET SERVEROUTPUT ON SIZE 900000;

DECLARE
  geom       MDSYS.sdo_geometry;
  vordarr    MDSYS.sdo_ordinate_array;
  NumList    SYS.odcinumberlist;
  
BEGIN
  
  geom     := SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,3,1),SDO_ORDINATE_ARRAY(0,0, 1,0, 2,1, 2,2, 0,2, 0,0));
  vordarr  := geom.sdo_ordinates;

  SELECT CAST (geom.sdo_ordinates AS SYS.odcinumberlist) INTO NumList FROM dual; 
  
  --test the result
  FOR i IN NumList.FIRST .. NumList.LAST
  LOOP
    dbms_output.put_line(NumList(i));
  END LOOP;

END;

0
0
1
0
2
1
2
2
0
2
0
0

Alternatively you could use a PIPELINED FUNCTION.
But I don't understand your objection of 999 elements when using SDO_ORDINATE_ARRAY, IMO there is no such a limit.

[Updated on: Fri, 24 August 2012 04:06]

Report message to a moderator

Re: create SDO_ORDINATE_ARRAY from number array [message #564485 is a reply to message #564477]

Fri, 24 August 2012 05:07

gis-man
Messages: 4
Registered: August 2012
Location: Wales

Junior Member

Hi Jum,

Thanks for your speedy response,

I did try using SELECT CAST (c1 AS MDSYS.SDO_ORDINATE_ARRAY) INTO ords FROM dual;
but got the message "expression of wrong type"

I tried modifying my script to do it with a for loop.
But I get an error on the line : ords(i) := c1(i)
I just need a statement which will append a value to a SDO_ORDINATE_ARRAY.

CREATE OR REPLACE PACKAGE ARRAYBINDPKG1 AS
TYPE ARRTYPE IS TABLE OF NUMBER(20) INDEX BY BINARY_INTEGER;
PROCEDURE iobind(c1 IN ARRTYPE);
END ARRAYBINDPKG1;
CREATE OR REPLACE PACKAGE BODY ARRAYBINDPKG1 AS
PROCEDURE iobind(c1 IN ARRTYPE) IS
ords MDSYS.SDO_ORDINATE_ARRAY;
BEGIN
ords := MDSYS.SDO_ORDINATE_ARRAY();
FOR i IN c1.FIRST .. c1.LAST LOOP
ords(i) := c1(i);
END LOOP;
INSERT INTO bind_example (geo) VALUES (MDSYS.SDO_GEOMETRY(2003,81989,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),ords));
END iobind;";
END ARRAYBINDPKG1;

Steve Smith

Report message to a moderator

Re: create SDO_ORDINATE_ARRAY from number array [message #564489 is a reply to message #564485]

Fri, 24 August 2012 05:31

gis-man
Messages: 4
Registered: August 2012
Location: Wales

Junior Member

I worked it out now thanks.

I needed to add ords.EXTEND(1) to extend the array

Report message to a moderator

Re: create SDO_ORDINATE_ARRAY from number array [message #564498 is a reply to message #564489]

Fri, 24 August 2012 06:40

_jum
Messages: 577
Registered: February 2008

Senior Member

You fill the array row by row (= slow by slow). This is not necessary, the cast in the other direction works fine with me:

SET SERVEROUTPUT ON SIZE 900000;

DECLARE

  geom       MDSYS.sdo_geometry;
  vordarr    MDSYS.sdo_ordinate_array;
  NumList    SYS.odcinumberlist := SYS.odcinumberlist(0,0, 1,0, 2,1, 2,2, 0,2, 0,0);
  
BEGIN
  
  SELECT CAST (NumList AS  MDSYS.sdo_ordinate_array) INTO vordarr FROM dual; 
  
  geom := SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,3,1), vordarr);
  
  dbms_output.put_line(sdo_geom.validate_geometry(geom,0.1));

END;


------------------------
TRUE
PL/SQL procedure successfully completed.

Report message to a moderator

Re: create SDO_ORDINATE_ARRAY from number array [message #564502 is a reply to message #564498]

Fri, 24 August 2012 07:44

gis-man
Messages: 4
Registered: August 2012
Location: Wales

Junior Member

Hi

I'm still getting "expression of wrong type" when I use it as follows.
The C1 array is a bound array created in PHP.
Can you tell me what "dual" refers too ?

Thanks again

Steve Smith

CREATE OR REPLACE PACKAGE ARRAYBINDPKG1 AS
TYPE ARRTYPE IS TABLE OF NUMBER(20) INDEX BY BINARY_INTEGER;
PROCEDURE iobind(c1 IN ARRTYPE);
END ARRAYBINDPKG1;
CREATE OR REPLACE PACKAGE BODY ARRAYBINDPKG1 AS
PROCEDURE iobind(c1 IN ARRTYPE) IS
ords MDSYS.SDO_ORDINATE_ARRAY;
BEGIN
SELECT CAST (c1 AS MDSYS.sdo_ordinate_array) INTO ords FROM dual;
INSERT INTO bind_example (name,age,geo) VALUES ('jane',c1(3),MDSYS.SDO_GEOMETRY(2003,81989,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),ords));
END iobind;
END ARRAYBINDPKG1;

Report message to a moderator

Re: create SDO_ORDINATE_ARRAY from number array [message #564505 is a reply to message #564502]

Fri, 24 August 2012 08:06

_jum
Messages: 577
Registered: February 2008

Senior Member

You should use code tags to make Your code easier to read. Please refer to How to use [code] tags and make your code easier to read?.
You can find some informations about the table DUAL in the link.
It is IMHO not possible to access the CAST operator in PL/SQL, so you have to use SQL with SELECT ... INTO ... FROM dual as workaround to cast one collection into another.
If there is no problem with performance, you can of course use the FOR LOOP.

Report message to a moderator

Re: create SDO_ORDINATE_ARRAY from number array [message #566353 is a reply to message #564502]

Thu, 13 September 2012 05:06

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

Steve,

In your latest posted code, the problem is that, in order to use "select cast(c1 ..." c1 must be a SQL type, not a PL/SQ type. You can either create your arrtype in SQL or use the sys.odcinuumberlist. I have demonstrated both below.

SCOTT@orcl_11gR2> CREATE TABLE bind_example
  2    (name  VARCHAR2(15),
  3  	age   NUMBER,
  4  	geo   MDSYS.SDO_GEOMETRY)
  5  /

Table created.

SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE arrtype AS VARRAY(32767) OF NUMBER;
  2  /

Type created.

SCOTT@orcl_11gR2> CREATE OR REPLACE PACKAGE arraybindpkg1
  2  AS
  3    PROCEDURE iobind
  4  	 (c1 IN arrtype);
  5  END arraybindpkg1;
  6  /

Package created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE PACKAGE BODY arraybindpkg1
  2  AS
  3    PROCEDURE iobind
  4  	 (c1 IN arrtype)
  5    IS
  6  	 ords MDSYS.SDO_ORDINATE_ARRAY;
  7    BEGIN
  8  	 SELECT CAST (c1 AS MDSYS.SDO_ORDINATE_ARRAY) INTO ords FROM DUAL;
  9  	 INSERT INTO bind_example (name, age, geo)
 10  	 VALUES
 11  	   ('jane', c1(3),
 12  	    MDSYS.SDO_GEOMETRY
 13  	      (2003, 81989, NULL,
 14  	       MDSYS.SDO_ELEM_INFO_ARRAY (1, 1003, 1),
 15  	       ords));
 16    END iobind;
 17  END arraybindpkg1;
 18  /

Package body created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> BEGIN
  2    arraybindpkg1.iobind (arrtype (0,0, 1,0, 2,1, 2,2, 0,2, 0,0));
  3  END;
  4  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> SELECT * FROM bind_example
  2  /

NAME                   AGE
--------------- ----------
GEO(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
jane                     1
SDO_GEOMETRY(2003, 81989, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(0, 0, 1, 0, 2, 1, 2, 2, 0, 2, 0, 0))


1 row selected.

SCOTT@orcl_11gR2> CREATE TABLE bind_example
  2    (name  VARCHAR2(15),
  3  	age   NUMBER,
  4  	geo   MDSYS.SDO_GEOMETRY)
  5  /

Table created.

SCOTT@orcl_11gR2> CREATE OR REPLACE PACKAGE arraybindpkg1
  2  AS
  3    PROCEDURE iobind
  4  	 (c1 IN SYS.ODCINUMBERLIST);
  5  END arraybindpkg1;
  6  /

Package created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE PACKAGE BODY arraybindpkg1
  2  AS
  3    PROCEDURE iobind
  4  	 (c1 IN SYS.ODCINUMBERLIST)
  5    IS
  6  	 ords MDSYS.SDO_ORDINATE_ARRAY;
  7    BEGIN
  8  	 SELECT CAST (c1 AS MDSYS.SDO_ORDINATE_ARRAY) INTO ords FROM DUAL;
  9  	 INSERT INTO bind_example (name, age, geo)
 10  	 VALUES
 11  	   ('jane', c1(3),
 12  	    MDSYS.SDO_GEOMETRY
 13  	      (2003, 81989, NULL,
 14  	       MDSYS.SDO_ELEM_INFO_ARRAY (1, 1003, 1),
 15  	       ords));
 16    END iobind;
 17  END arraybindpkg1;
 18  /

Package body created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> BEGIN
  2    arraybindpkg1.iobind (SYS.ODCINUMBERLIST (0,0, 1,0, 2,1, 2,2, 0,2, 0,0));
  3  END;
  4  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> SELECT * FROM bind_example
  2  /

NAME                   AGE
--------------- ----------
GEO(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
jane                     1
SDO_GEOMETRY(2003, 81989, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(0, 0, 1, 0, 2, 1, 2, 2, 0, 2, 0, 0))


1 row selected.

Report message to a moderator

Previous Topic:	Oracle Spatial Query peformance
Next Topic:	sdo_contains help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 01:45:26 CST 2025