CREATE TABLE geo_table(pkey integer, geom_id VARCHAR(20), geom ST_Geometry);
INSERT INTO geo_table VALUES(0, 'S101', new ST_Geometry('Point(10 20 5)'));
INSERT INTO geo_table VALUES(1, 'S102', new ST_Geometry('LineString(10 10, 20 20)'));
INSERT INTO geo_table VALUES(2, 'S103', new ST_Geometry(
'Polygon((0 0, 0 10, 10 10, 10 0, 0 0))'));
INSERT INTO geo_table VALUES(3, 'S104', new ST_Geometry('LineString(EMPTY)'));
INSERT INTO geo_table VALUES(4, 'S105', NULL);
INSERT INTO geo_table VALUES(5, 'S106', new ST_Geometry(
'MultiLineString((10 10, 20 20),(30 30, 40 40, 50 50))'));
INSERT INTO geo_table VALUES(6, 'S107', new ST_Geometry(
'Polygon((0 0, 0 10, 10 10, 10 0, 0 0), (2 2, 2 8, 8 8, 8 2, 2 2))'));
SELECT PointsTable.geom_id1 (FORMAT 'X(8)'),
PointsTable.element_id (FORMAT 'Z'),
PointsTable.ring_id (FORMAT 'Z'),
PointsTable.point_id (FORMAT 'Z'),
PointsTable.geomType (FORMAT 'X(15)'),
PointsTable.x (FORMAT '99.99'),
PointsTable.y (FORMAT '99.99'),
PointsTable.z (FORMAT '99.99')
FROM GeometryToRows(ON (SELECT geom_id, geom FROM geo_table) )
AS PointsTable (geom_id1, element_id, ring_id, point_id, geomType, x, y, z)
ORDER BY 1, 2, 3, 4;
The output would look like the following:
geom_id1 element_id ring_id point_id geomType x y z -------- ---------- ------- -------- --------------- ----- ----- ----- S101 1 ? 1 POINT 10.00 20.00 05.00 S102 1 ? 1 LINESTRING 10.00 10.00 ? S102 1 ? 2 LINESTRING 20.00 20.00 ? S103 1 1 1 POLYGON 00.00 00.00 ? S103 1 1 2 POLYGON 00.00 10.00 ? S103 1 1 3 POLYGON 10.00 10.00 ? S103 1 1 4 POLYGON 10.00 00.00 ? S103 1 1 5 POLYGON 00.00 00.00 ? S104 1 ? 1 LINESTRING ? ? ? S105 1 ? 1 ? ? ? ? S106 1 ? 1 MULTILINESTRING 10.00 10.00 ? S106 1 ? 2 MULTILINESTRING 20.00 20.00 ? S106 2 ? 1 MULTILINESTRING 30.00 30.00 ? S106 2 ? 2 MULTILINESTRING 40.00 40.00 ? S106 2 ? 3 MULTILINESTRING 50.00 50.00 ? S107 1 1 1 POLYGON 00.00 00.00 ? S107 1 1 2 POLYGON 00.00 10.00 ? S107 1 1 3 POLYGON 10.00 10.00 ? S107 1 1 4 POLYGON 10.00 00.00 ? S107 1 1 5 POLYGON 00.00 00.00 ? S107 1 2 1 POLYGON 02.00 02.00 ? S107 1 2 2 POLYGON 02.00 08.00 ? S107 1 2 3 POLYGON 08.00 08.00 ? S107 1 2 4 POLYGON 08.00 02.00 ? S107 1 2 5 POLYGON 02.00 02.00 ?
Notice how the ORDER BY clause causes the rows to be arranged by ID values, keeping the rows for each geometry and sub-geometry (for Multi types) together and organized hierarchically by geometries and their sub-geometries. Otherwise, the rows would be returned in a random order, and the relationships would be more difficult to discern.