如何在Oracle中提取SDE.ST_GEOMETRY对象的POINTS属性

问题描述 #

我需要从Oracle表中提取Shape数据，目前已能获取entity、numpts、minx、miny等属性，但POINTS属性存储为BLOB类型，希望将其转换为VARCHAR类型。请问如何提取SDE.ST_GEOMETRY列中的POINTS属性值？

解决方案 #

使用TREAT(shape AS sde.st_geometry).points AS points即可成功提取POINTS属性并转为可读的VARCHAR类型。

表结构 #

CREATE TABLE X
(
  OBJECTID      INTEGER                         NOT NULL,
  GRID_ID       VARCHAR2(17 BYTE),
  H3_10_INT_ID  VARCHAR2(256 BYTE),
  CITY_NAME        VARCHAR2(30 BYTE),
  DISTRICT_NAME      VARCHAR2(30 BYTE),
  NEIGHBOURHOOD_NAME VARCHAR2(50 BYTE),
  SHAPE         SDE.ST_GEOMETRY
)
LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE  TBS_X
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  CACHE
  LOGGING
      STORAGE    (
                  INITIAL          104K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                 ))
TABLESPACE TBS_X
PCTUSED    0
PCTFREE    0
INITRANS   4
MAXTRANS   255
STORAGE    (
            INITIAL          400K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;

表数据示例 #

Insert into X
(OBJECTID, GRID_ID, H3_10_INT_ID, CITY_NAME,
DISTRICT_NAME, NEIGHBOURHOOD_NAME)
Values
(133, '8a1ec8000537fff', '622038258059280383', 55498, 'Tekirdag',
'Suleymanpasa', 'Osmanli');
COMMIT;

当前使用的查询语句 #

SELECT objectid,sde.st_entity(SHAPE),sde.st_numpoints(SHAPE),
sde.st_minx(SHAPE),sde.st_miny(SHAPE),sde.st_maxx(SHAPE),sde.st_maxy(SHAPE),sde.st_minz(SHAPE),sde.st_maxz(SHAPE),sde.st_maxm(SHAPE),sde.st_area(SHAPE),
sde.st_length(SHAPE),
sde.st_srid(SHAPE)
FROM X

内容的提问来源于stack exchange，提问作者charmander