4 Replies Latest reply: Apr 2, 2013 10:02 PM by DAUM RSS

    How to process 'Sequencial Vertices to Polygon'

    DAUM
      Hi~

      I made a table of sequencial vertices from polygon using by 'SDO_UTIL.GETNUMVERTICES'.
      The table has following four columns,
      *"original primarykey, coord. of X, coord. of Y, ID(sequence)"*.

      PK     X     Y ID
      11847404     193952.2429     432465.9997     1
      11847404     193942.4635     432459.81      2
      11847404     193941.1902     432461.8218     3
      11847404     193919.1535     432447.8742     4
      11847404     193905.9558     432439.521     5
      11847404     193907.2291     432437.5093     6
      11847404     193898.0412     432431.694     7
      11847404     193919.1577     432398.3308     8
      11847404     193946.2585     432415.4836     9
      11847404     193973.3593     432432.6365     10


      After processing some logics to the table,
      I don't know how to reconstruct  table(sequencial vertices) to polygon.

      Is there any SDO function? or Should I use pivot the columns to CLOB and use 'FROM_WKTGEOMETRY'?


      Thanks and Regards
      Sangho, Kim.

      글 수정: 951997

      글 수정: 951997

      글 수정: 951997
        • 1. Re: How to process 'Sequencial Vertices to Polygon'
          FlyingGuy
          So let me see if I understand... You took an sdo_polygon then extracted the vertices and now you want to re-assemble them again?

          If that is the case and the data you show in your example is an incomplete polygon.

          The 1st and last vertices in a polygon must overlap.

          Yes you can just ripple through the records ( I recommend a stored procedure ) order by original key, by id and then concatenate them into the polygon wkt and then created the sdo object.
          • 2. Re: How to process 'Sequencial Vertices to Polygon'
            Simon Greener
            This will do it.
            WITH polyPoints As 
            (
             select 11847404 as pk, 193952.2429 as x, 432465.9997 as y, 1 as id from dual union all
             select 11847404 as pk, 193942.4635 as x, 432459.81 as y, 2 as id from dual union all
             select 11847404 as pk, 193941.1902 as x, 432461.8218 as y, 3 as id from dual union all
             select 11847404 as pk, 193919.1535 as x, 432447.8742 as y, 4 as id from dual union all
             select 11847404 as pk, 193905.9558 as x, 432439.521 as y, 5 as id from dual union all
             select 11847404 as pk, 193907.2291 as x, 432437.5093 as y, 6 as id from dual union all
             select 11847404 as pk, 193898.0412 as x, 432431.694 as y, 7 as id from dual union all
             select 11847404 as pk, 193919.1577 as x, 432398.3308 as y, 8 as id from dual union all
             select 11847404 as pk, 193946.2585 as x, 432415.4836 as y, 9 as id from dual union all
             select 11847404 as pk, 193973.3593 as x, 432432.6365 as y, 10 as id from dual 
            )
            select c.pk,sdo_geom.validate_geometry(c.polygon,0.005) as vGeom,  c.polygon
              from (SELECT a.pk, 
                           mdsys.sdo_geometry(2003,NULL,NULL,
                                              mdsys.sdo_elem_info_array(1,1003,1),
                                              CAST(MULTISET(SELECT b.COLUMN_VALUE 
                                                              FROM polyPoints b,
                                                                   TABLE(mdsys.sdo_ordinate_array(b.x,b.y)) b 
                                                             WHERE b.pk = a.pk 
                                                             ORDER BY b.id, rownum) 
                                                  AS mdsys.sdo_ordinate_array)) AS polygon
                      FROM polyPoints a
                     GROUP BY a.pk
                     ORDER BY a.pk
                    ) c;
            Note that vGeom shows, as FlyingGuy points out, that the geometry is not closed (13348).

            regards
            Simon

            Edited by: Simon Greener on Apr 2, 2013 9:53 PM - Fixed SDO_GTYPE to be 2003 and not 3003 (thanks jhm)
            • 3. Re: How to process 'Sequencial Vertices to Polygon'
              _jum
              One little remark to the Simon's solution:
              To get 2D-data -> use 2003 for SDO_GTYPE
              ...
                mdsys.sdo_geometry(2003,NULL,NULL,
              ...
              To get 3D-data -> add Z coordinates in the SDO_ORDINATES_ARRAY
              ...
                TABLE(mdsys.sdo_ordinate_array(b.x,b.y,0)) b 
              ...
              • 4. Re: How to process 'Sequencial Vertices to Polygon'
                DAUM
                That's what I looking for~!!!!!

                I'm always appreciative of you.