从SDO_ORDINATES集合中为简单的点sdo几何选择x，y

Question

我想从sdo几何表中选择x，y值。gtype是2001，所以它们是点。sdo几何图形列名为geometry。

我试过了：

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

这将返回空值。

我也尝试过索引：

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

这是错误输出。

如果我在一个集合内部寻找一个给定点，我会看到一个如下所示的数组：

1661473.42619016
2630277.19731551
1
0

数组中的前两个值是x和y，我不确定1和0扮演了什么角色。

表创建：

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

样例行数：

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)));

Answer 1

您需要大量的SQL技巧才能从该表中获取X和Y坐标:通过交叉连接TABLE操作符(但您必须使用表别名-这是唯一需要别名的时候)取消集合元素的嵌套，使用分析函数对每个ENTITY_ID的元素进行排序，然后使用MAX/CASE聚合结果并获得每行一个X和一个Y：

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