0

Oracle 18c 10.7.1 EGDB:

I want to write a SQL query that has rows with the XY coordinates of each vertex of an SDE.ST_GEOMETRY polygon feature class.

I can do it for a line FC using a cross join, a numbers table, and ST_PointN:

select   
    a.objectid,
    b.number_ as vertex_id,
    a.sde.st_x(sde.st_pointn(a.shape, b.number_)) as x,
    a.sde.st_y(sde.st_pointn(a.shape, b.number_)) as y
from
    infrastr.active_transportation a
cross join 
    infrastr.numbers b
where    
    b.number_ <= sde.st_numpoints(a.shape)
    and a.objectid = 14113

enter image description here

But if I run that query on a polygon FC instead of a line FC, then I get an error:

ORA-20003: ST_Geometry type must be an ST_LINESTRING or ST_MULTIPOINT type.
ORA-06512: at "SDE.ST_GEOMETRY_OPERATORS", line 2614

If I comment out the lines that use ST_PointN, then the query runs successfully:

select   
    a.objectid,
    b.number_ as vertex_id
    --a.sde.st_x(sde.st_pointn(a.shape, b.number_)) as x,
    --a.sde.st_y(sde.st_pointn(a.shape, b.number_)) as y
from
    infrastr.bc_polygons a
cross join 
    infrastr.numbers b
where    
    b.number_ <= sde.st_numpoints(a.shape)

enter image description here

So, that tells me that the problem is with ST_PointN. ST_PointN appears to only work for lines:

ST_PointN

ST_PointN takes an ST_LineString and an integer index and returns a point that is the nth vertex in the ST_LineString's path.

Is there a way to get a specific vertex of a polygon using ST_GEOMETRY functions?


Edit:

ArcGIS Enterprise Ideas: SQL Function to get specific vertex of SDE.ST_GEOMETRY polygon

0

Your Answer

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