Oracle Spatial: Update a numeric column based on Point-coordinates-CodePudding

(Already solved with a second solution, but I wonder why the first idea does not work).

I have a table with FID, Geom (Point-data), orientation and so on. I want to update the Orientation based on the coordinates, like "set orientation = 99 where X = something and Y = something"

I have this:

UPDATE WW_POINT
   SET
    ORIENTATION = 99.9
 WHERE
        F_CLASS_ID_ATTR = 77   
       AND GEOM.SDO_POINT.X = 2695056.511
       AND GEOM.SDO_POINT.Y = 1279718.364;

The result is:

Error starting at line : 1 in command -
UPDATE WW_POINT
   SET
    ORIENTATION = 99.9
 WHERE
        F_CLASS_ID_ATTR = 77   -- haltunsgverbindung
       AND GEOM.SDO_POINT.X = 2695056.511
       AND GEOM.SDO_POINT.Y = 1279718.364
Error at Command Line : 7 Column : 12
Error report -
SQL Error: ORA-00904: "GEOM"."SDO_POINT"."Y": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

A simple select returns X and Y as expected:

SELECT
    X.GEOM.SDO_POINT.X
  , X.GEOM.SDO_POINT.Y
  FROM
    WW_POINT X

So the question is: What is wrong here?

My second solution with SDO_EQUAL seem to work fine:

UPDATE WW_POINT
   SET
    ORIENTATION = 389.608
 WHERE
        F_CLASS_ID_ATTR = 77   -- haltunsgverbindung

       AND SDO_EQUAL (
        GEOM
      , MDSYS.SDO_GEOMETRY (
            2001
          , 2056
          , SDO_POINT_TYPE (
                2695056.511
              , 1279718.364
              , NULL
            )
          , NULL
          , NULL
        )
    ) = 'TRUE';

CodePudding user response：

Comparing both of your queries: the second one (working) has a form <table alias>.<column name>.<attr>.<attr>, but the first one is <column name>.<attr>.<attr>.

From the documentation:

t_alias

Specify a correlation name, which is an alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods.

Given this sample table:

create table t (p)
as
select
  SDO_GEOMETRY (
    2001, 2056
    , SDO_POINT_TYPE (1, 1, NULL)
    , NULL, NULL
  )
from dual

A query without table alias fails:

select t.p.sdo_point.x
from t
where t.p.sdo_point.x = 1

ORA-00904: "T"."P"."SDO_POINT"."X": invalid identifier

And the query with table alias works as expected allowing attribute access in the select list as well as in the where clause:

select t.p.sdo_point.x
from t t
where t.p.sdo_point.x = 1

P.SDO_POINT.X
1

db<>fiddle here