Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Modify SDO_ELEM_INFO_ARRAY of existing geometry

User_1871Jun 8 2022 — edited Jun 11 2022

I have an existing SDO_GEOMETRY:

SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(10,10,  100,100))
--                                                          🡅

I want to modify the SDO_ELEM_INFO_ARRAY like this:
Before: (1, 1003, 1)
After: (1, 1003, 3)
How can I modify that SDO_ELEM_INFO_ARRAY value using SQL?
-----------------------------------------------------------------------
Background:
In a different post, I asked a side question about the following:
Bug: If I create a polygon/rectangle from two WKT vertices, the SDO_ELEM_INFO_ARRAY is incorrect.

https://community.oracle.com/tech/apps-infra/discussion/comment/16838025/#Comment_16838025
sdo_geometry('polygon ((10 10, 100 100))')
produces this geometry:
MDSYS.SDO_GEOMETRY(2003, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1), MDSYS.SDO_ORDINATE_ARRAY(10, 10, 100, 100))
As such, I'm hoping to work around that issue by replacing the 1 with a 3 in the SDO_ELEM_INFO_ARRAY.
-----------------------------------------------------------------------
Related:
Replace value in SDO_ELEM_INFO_ARRAY varray

This post has been answered by _jum on Jun 8 2022

Jump to Answer

_jum

Answer

Alias are important:

SELECT SDO_GEOMETRY(sq.shape.sdo_gtype, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), sq.shape.sdo_ordinates) rect 
 FROM
 (SELECT SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(10, 10, 100, 100)) shape 
   FROM dual) sq;

RECT
-------------
MDSYS.SDO_GEOMETRY(2003, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 3), MDSYS.SDO_ORDINATE_ARRAY(10, 10, 100, 100))

Marked as Answer by User_1871 · Jun 16 2022

1 person found this helpful

User_1871

_jum Thanks.
Side note:
I learned recently that an alternative to the "use an alias to access the object attribute" trick is to wrap the column in brackets. I didn't know that was possible until now.

with cte as
(select sdo_geometry(2003, null, null, sdo_elem_info_array(1, 1003, 1), sdo_ordinate_array(10, 10, 100, 100)) shape 
from dual)

select 
  sdo_geometry((shape).sdo_gtype, null, null, sdo_elem_info_array(1,1003,3), (shape).sdo_ordinates) as rect 
from  --          🡅 wrapped in brackets                                        🡅 wrapped in brackets
  cte

1 person found this helpful

_jum

Hi @User_1871,
this is a nice and useful trick, especially in connection to a WITH-clause.
Thanks for sharing!

1 person found this helpful

1 - 3

Added on Jun 8 2022

#database-key-features, #spatial

3 comments

140 views

1 mention

Database Software

Modify SDO_ELEM_INFO_ARRAY of existing geometry

Comments

Post Details