ST_GeomFromCollection function for Oracle

Bud — Fri, 17 Jun 2022 07:12:25 GMT

SDE.ST_GEOMETRY for PostgreSQL has an ST_GeomFromCollection function:

--Return each linestring in the multilinestring

SELECT sde.st_astext((sde.st_geomfromcollection(gst.shape)).st_geo) shapetext, ((sde.st_geomfromcollection(gst.shape)).path[1]) path
 FROM ghanasharktracks gst;

shapetext                                                                                     path
-----------------------------------------------------------------------------------------------------------
"LINESTRING Z ( 1.00000000 1.00000000 0.00000000, 1.00000000 6.00000000 0.00000000)"          1
"LINESTRING Z ( 1.00000000 3.00000000 0.00000000, 3.00000000 3.00000000 0.00000000)"          2
"LINESTRING Z ( 3.00000000 1.00000000 0.00000000, 3.00000000 3.00000000 0.00000000)"          3
"LINESTRING Z ( 4.00000000 1.00000000 0.00000000, 4.00000000 6.00000000 0.00000000)"          4

It would be really helpful if we had the same functionality in Oracle. For example, it would simplify this query: Select feature parts and vertices as rows (ST_GEOMETRY)

I don't see why that couldn't be done in Oracle. The return type could be a nested table. In the FROM clause, we could cross join to the nested table to get the feature parts.

Cross-joining with a table object propagates rows (without needing Table() function)
At what version did the TABLE() keyword for table collection expressions become optional?
Cross join to varray without using Table() expression

Could Esri consider creating an ST_GeomFromCollection function for Oracle?

idea ST_GeomFromCollection function for Oracle in ArcGIS Enterprise Ideas

ST_GeomFromCollection function for Oracle