Definition
ST_GeomFromCollection returns a set of st_geometry rows. Each row comprises a geometry and an integer. The integer represents the geometry's position in the set.
Use the ST_GeomFromCollection function to access each individual geometry in a multipart geometry. When the input geometry is a collection or multipart geometry (for example, ST_MultiLineString, ST_MultiPoint, ST_MultiPolygon), ST_GeomFromCollection returns a record for each of the collection components, and the path expresses the position of the component in the collection.
If you use ST_GeomFromCollection on a simple geometry (for example, ST_Point, ST_LineString, ST_Polygon), a single record is returned with an empty path since there is only one geometry.
Syntax
sde.st_geomfromcollection (shape sde.st_geometry)
To return only the geometry, use (sde.st_geomfromcollection (shape)).st_geo.
To return only the position of the geometry, use (sde.st_geomfromcollection (shape)).path[1].
Return type
ST_Geometry set
Example
In this example, create a multiline feature class (ghanasharktracks) containing a single feature with a four-part shape.
--Create the feature class.
CREATE TABLE ghanasharktracks (objectid integer, shape sde.st_geometry);
--Insert a multiline with four parts using SRID 4326.
INSERT INTO ghanasharktracks VALUES
(1,
sde.st_geometry('MULTILINESTRING Z (( 1 1 0, 1 6 0),(1 3 0, 3 3 0),(3 1 0, 3 3 0), (4 1 0, 4 6 0))',
4326
)
);
To confirm the field contains data, query the table. Use ST_AsText directly on the shape field to see the shape coordinates as text. Note that the text description of the multilinestring is returned.
--View inserted feature.
SELECT gst_orig.objectid, sde.st_astext(gst_orig.shape) shapetext
FROM ghanasharktracks gst_orig;
shapetext
-------------------------------
"MULTILINESTRING Z (( 1.00000000 1.00000000 0.00000000, 1.00000000 6.00000000 0.00000000),(1.00000000 3.00000000 0.00000000, 3.00000000 3.00000000 0.00000000),(3.00000000 1.00000000 0.00000000, 3.00000000 3.00000000 0.00000000), (4.00000000 1.00000000 0.00000000, 4.00000000 6.00000000 0.00000000))"
To return each linestring geometry individually, use the ST_GeomFromCollection function. To see the geometry as text, this example uses the ST_AsText function with the 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