Hello!

I am struggling with Oracle Spatial (10.2.0.1.0 on Win2003). Maybe
somebody here can halp me. I have a table "SOUNDING" containing rows
with (essentially) point IDs "PID" and point geometries "GEOMETRY"
(SDO_GTYPE = 3001):

> DESCR SOUNDING;
Name
--------------------------------
PID NUMBER(12)
DATASETID VARCHAR2(16)
GEOMETRY MDSYS.SDO_GEOMETRY

A sample point looks like this:

GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO,
SDO_ORDINATES)
----------------------------------------------------------------
SDO_GEOMETRY(3001, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1),
SDO_ORDINATE_ARRAY(-70.0213, 40.44136, -267.0))

The points represent samples ordered along a measurement line.

Furthermore I have a table "DATASET" containing ID "DATASETID"
(referenced by SOUNDING.DATASETID) and a geometry attribute:

> DESCR DATASET;
Name
--------------------------------
DATASETID VARCHAR2(16)
LINEGEOMETRY MDSYS.SDO_GEOMETRY

Now, what I want to do: I need the measurement line as a line
geometry (SDO_GTYPE = 3002) and tried to find out what query to use
for translating the point geometries into a line along these points.
I could think of two ways: (1) adding the coordinates of each point
to the end of a line with SDO_UTIL.APPEND() directly after inserting
that point. Problem with that is that as the line grows (longer than
some 1000 points), SDO_UTIL.APPEND() gets _really_ slow. (2) First
inserting all the point geometries and then merging them with
SDO_AGGR_UNION(). This is the closest I could get:

UPDATE DATASET
SET LINEGEOMETRY = (
SELECT SDO_AGGR_UNION(SDOAGGRTYPE(GEOMETRY, 0.0001))
FROM (
SELECT GEOMETRY
FROM SOUNDING
WHERE DATASETID = 'NGDCA2075L01'
ORDER BY PID
)
)
WHERE DATASETID = 'NGDCA2075L01';

This is really fast. Problem with (2): SDO_AGGR_UNION() outputs the
point succession in arbitrary order, not ordered by PID as I need it
(I know that it performs a union operation and not a concatenation,
and that the ORDER BY PID is essentially useless here).

I'm sure there is another way to do a real concatenation than (1) -
I just don't find it. Anybody out there who can help?


Thanks a lot!

Benjamin