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

How to process 'Sequencial Vertices to Polygon'

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'
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'
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'
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'
That's what I looking for~!!!!!

I'm always appreciative of you.