The Customer's project is going to production

After a lot of work with SI partner on customer's (NFA) GIS DW project, we finally to the point of going production.
We did something on Oracle Spatial Raster
1. Loading image is not really a big issue and the display image performance with Oracle mapViewer is quit good. The customer is satisfied.
2. Though the entire system combines MapXtreme and Oracle MapViewer + Spatial. MapXtreme used as render vector data, though MapViewer can do too, partner's favor tool. We got to some works like creating indexes on tables where MapXtreme stores the vector data on ordinary columns and Oracle geometry columns. This boots the performance.

Create Spatial Index Notes

If the index creation does not complete for any reason, the index is invalid and must
be deleted with the DROP INDEX [FORCE] statement.

R-tree indexes can be built on two, three, or four dimensions of data. The default number of dimensions is two, but if the data has more than two dimensions, you can use the sdo_indx_dims parameter keyword to specify the number of dimensions on which to build the index. However, if a spatial index has been built on more than two dimensions of a layer, the only spatial operator that can be used against that layer is SDO_FILTER (the primary filter or index-only query), which considers all dimensions. The SDO_RELATE, SDO_NN, and SDO_WITHIN_DISTANCE operators are disabled if the index has been built on more than two dimensions.

Rollback Segment
The rollback segment should be 100*n bytes, where n is the number of rows of data to be indexed.

SORT_AREA_SIZE
The optimal value depends on the database size, but a good guideline is to make it at least 1 million bytes when you create an R-tree index. For example, to change the value to 20 million bytes: ALTER SESSION SET SORT_AREA_SIZE = 20000000;

Tablespace
The tablespace specified with the tablespace keyword in the CREATE INDEX statement (or the default tablespace if the tablespace keyword is not specified) is used to hold both the index data table and some transient tables that are created for internal computations.

• The R-tree index data table requires approximately 70*n bytes (where n is the number of rows in the table).
• The transient tables require up to approximately 200*n bytes (where n is the number of rows in the table); however, this space is freed up after the R-tree index is created.

For large tables (over 1 million rows), a temporary tablespace may be needed to perform internal sorting operations. The recommended size for this temporary tablespace is 100*n bytes, where n is the number of rows in the table.

Performance-related Guideline

The following performance-related guidelines apply to the use of spatial operators,
procedures, and functions:

• If an operator and a procedure or function perform comparable operations, and if the operator satisfies your requirements, use the operator. For example, unless you need to do otherwise, use SDO_RELATE instead of SDO_GEOM.RELATE, and use SDO_WITHIN_DISTANCE instead of SDO_GEOM.WITHIN_DISTANCE.
• With operators, always specify TRUE in uppercase. That is, specify = 'TRUE', and do not specify <> 'FALSE' or = 'true'.
• With operators, use the /*+ ORDERED */ optimizer hint if the query window comes from a table. (You must use this hint if multiple windows come from a table.) See the Usage Notes and Examples for specific operators for more information.

Taiwan Coordinate System - TWD 67 & TWD 97 Testing Scripts

To test the Coordinate Systems, just use SQL*Plus
The following is the examples
SQL> select
sdo_cs.transform(
SDO_GEOMETRY(
2001,
20000067,
SDO_POINT_TYPE(
121 + 5 / 60 + 2.255 / 3600,
23 + 37 / 60 + 42.655 / 3600,
NULL),
NULL,
NULL),
200000671)
from
dual;

SDO_CS.TRANSFORM(SDO_GEOMETRY(2001,20000067,SDO_POINT_TYPE(121+5/60+2.255/3600,2
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 200000671, SDO_POINT_TYPE(258566.571082774, 2613894.79614095,
NULL), NULL, NULL)


SQL> select
sdo_cs.transform(
SDO_GEOMETRY(
2001,
200000671,
SDO_POINT_TYPE(
258566.57,
2613894.80,
NULL),
NULL,
NULL),
20000067)
from
dual;

SDO_CS.TRANSFORM(SDO_GEOMETRY(2001,200000671,SDO_POINT_TYPE(258566.57,2613894.80
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 20000067, SDO_POINT_TYPE(121.083959711632, 23.6285153121797,
NULL), NULL, NULL)

SQL>

Taiwan Coordinate System - TWD 67 & TWD 97

Here is how to add Taiwan Coordinate Systems - TWD 67 & TWD97- in Oracle Database.

---

The following is the EPSG format:

insert into cs_srs (
cs_name,
srid,
auth_srid,
auth_name,
wktext,
cs_bounds)
values (
'TWD67',
2000067,
2000067,
'Oracle Taiwan',
'GEOGCS [ "TWD67", DATUM ["Australian Geodetic Datum 1966 (EPSG ID 6202)", SPHEROID ["Australian National Spheroid (EPSG ID 7003)", 6378160, 298.25]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994328]]',
null);

insert into cs_srs (
cs_name,
srid,
auth_srid,
auth_name,
wktext,
cs_bounds)
values (
'TM67 / TWD67',
20000671,
20000671,
'Oracle Taiwan',
'PROJCS["TM67 / TWD67", GEOGCS [ "TWD67", DATUM ["Australian Geodetic Datum 1966 (EPSG ID 6202)", SPHEROID ["Australian National Spheroid (EPSG ID 7003)", 6378160, 298.25]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994328]], PROJECTION ["TM67 / TWD67 (EPSG OP 2000067)"], PARAMETER ["Latitude_Of_Origin", 0], PARAMETER ["Central_Meridian", 121], PARAMETER ["Scale_Factor", .9999], PARAMETER ["False_Easting", 250000], PARAMETER ["False_Northing", 0], UNIT ["Meter", 1]]',
null);

insert into cs_srs (
cs_name,
srid,
auth_srid,
auth_name,
wktext,
cs_bounds)
values (
'TWD97',
2000097,
2000097,
'Oracle Taiwan',
'GEOGCS [ "TWD97", DATUM ["World Geodetic System 1984 (EPSG ID 6326)", SPHEROID ["WGS 84 (EPSG ID 7030)", 6378137, 298.257223563]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994328]]',
null);

insert into cs_srs (
cs_name,
srid,
auth_srid,
auth_name,
wktext,
cs_bounds)
values (
'TM97 / TWD97',
20000971,
20000971,
'Oracle Taiwan',
'PROJCS["TM97 / TWD97", GEOGCS [ "TWD97", DATUM ["World Geodetic System 1984 (EPSG ID 6326)", SPHEROID ["WGS 84 (EPSG ID 7030)", 6378137, 298.257223563]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994328]], PROJECTION ["TM97 / TWD97 (EPSG OP 2000097)"], PARAMETER ["Latitude_Of_Origin", 0], PARAMETER ["Central_Meridian", 121], PARAMETER ["Scale_Factor", .9999], PARAMETER ["False_Easting", 250000], PARAMETER ["False_Northing", 0], UNIT ["Meter", 1]]',
null);

---

The following is the pre-Oracle 10gR2 translation

insert into cs_srs (
cs_name,
srid,
auth_srid,
auth_name,
wktext,
cs_bounds)
values (
'TWD67',
20000067,
20000067,
'Oracle Taiwan',
'GEOGCS [ "TWD67", DATUM ["Australian Geodetic Datum 1966 (EPSG ID 6202)", SPHEROID ["Australian National Spheroid (EPSG ID 7003)", 6378160, 298.25]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994328]]',
null);

insert into cs_srs (
cs_name,
srid,
auth_srid,
auth_name,
wktext,
cs_bounds)
values (
'TM67 / TWD67',
200000671,
200000671,
'Oracle Taiwan',
'PROJCS["TM67 / TWD67", GEOGCS [ "TWD67", DATUM ["Australian Geodetic Datum 1966 (EPSG ID 6202)", SPHEROID ["Australian National Spheroid (EPSG ID 7003)", 6378160, 298.25]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994328]], PROJECTION ["Transverse Mercator"], PARAMETER ["Latitude_Of_Origin", 0], PARAMETER ["Central_Meridian", 121], PARAMETER ["Scale_Factor", .9999], PARAMETER ["False_Easting", 250000], PARAMETER ["False_Northing", 0], UNIT ["Meter", 1]]',
null);

insert into cs_srs (
cs_name,
srid,
auth_srid,
auth_name,
wktext,
cs_bounds)
values (
'TWD97',
20000097,
20000097,
'Oracle Taiwan',
'GEOGCS [ "TWD97", DATUM ["World Geodetic System 1984 (EPSG ID 6326)", SPHEROID ["WGS 84 (EPSG ID 7030)", 6378137, 298.257223563]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994328]]',
null);

insert into cs_srs (
cs_name,
srid,
auth_srid,
auth_name,
wktext,
cs_bounds)
values (
'TM97 / TWD97',
200000971,
200000971,
'Oracle Taiwan',
'PROJCS["TM97 / TWD97", GEOGCS [ "TWD97", DATUM ["World Geodetic System 1984 (EPSG ID 6326)", SPHEROID ["WGS 84 (EPSG ID 7030)", 6378137, 298.257223563]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994328]], PROJECTION ["Transverse Mercator"], PARAMETER ["Latitude_Of_Origin", 0], PARAMETER ["Central_Meridian", 121], PARAMETER ["Scale_Factor", .9999], PARAMETER ["False_Easting", 250000], PARAMETER ["False_Northing", 0], UNIT ["Meter", 1]]',
null);