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

Hello again!

I forgot something. Minor update...

Benjamin Hell wrote:
> (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';

To get a line geometry instead of a multipoint something, I manually
change SDO_GTYPE and SDO_ELEM_INFO afterwards:

UPDATE DATASET D
SET D.SIMPLEGEOMETRY.SDO_GTYPE = 3002,
D.SIMPLEGEOMETRY.SDO_ELEM_INFO =
SDO_ELEM_INFO_ARRAY(1, 2, 1)
WHERE D.DATASETID = 'NGDCA2075L01';


Regards,

Benjamin

If I were you I'd probably do a little PL/SQL function
which gets the points in the right order, assembles
info and ordinate arrays and returns a geometry object.

insert into lines select linefunction(x.datasetid) from (select distinct datasetid ...) x;

or so.

If you are good at it, you could even do an aggregate function
for this.

Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.