尝试"SDO_INSIDE“时出现”'invalid“

Question

我正在尝试检查“访客”是否在“村落建筑”内。但是每次我执行SELECT语句时，我都会得到一个错误:表"visitors“中的"position”是一个无效的标识符。

DROP TABLE village CASCADE CONSTRAINTS;

CREATE TABLE village (
  building_id integer PRIMARY KEY,
  name VARCHAR2(30),
  visitors integer,
  building SDO_GEOMETRY
);

/*****************index********************/
delete from user_sdo_geom_metadata where table_name = 'VILLAGE';

INSERT INTO user_sdo_geom_metadata
(   TABLE_NAME,
    COLUMN_NAME,
    DIMINFO,
   SRID
)
VALUES
(   'village',
    'building',
    SDO_DIM_ARRAY( -- 20X20 grid
    SDO_DIM_ELEMENT('X', 0, 1000, 0.5),
    SDO_DIM_ELEMENT('Y', 0, 1000, 0.5)
),
  NULL -- SRID
);


-- inserts
INSERT INTO village VALUES(1,'Kirche', 4,
    SDO_GEOMETRY(
    2003, 
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,1), 
    SDO_ORDINATE_ARRAY(100,100, 100,120, 80,120, 80,150 ,100,200, 150,200, 150,150, 200,150, 200,120, 150,120, 150,100)
    )
);


drop table visitors;
create table visitors(
  id integer,
  position SDO_GEOMETRY
);

drop sequence visitors_seq;
create sequence visitors_seq;

INSERT INTO visitors VALUES (visitors_seq.nextval,
  SDO_GEOMETRY(
    2001,
    NULL,
    SDO_POINT_TYPE(160, 100, NULL),
    NULL,
    NULL
  )
);
commit;


SELECT * FROM village  WHERE SDO_INSIDE(village.building, visitors.position) = 'TRUE';

是否必须使用SDO_GEOMETRY数据而不是"visitors.position“作为参数？

Answer 1

您的查询FROM子句没有引用表visitors。您需要将其包括在内。