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 Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/43c94be2df4b13d248ddcc4fa4641868?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
_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 #564498 is a reply to message #564489] |
Fri, 24 August 2012 06:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
_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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/43c94be2df4b13d248ddcc4fa4641868?s=64&d=mm&r=g) |
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 #566353 is a reply to message #564502] |
Thu, 13 September 2012 05:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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.
|
|
|
Goto Forum:
Current Time: Sat Sep 07 18:32:06 CDT 2024
|