how to extract points attribute in st_geometry object in oracle

Question

I would like to extract shape data from a table. I can get entity,numpts,minx,miny all the attributes except POINTS which is is BLOB. I would like to get the data as varchar. How can I extract points attrribute's value in sd.st_geometry column ?

SOLUTION:I did extract POINTS attribute as treat(shape as sde.st_geometry).points as points. Thanks.

This is my table

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;

This is one sample of the table.

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;

You can see the sample shape object when I double click

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

Please edit the question to include a minimal reproducible example with: the CREATE TABLE and INSERT statements for your sample data; an explanation of the logic that you want to implement (i.e. what format, etc. do you want to get the points as); and the expected output for that sample data. — MT0
– MT0, Commented Mar 11, 2024 at 8:30
I edited the question with create and insert statements thanks — charmander
– charmander, Commented Mar 11, 2024 at 9:10
Your INSERT does not provide a minimal reproducible example as you never insert anything into the shape column so it will be NULL. Please provide a way to replicate the problem and also explain what format you want the output as. — MT0
– MT0, Commented Mar 11, 2024 at 9:17
I have selected the output of select *from x where objectid=1 then I clicked the export dataset and chosen export dataset as insert statements and then got this result. I dont know why the exported insert statement as like this. You may try also according to the image I have inserted above and try it you will see what I mean. Thanks ... — charmander
– charmander, Commented Mar 11, 2024 at 10:48

MT0 · Accepted Answer · 2024-03-11 09:40:16Z

0

If you have the sample data:

CREATE TABLE X
(
  OBJECTID           INTEGER           NOT NULL,
  SHAPE              ST_GEOMETRY
);

INSERT INTO x (objectid, shape)
VALUES (
  1,
  ST_LINESTRING(
    ST_POINT_ARRAY(
      ST_POINT(1, 2, 4326),
      ST_POINT(3, 4, 4326),
      ST_POINT(5, 6, 4326)
    )
  )
);

Then you can use:

SELECT objectid,
       TREAT(x.shape AS ST_LINESTRING).ST_NUMPOINTS() AS numpoints,
       p.*
FROM   x
       CROSS JOIN LATERAL (
         SELECT p.st_x() AS x,
                p.st_y() AS y
         FROM   TABLE(TREAT(x.shape AS ST_LINESTRING).ST_POINTS()) p
       ) p

Which outputs:

OBJECTID	NUMPOINTS	X	Y
1	3	1	2
1	3	3	4
1	3	5	6

fiddle

answered Mar 11, 2024 at 9:40

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

charmander Over a year ago

I am getting error -> ORA-00932: inconsistent datatypes: expected SDE.ST_GEOMETRY got MDSYS.ST_LINESTRING

charmander Over a year ago

When I double click "Points" (hugeblob) I get only dot (.)

charmander Over a year ago

I did extract POINTS attribute as treat(shape as sde.st_geometry).points as points. Thanks.

Collectives™ on Stack Overflow

how to extract points attribute in st_geometry object in oracle

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related