2

I want to loop through the vertices in a SHAPE/GEOMETRY column in a user-defined data type (ESRI's version of ST_GEOMETRY). I've posted the details in a GIS SE post here: Update sde.st_geometry M values to cumulative distance (geometric length)

I think this would normally be done in a stored procedure (probably in conjunction with the sde.ST_GEOMETRY functions ST_NumPoints and ST_PointN) . However, I don't have CREATE PROCEDURE permissions, so I can't make a stored procedure.

As an alternative to creating a stored procedure, is there a way to loop through the vertices using an SQL statement instead?

Oracle 12c (12.1.0.2.0)

Improve this question
3
  • 1
    The model clause has iterations in it. Not sure about the gis part but if you can use the model clause that would performance wise almost certainly be a winner. If readability and maintainability counts …. It is pretty advanced use of sql
    – user953
    Commented Sep 12, 2021 at 9:40
  • @ik_zelf : I wonder if the recursive WITH clause could be used.
    – User1974
    Commented Sep 27, 2021 at 23:19
  • I have my doubts. Recursion can be very elegant when used in their type of problems. The model clause is aimed at working with spreadsheet style organized data. Iterations are an important control in there and not a side effect.
    – user953
    Commented Sep 28, 2021 at 6:09

1 Answer 1

Reset to default
6
+50

You can put a pl/sql in an anonymous block. Something like this might help:

declare
  v_vertices  your_user_defined_data_type;

  cursor your_cursor_c
  is
    select vertices
    from   your_table
    where  your_column = 'A VALUE';

begin
    open your_cursor_c;
    loop
      fetch your_cursor_c
      into  v_vertices;
      exit when your_cursor_c%notfound;

      ST_NumPoints(v_vertices);
      ST_PointN(v_vertices);

      commit;
    end loop;
end;

Also with 12c there is some inline procedure/function stuff that you can do. Not sure if it is necessary but here is a link: https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1.

Improve this answer
2
  • 1
    Essentially you can do almost anything with an anonymous PL/SQL block that you can do with a procedure or package.
    – Gandolf989
    Commented Sep 13, 2016 at 19:28
  • 1
    The link about declaring functions in the WITH clause is especially helpful. I can make functions without actually having the CREATE FUNCTION privilege. Which is awesome. All I have to do is replace the word CREATE with the word WITH.
    – User1974
    Commented Nov 23, 2016 at 22:06

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.