Home » Server Options » Spatial » create SDO_ORDINATE_ARRAY from number array (10g)
create SDO_ORDINATE_ARRAY from number array [message #564464] Fri, 24 August 2012 02:30 Go to next message
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
Re: create SDO_ORDINATE_ARRAY from number array [message #564477 is a reply to message #564464] Fri, 24 August 2012 04:04 Go to previous messageGo to next message
_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 Go to previous messageGo to next message
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
Re: create SDO_ORDINATE_ARRAY from number array [message #564489 is a reply to message #564485] Fri, 24 August 2012 05:31 Go to previous messageGo to next message
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
Re: create SDO_ORDINATE_ARRAY from number array [message #564498 is a reply to message #564489] Fri, 24 August 2012 06:40 Go to previous messageGo to next message
_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.
Re: create SDO_ORDINATE_ARRAY from number array [message #564502 is a reply to message #564498] Fri, 24 August 2012 07:44 Go to previous messageGo to next message
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;

Re: create SDO_ORDINATE_ARRAY from number array [message #564505 is a reply to message #564502] Fri, 24 August 2012 08:06 Go to previous messageGo to next message
_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.
Re: create SDO_ORDINATE_ARRAY from number array [message #566353 is a reply to message #564502] Thu, 13 September 2012 05:06 Go to previous message
Barbara Boehmer
Messages: 9077
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.
Previous Topic: Oracle Spatial Query peformance
Next Topic: sdo_contains help
Goto Forum:
  


Current Time: Thu Mar 28 09:53:43 CDT 2024