CODEWITHSUNDEEP
oracle-databasecollections
fallingdog
fallingdog

Reputation: 192

select x,y from SDO_ORDINATES collection for simple point sdo geometry

I want to select the x, y values from a sdo geometry table. The gtype is 2001, so they are points. The sdo geometry column name is geometry.

I have tried:

select geometry.sdo_point.x, geometry.sdo_point.y from el_pole_test

This returns null values.

I have also tried by index:

select p.geometry.SDO_ORDINATES[0] as point_x,  p.geometry.SDO_ORDINATES[1] as point_y from el_pole_test

This errors out.

If I look inside of a collection for a given point I see an array like this:

1661473.42619016
2630277.19731551
1
0

The first two values in the array are the x and y. I am not sure what role the 1 and 0 play.

Table create:

create table EL_POLE_TEST
(
  entity_id VARCHAR2(39) not null,
  geometry  MDSYS.SDO_GEOMETRY
)

Sample rows:

INSERT INTO el_pole_test 
VALUES      ('00001D212', 
             mdsys.Sdo_geometry(2001, NULL, NULL, 
             mdsys.Sdo_elem_info_array(1, 1, 1, 3, 1, 0), 
mdsys.Sdo_ordinate_array(1661473.42619016, 2630277.19731551, 1, 0))); 

INSERT INTO el_pole_test 
VALUES      ('00000D212', 
             mdsys.Sdo_geometry(2001, NULL, NULL, 
             mdsys.Sdo_elem_info_array(1, 1, 1, 3, 1, 0), 
mdsys.Sdo_ordinate_array(1667630.11795338, 2640351.11795338, 1, 0)));

Upvotes: 1

Views: 1904

Answers (1)

Jon Heller
Jon Heller

Reputation: 36817

You need a surprisingly large number of SQL tricks to get the X and Y coordinates out of that table: unnest the collection elements by cross joining the TABLE operator (but you must use a table alias - this is the only time when an alias is required), use an analytic function to order the elements for each ENTITY_ID, and then use MAX/CASE to aggregate the results and get one X and one Y per row:

select
    entity_id,
    max(case when rownumber = 1 then column_value else null end) x,
    max(case when rownumber = 2 then column_value else null end) y
from
(
    select
        entity_id,
        column_value,
        row_number() over (partition by entity_id order by rownum) rownumber
    from el_pole_test e
    cross join table(e.geometry.sdo_ordinates)
)
group by entity_id;


ENTITY_ID  X                 Y               
---------  ----------------  ----------------
00000D212  1667630.11795338  2640351.11795338
00001D212  1661473.42619016  2630277.19731551

Upvotes: 2

Related Questions