0

I have an Oracle 18c table that has an SDO_GEOMETRY column (lines):

create table a_sdo_geometry_tbl (line_id integer, shape mdsys.sdo_geometry);

insert into a_sdo_geometry_tbl (line_id, shape) 
values (1, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671539.6852734378,4863324.181436138, 671595.0500703361,4863343.166556185, 671614.013553706,4863350.343483042, 671622.2044153381,4863353.525396131))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (2, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (71534.5567096211,4863119.991809748, 671640.7384688659,4863157.132745253, 671684.8621150404,4863172.022995591))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (3, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671622.2044153381,4863353.525396131, 671633.3267164109,4863357.846229106, 671904.0614077691,4863451.286166754))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (4, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671684.8620521119,4863172.022995591, 671892.1496144319,4863244.141440067, 671951.2156571196,4863264.824310392, 671957.4471461186,4863266.847617676, 671966.8243856924,4863269.146632658))  )

select
    line_id,
    sdo_util.to_wktgeometry(shape) as well_known_text
from
    a_sdo_geometry_tbl;

   LINE_ID    WELL_KNOWN_TEXT
--------------------------------------------------------------------------------
         1    LINESTRING (671539.685273438 4863324.18143614, 671595.050070336 4863343.16655619, 671614.013553706 4863350.34348304, 671622.204415338 4863353.52539613)         
                                                                                
         2    LINESTRING (71534.5567096211 4863119.99180975, 671640.738468866 4863157.13274525, 671684.86211504 4863172.02299559)                                             
                                                                                
         3    LINESTRING (671622.204415338 4863353.52539613, 671633.326716411 4863357.84622911, 671904.061407769 4863451.28616675)                                            
                                                                                
         4    LINESTRING (671684.862052112 4863172.02299559, 671892.149614432 4863244.14144007, 671951.21565712 4863264.82431039, 671957.447146119 4863266.84761768, 671966.824385692 4863269.14663266)                                                       
                                                                                
4 rows selected.

For each line, I want to select each vertex as a separate row in a query/resultset.

I want to do this via a recursive WITH clause -- without using the GetVertices() function or a custom TYPE.

Is there a way to do that?


The resultset would look like this:

   LINE_ID  VERTEX_ID          X          Y
---------- ---------- ---------- ----------
         1          1 671539.685 4863324.18
         1          2 671595.050 4863343.17
         1          3 671614.014 4863350.34
         1          4 671622.204 4863353.53

         2          1 71534.5567 4863119.99
         2          2 671640.738 4863157.13
         2          3 671684.862 4863172.02

         3          1 671622.204 4863353.53
         3          2 671633.327 4863357.85
         3          3 671904.061 4863451.29

         4          1 671684.862 4863172.02
         4          2 671892.150 4863244.14
         4          3 671951.216 4863264.82
         4          4 671957.447 4863266.85
         4          5 671966.824 4863269.15

Hints:

The following functions might be useful:

PointN: Returns a point that is the nth vertex in the collection of vertices

GetNumVertices: Returns the number of vertices in the input geometry.


1

2 Answers 2

0

Option 1:

Use the WELL_KNOWN_TEXT function:

SELECT DISTINCT
  LINE_ID,
  ROUND(TO_NUMBER(REGEXP_SUBSTR (VALUE, '[^ - ]+', 1, 1 )),10) AS X,
  ROUND(TO_NUMBER(REGEXP_SUBSTR (VALUE, '[^ - ]+', 2, 1 )),10) AS Y
FROM
  (
    SELECT
      LINE_ID,
      TRIM(REGEXP_SUBSTR(STR, '[^,]+', 1, LEVEL)) AS VALUE
    FROM
      (
        SELECT
          LINE_ID,
          REPLACE(REPLACE(SUBSTR(WELL_KNOWN_TEXT,12),'(',''),')','') STR
        FROM
          (
            SELECT
              LINE_ID,
              SDO_UTIL.TO_WKTGEOMETRY(SHAPE) AS WELL_KNOWN_TEXT
            FROM
              A_SDO_GEOMETRY_TBL
          )
      )
      CONNECT BY INSTR(STR, ',', 1, LEVEL - 1) > 0
  )
ORDER BY  LINE_ID; 
0

Option 2:

Use spatial functions, including a custom PointN function, but not including the Get_Vertices function:

--Custom PointN fucntion: https://gis.stackexchange.com/a/412038/135445
create or replace function cust_pointn (g sdo_geometry, n number)
return sdo_geometry
as
begin
  return (
    sdo_geometry (
      2001,
      g.sdo_srid,
      sdo_point_type (
        g.sdo_ordinates((n-1) * 2 + 1),
        g.sdo_ordinates((n-1) * 2 + 2),
        null
      ),
      null,
      null
    )
  );
end;
/

--Generate rows for each vertex using recursive WITH: https://stackoverflow.com/a/65993625/10936066
with  rcte (line_id, vertex_id, num_vertices) as (
      select line_id, 1 as vertex_id, sdo_util.getnumvertices(shape) as num_vertices
      from a_sdo_geometry_tbl
      where sdo_util.getnumvertices(shape) > 0
      union all
      select line_id, vertex_id + 1, num_vertices
      from rcte
      where vertex_id < num_vertices
     )

--1. Select from the recursive WITH; has rows for each vertex
--2. Join to the original table to get the shape/line column
--3. Use the custom function to get the vertex shape/point (since I couldn't figure out how to include the shape column in the recursive WITH query)
--4. get the x and y from the vertex shape/point
select 
    a.line_id, 
    a.vertex_id,
    cust_pointn(shape,a.vertex_id) as vertex_point_shape,
    cust_pointn(shape,a.vertex_id).sdo_point.x as x,
    cust_pointn(shape,a.vertex_id).sdo_point.y as y
from 
    rcte a
left join
    a_sdo_geometry_tbl b
    on a.line_id = b.line_id
order by
    a.line_id,
    a.vertex_id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.