4.4 Transforming Data to a Spherical Mercator Coordinate System

Popular online map services such as Google Maps use a spherical Mercator projection for their maps.

If you are using an Oracle Database release earlier than 11.1.0.7, and if you need to overlay your own spatial data on top of such a tile layer, such as a Google Maps tile layer, you must set up the database to properly handle coordinate system transformation between the coordinate system of that tile layer and your own data coordinate system, if the two coordinate systems are not the same.

Note:

To perform the actions in this section, your database must be Release 10.2.0.1 or later.

Google Maps uses a Spherical Mercator coordinate system (EPSG: 3785), which is also widely used among commercial API providers such as Yahoo! Maps. This coordinate system (SRID 3785) was not provided with Oracle Spatial before Release 11.1.0.7. In order to enable the map visualization componentthe map visualization component andOracle Spatial to transform your own data to this coordinate system, you must first add this coordinate system definition into your Oracle database if it is not already defined.

To check if this coordinate system is defined, you can enter the following statement:

SELECT srid FROM mdsys.cs_srs WHERE srid=3785;

If the preceding statement returns a row, you do not need to perform the actions in this section. If the preceding statement does not return a row, you must perform the actions in this section in order to be able to overlay your own spatial data on top of the tile layer.

Follow these steps:

1. Connect to the database as a privileged user, such as one with the DBA role.

2. Run the csdefinition.sql script, as follows. (Replace $MAPVIEWER_HOME with the root directory of the WebLogic Server instance where your MapViewer is deployed, and enter the command on a single line.)

   • Linux: $MAPVIEWER_HOME/j2ee/home/applications/mapviewer/web/WEB-INF/admin/csdefinition.sql

   • Windows: $MAPVIEWER_HOME\j2ee\home\applications\mapviewer\web\WEB-INF\admin\csdefinition.sql

3. If necessary, create a transformation rule to cause Oracle Spatial to skip datum conversion when transforming data from a specified coordinate system to the Spherical Mercator system. To find out if you need to create such a transformation rule, see Creating a Transformation Rule to Skip Datum Conversion.

4. Either pre-transform your spatial data for better performance, or let the map visualization component transform the data at runtime ("on the fly"). Note that if your database release is earlier than 10.2.0.4, pre-transforming is the only option.

   • To pre-transform all your data into the Spherical Mercator coordinate system, use the SDO_CS.TRANSFORM_LAYER procedure on all the data, and use the transformed data for mapping. (See the SDO_CS.TRANSFORM_LAYER reference section in Oracle Spatial Developer's Guide.)

   • To let the map visualization component transform the data at runtime, do not transform the data before using it for mapping.

• Creating a Transformation Rule to Skip Datum Conversion
  

4.4.1 Creating a Transformation Rule to Skip Datum Conversion

Spatial data is often in a coordinate system based on an ellipsoid datum, such as WGS84 or BNG. In such cases, Oracle Spatial by default applies datum conversion when transforming the data into the Spherical Mercator system. This will introduce a small amount of mismatch or error between your data and the Google Maps other map service tiles. If you want to address this issue, you can create transformation rules that tell Oracle Spatial to skip datum conversion when transforming data from a specified coordinate system to the Spherical Mercator system.

Example 4-1 shows SQL statements that are included in the csdefinition.sql script and that create such transformations rules. However, if the coordinate system of your spatial data is not covered by the rules shown in Example 4-1, you can create your own rule if the coordinate system of your data is not covered by these rules. (For more information about creating coordinate system transformation rules, see Oracle Spatial Developer's Guide.)

Example 4-1 Transformation Rules Defined in the csdefinition.sql Script

-- Create the tfm_plans, that is, the transformation rules.
-- Note: This will result in an incorrect conversion since it ignores a datum
-- datum between the ellipsoid and the sphere. However, the data will match
-- up better on Google Maps.
 
-- For wgs84 (8307)
call sdo_cs.create_pref_concatenated_op( 83073785, 'CONCATENATED OPERATION 8307 3785', TFM_PLAN(SDO_TFM_CHAIN(8307, 1000000000, 4055, 19847, 3785)), NULL);
 
-- For 4326, EPSG equivalent of 8307
call sdo_cs.create_pref_concatenated_op( 43263785, 'CONCATENATED_OPERATION_4326_3785', TFM_PLAN(SDO_TFM_CHAIN(4326, 1000000000, 4055, 19847, 3785)), NULL); 
 
-- For OS BNG, Oracle SRID 81989
call sdo_cs.create_pref_concatenated_op( 819893785, 'CONCATENATED OPERATION 81989 3785', TFM_PLAN(SDO_TFM_CHAIN(81989, -19916, 2000021, 1000000000, 4055, 19847, 3785)), NULL); 
 
-- For 27700, EPSG equivalent of 81989
call sdo_cs.create_pref_concatenated_op( 277003785, 'CONCATENATED_OPERATION_27700_3785', TFM_PLAN(SDO_TFM_CHAIN(27700, -19916, 4277, 1000000000, 4055, 19847, 3785)), NULL);
commit;