Loading DEM

1. Request partner to translate DEM data into X, Y, Z format. The filename called"tw20fix84.img_XYZ.txt"
2. Create Oracle Directory, PC_DATA, by system and grant read/write privieges to user, FSMS.
3. Create external table, rawtext.
4. Create a POINT constructor for convenience with default SRID 4326.

CREATE OR REPLACE FUNCTION point(
x NUMBER, y NUMBER, srid NUMBER DEFAULT 4326)
RETURN SDO_GEOMETRY
DETERMINISTIC
IS
BEGIN
  RETURN SDO_GEOMETRY(2001, srid, SDO_POINT_TYPE(x, y, NULL), NULL, NULL);
END;
/

5. Create a table, DEM, with a SDO_GEOMETRY column, location.

CREATE TABLE DEM
(
  X NUMBER
, Y NUMBER
, Z VARCHAR2(10)
, LOCATION MDSYS.SDO_GEOMETRY
);

6. Load data, rawtext, into table, DEM, by using insert select SQL.
INSERT INTO dem
SELECT x, y, z, point(x, y)
FROM rawtext;

7. Update USER_SDO_GEOM_METADATA
Before update SDO_GEOM_METADATA, I got to know the MBR
SELECT SDO_AGGR_MBR(location) FROM dem;
or for this , I use

SELECT MIN(x), MAX(x), MIN(y), MAX(y), MIN(z), MAX(z)
FROM dem;

    MIN(X)     MAX(X)     MIN(Y)     MAX(Y) MIN(Z)     MAX(Z)
---------- ---------- ---------- ---------- ---------- ----------
119.982565  122.02683 21.8673002 25.3523955 0          999

Use this information to update USER_SDO_GEOM_METADATA

INSERT INTO

USER_SDO_GEOM_METADATA

VALUES (

'DEM','LOCATION',

SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',119.982565,122.02683,10),

SDO_DIM_ELEMENT('Y',21.8673002,25.3523955,10)

 ),4326);

8. Now, it's time to create spatial index

CREATE INDEX sdo_dem_location ON dem (location)

INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Then we can perform some queries.