Spatial: Make line out of (many) points

From: Benjamin Hell <bhell_at_spamfence.net>
Date: Fri, 01 Sep 2006 16:45:07 +0200
Message-ID: <4lqvbtF397fqU1@individual.net>

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 Received on Fri Sep 01 2006 - 09:45:07 CDT