Oracle 9i 3D to 2D data for mapserver

Another weird thing we discovered in mapserver / OraSpatial is the fact that mapserver can not display 3D data. Fortunately Oracle has a function to reproject the data to 2D. To prevent that in each query, all the data is converted to 2d, before making the extent-selection we used a materialized view (snapshot) to reproject the data and keep it available for mapserver. In a snapsot all data is copied so it takes a lot of diskspace. In this months oracle magazine, u can read a nice article (http://oracle.com/technology/oramag/oracle/05-mar/o25data.html) on snapshots.

CREATE MATERIALIZED VIEW ITEM2D
BUILD IMMEDIATE
REFRESH NEXT sysdate + 1
AS select
SDO_LRS.CONVERT_TO_STD_GEOM(i.geometry,m.diminfo) as loc
from
item i,
user_sdo_geom_metadata m
where
m.table_name = 'item'
and
m.column_name = 'GEOMETRY'

In for example Toad, the materialized view is not stored in the list of views, but as a table. However the snapshot can not be removed using drop table, instead use drop materialized view ?item2D? (in quotes). Don?t forget to create a new spatial index on the materialized view (since its not a normal view)

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES ('ITEM2D', 'LOC',
MDSYS.SDO_DIM_ARRAY
(MDSYS.SDO_DIM_ELEMENT('X', 180000, 220000, 0.005),
MDSYS.SDO_DIM_ELEMENT('Y', 380000, 450000, 0.005)
),
19025);

and

CREATE INDEX si_ITEM2D on ITEM2D(LOC) indextype is mdsys.spatial_index