SQL & PL/SQL

Hi,

I have some spatial data, of which I will try to describe the relevant aspects:

- A LineSegment table, which contains information on cables, including Area ID (approx 4 million rows)
- A Location table, which contains a LineSegmentID (one-to-one), and a Geometry (spatial) column (approx 4 million rows).

What I need to do is create a new table, containing conceptual "nodes", containing the following columns:

- NodeID (number)
- LineSegmentID (number)
- LineSegmentEnd (1 or 2)

So I need to work out, for each cable, which other cables it connects to, by comparing its endpoints with the endpoints of other cables in the same area. An area contains up to 464 cables. There are a total of 160 thousand areas.

I am trying to work out the most efficient way of achieving this, ideally by performing a batch job that will take less than half an hour. Oracle is relatively new to me, but I am guessing the correct approach would be to use a series of intermediate (staging) tables, as I believe nested cursors would be way too slow (I have performed a simple test to confirm this).

I guess I will need to extract, into a temp table, the start point and end point of each cable using SDO_LRS.GEOM_SEGMENT_START_PT and SDO_LRS.GEOM_SEGMENT_END_PT, along with area ID. Then join the table with itself and use SDO_GEOM.SDO_DISTANCE to work out which points are close to each other (e.g. less than one metre). However I am struggling to outline a step by step process.

Does anyone have any thoughts which may help?

(Oracle 11g)

Thanks