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
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)
So, that tells me that the problem is with ST_PointN. ST_PointN appears to only work for lines:
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