Querying 3D Data in Oracle (Repost)

From: Jean Anderson <jean_at_beno.CSS.GOV>
Date: 20 May 92 20:44:40 GMT
Message-ID: <50765_at_seismo.CSS.GOV>

In <50764_at_seismo.CSS.GOV>, I wrote:
> Is anybody working with 3 dimensional data in oracle? I know how to
> handle 2D time series data, but 3D frankly has me stumped. And I'm
> getting more and more questions about it.

My original posting was too terse and left out the key question:

     How do you efficiently query 3D data?
                ^^^^^^^^^^^

I'm getting lots of email that suggests storing polar coordinates in the database. This is, of course, one way to do it, but very slow.

2D Data

---

The seismic time series data we work with has 40 samples per second. If we stored each data sample in the database, each channel would daily insert about 3.5 million records into the database. An analyst querying 4 hours of data from many stations (100 channels is reasonable) would retrieve about 57.6 million records from the database, from which a picture would have to be constructed and displayed (preferably the same day).

Instead of storing each sample in the database, we store time series "blobs" on disk. Records in the database point to these blobs and contain, among other things, start time, end time, directory/file, and an offset in that file where the time series segment starts. An analyst querying 4 hours of data extracts several hundred records from the database, instead of 57 million, and the squiggly lines are drawn in seconds. The query extracting that data is simple and efficient. (Details are available, but I'm trying to keep this posting from becoming a dissertation).

3D Data

---

I really doubt that GIS products store each polar coordinate in the database at every display resolution required. Same doubt for CAD. But I am admittedly naive when it comes to graphics data (if anybody knows of any publications on "graphics for DBA's", let me know). The question is, do they use a database at all? if so, how much of the data can they reasonably manage directly in the database and still meet performance requirements?

Storing 3D data in image blobs would be much better than storing the polar coordinates. But you still have to retrieve them and piece them together into a single picture. Complicating it even further, it is likely that images (say, satellite images) were taken at a different orientations and have to be rotated so the final picture is coherent. Add a time component (say, a weather map displaying a storm system whirling across the earth over a period of time) and my eyeballs start twirling. How much can you use Oracle (well, any rdbms, I suppose) for managing storage and display of this type of data?

• jean

+-----------------------------------------------------------------------+
| Jean Anderson, DBA                       email:  jean_at_seismo.css.gov  |
| SAIC Open Systems Division, MS A2-F         or:  jean_at_esosun.css.gov  |
| 10210 Campus Point Drive                 phone:  (619)458-2727        |
| San Diego, CA  92121                       fax:  (619)458-4993        |
+-----------------------------------------------------------------------+

Received on Wed May 20 1992 - 22:44:40 CEST