Database Software

Hi all,

sorry for a potentially newbie spatial question.

Is there any reason why we can't select the x and y coords from a point type within SQL but rather have to use pl/sql if we want to split the geometry object out?

SQL> create table geom_foo (locn sdo_geometry);

Table created.

SQL>
SQL> insert INTO GEOM_FOO values (SDO_GEOMETRY(
  2                      2001,
  3                      8307,
  4                      SDO_POINT_TYPE(-180, -90, NULL),
  5                      NULL,
  6                      NULL));

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select locn.sdo_point.x
  2    from geom_foo;
select locn.sdo_point.x
       *
ERROR at line 1:
ORA-00904: "LOCN"."SDO_POINT"."X": invalid identifier


SQL>
SQL> declare
  2     v_locn sdo_geometry;
  3  begin
  4
  5     select locn
  6       into v_locn
  7       from geom_foo;
  8
  9     dbms_output.put_line(v_locn.sdo_point.x);
 10
 11  end;
 12     /
-180

PL/SQL procedure successfully completed.