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.
