Displaying MapViewer tile layers over Google Maps

A lot of folks have asked how to display a MapViewer tile layer in a Google Maps application. My colleague Jayant Sharma recently wrote a very nice note on this subject, using Google Maps' custom tile layer support. The following are the detailed instructions form his note. They can also be used with other map services such as Microsoft Virtual Earth.

Overview

There are 3 high-level steps involved:

1. Preparing your Oracle Spatial data by creating the EPSG:3785 coordinate system and transforming the data.
2. Creating a MapViewer tile layer for your data.
3. Displaying the MapViewer tile layer over Google Maps using its custom tile layer mechanism.
   

Adding the EPSG:3785 coordinate system definition

The Google Maps are projected and rendered in a coordinate system known as Spherical Mercator (EPSG id 3785). So the data stored in an Oracle database and rendered by MapViewer into map tiles must also be in that coordinate system. This section describes the steps to add this new EPSG coordinate system definition into an Oracle database.

Note: This requires a 10.2.0.4 database. Earlier versions do not have the required patch that enables transformations (SDO_CS.TRANSFORM) between this (3785) and other (e.g. WGS84 or BNG) coordinate systems.

Note 2: You may want to verify that the SRID 3785 does NOT already exist in the database. To verify, you can run the SQL query "select srid from mdsys.cs_srs where srid=3785" from any database user. If a row is returned, then you already have this SRID, and you should SKIP this section. With the latest 11g Oracle database this SRID does indeed exist out of the box.

Connect to your 10.2.0.4 database instance (with Spatial or Locator installed and enabled) as a privileged user (e.g. a user with the DBA role) and issue the following SQL statements.

insert into MDSYS.SDO_ELLIPSOIDS (

ELLIPSOID_ID,

ELLIPSOID_NAME,

SEMI_MAJOR_AXIS,

UOM_ID,

INV_FLATTENING,

SEMI_MINOR_AXIS,

INFORMATION_SOURCE,

DATA_SOURCE,

IS_LEGACY,

LEGACY_CODE)

VALUES (

7059,

'Popular Visualisation Sphere',

6378137,

9001,

1.0000E+12,

NULL,

null,

'EPSG',

'FALSE',

null);

insert into MDSYS.SDO_DATUMS (

DATUM_ID,

DATUM_NAME,

DATUM_TYPE,

ELLIPSOID_ID,

PRIME_MERIDIAN_ID,

INFORMATION_SOURCE,

DATA_SOURCE,

SHIFT_X,

SHIFT_Y,

SHIFT_Z,

ROTATE_X,

ROTATE_Y,

ROTATE_Z,

SCALE_ADJUST,

IS_LEGACY,

LEGACY_CODE)

VALUES (

6055,

'Popular Visualisation Datum',

'GEODETIC',

7059,

8901,

null,

'EPSG',

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

'FALSE',

NULL);

insert into MDSYS.SDO_COORD_REF_SYSTEM (

SRID,

COORD_REF_SYS_NAME,

COORD_REF_SYS_KIND,

COORD_SYS_ID,

DATUM_ID,

geog_crs_datum_id,

SOURCE_GEOG_SRID,

PROJECTION_CONV_ID,

CMPD_HORIZ_SRID,

CMPD_VERT_SRID,

INFORMATION_SOURCE,

DATA_SOURCE,

IS_LEGACY,

LEGACY_CODE,

LEGACY_WKTEXT,

LEGACY_CS_BOUNDS,

is_valid,

supports_sdo_geometry)

VALUES (

4055,

'Popular Visualisation CRS',

'GEOGRAPHIC2D',

6422,

6055,

6055,

NULL,

NULL,

NULL,

NULL,

null,

'EPSG',

'FALSE',

NULL,

NULL,

NULL,

'TRUE',

'TRUE');

INSERT INTO sdo_coord_ops (

coord_op_id,

coord_op_name,

coord_op_type,

source_srid,

target_srid,

coord_tfm_version,

coord_op_variant,

coord_op_method_id,

UOM_ID_SOURCE_OFFSETS,

UOM_ID_TARGET_OFFSETS,

information_source,

data_source,

show_operation,

is_legacy,

legacy_code,

reverse_op,

is_implemented_forward,

is_implemented_reverse)

VALUES (

19847,

'Popular Visualisation Mercator',

'CONVERSION',

null,

null,

'',

null,

9804,

null,

null,

null,

null,

1,

'FALSE',

null,

1,

1,

1);

insert into MDSYS.SDO_COORD_OP_PARAM_VALS (

COORD_OP_ID,

COORD_OP_METHOD_ID,

PARAMETER_ID,

PARAMETER_VALUE,

PARAM_VALUE_FILE_REF,

UOM_ID)

VALUES (

19847,

9804,

8801, -- Latitude of natural origin

0,

NULL,

9102);

insert into MDSYS.SDO_COORD_OP_PARAM_VALS (

COORD_OP_ID,

COORD_OP_METHOD_ID,

PARAMETER_ID,

PARAMETER_VALUE,

PARAM_VALUE_FILE_REF,

UOM_ID)

VALUES (

19847,

9804,

8802, -- longitude of natural origin

0,

NULL,

9102);

insert into MDSYS.SDO_COORD_OP_PARAM_VALS (

COORD_OP_ID,

COORD_OP_METHOD_ID,

PARAMETER_ID,

PARAMETER_VALUE,

PARAM_VALUE_FILE_REF,

UOM_ID)

VALUES (

19847,

9804,

8805, -- scale factor at natural origin

1,

NULL,

9201);

insert into MDSYS.SDO_COORD_OP_PARAM_VALS (

COORD_OP_ID,

COORD_OP_METHOD_ID,

PARAMETER_ID,

PARAMETER_VALUE,

PARAM_VALUE_FILE_REF,

UOM_ID)

VALUES (

19847,

9804,

8806, -- false easting

0,

NULL,

9001);

insert into MDSYS.SDO_COORD_OP_PARAM_VALS (

COORD_OP_ID,

COORD_OP_METHOD_ID,

PARAMETER_ID,

PARAMETER_VALUE,

PARAM_VALUE_FILE_REF,

UOM_ID)

VALUES (

19847,

9804,

8807, -- false northing

0,

NULL,

9001);

INSERT INTO sdo_coord_ref_system (

srid,

coord_ref_sys_name,

coord_ref_sys_kind,

coord_sys_id,

datum_id,

geog_crs_datum_id,

source_geog_srid,

projection_conv_id,

cmpd_horiz_srid,

cmpd_vert_srid,

information_source,

data_source,

is_legacy,

legacy_code,

legacy_wktext,

legacy_cs_bounds,

is_valid,

supports_sdo_geometry)

VALUES (

3785,

'Popular Visualisation CRS / Mercator',

'PROJECTED',

4499,

null,

6055,

4055,

19847,

Null,

Null,

null,

null,

'FALSE',

null,

null,

null,

'TRUE',

'TRUE');

-- create the tfm_plans, i.e. transformation rule.

-- Note: This will result in an incorrect conversion since it ignores a datum shift

-- between the ellipsoid and the sphere. However the data will match up better

-- on google maps

-- first 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);

-- 4326 is the 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);

-- similarly for os bng (oracle srid 81989 or epsg 27700 it is

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);

-- 27700 is the 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;

Next transform existing data, or a copy of it, into 3785 and use it for generating the tiles. Otherwise the data will be transformed on the fly and that’ll slow down tile generation. The next section will use the MVDEMO sample data set to illustrate the steps.

Setting up a Oracle map tile layer for Google Maps

The MVDEMO sample dataset has tables named counties, cities, interstates, and states and the data is in WGS84 (srid = 8307). There’s also a set of styles, themes, basemaps, and map tile caches defined. These definitions are stored in the USER_SDO_STYLES, SUER_SDO_THEMES, USER_SDO_MAPS, and USER_SDO_CACHED_MAPS views.

The first step is to create a set of tables with the data transformed to 3785.

create table states_3785 as select * from states;

create table interstates_3785 as select * from interstates;

create table cities_3785 as select * from cities;

create table counties_3785 as select * from counties;

update states_3785 set geom = sdo_cs.transform(geom, 3785);

update interstates_3785 set geom = sdo_cs.transform(geom, 3785);

update cities_3785 set location = sdo_cs.transform(location, 3785);

update counties_3785 set geom = sdo_cs.transform(geom, 3785);

commit;

-- insert metadata

insert into user_sdo_geom_metadata values('STATES_3785', 'GEOM',

sdo_dim_array(sdo_dim_element('X', -20037508.3427, 20037508.3427, 0.05), sdo_dim_element('Y', -20037508.3427, 20037508.3427, 0.05)), 3785);

insert into user_sdo_geom_metadata values('INTERSTATES_3785', 'GEOM',

sdo_dim_array(sdo_dim_element('X', -20037508.3427, 20037508.3427, 0.05), sdo_dim_element('Y', -20037508.3427, 20037508.3427, 0.05)), 3785);

insert into user_sdo_geom_metadata values('COUNTIES_3785', 'GEOM',

sdo_dim_array(sdo_dim_element('X', -20037508.3427, 20037508.3427, 0.05), sdo_dim_element('Y', -20037508.3427, 20037508.3427, 0.05)), 3785);

insert into user_sdo_geom_metadata values('CITIES_3785', 'LOCATION',

sdo_dim_array(sdo_dim_element('X', -20037508.3427, 20037508.3427, 0.05), sdo_dim_element('Y', -20037508.3427, 20037508.3427, 0.05)), 3785);

-- create spatial indexes

create index states_3785_sidx on states_3785(geom) indextype is mdsys.spatial_index;

create index interstates_3785_sidx on interstates_3785(geom)

indextype is mdsys.spatial_index;

create index counties_3785_sidx on counties_3785(geom)

indextype is mdsys.spatial_index;

create index cities_3785_sidx on cities_3785(location)

indextype is mdsys.spatial_index parameters(‘layer_gtype=point’);

The second step is to create the themes for these tables and after that a basemap made of these newly created themes. This can be done with MapBuilder or using SQL statements to copy the theme definitions while changing the referenced table names by appending a _3785 to the theme and table name.

insert into user_sdo_themes select name || ‘_3785’, description,

base_table || ‘_3785’, geometry_column, styling_rules

from user_sdo_themes where base_table in (‘STATES’, ‘INTERSTATES’,

‘COUNTIES’, ‘CITIES’) and length(name) < 27

commit;

-- if a theme name is > 27 chars long modify it individually

-- It’ll has to be manually modified in the basemap definition

-- too. E.g there may be one name new_theme_demo_highways_line

-- that can be named theme_demo_highways_line_3785 instead

Next define a basemap using these themes. You can use the following SQL statements to manually create a new basemap named DEMO_MAP_3785 that is similar to DEMO_MAP, then update its referenced theme names and map scales:

insert into user_sdo_maps select ‘DEMO_MAP_3785’, description,definition

from user_sdo_maps where name=’DEMO_MAP’;

update user_sdo_maps set definition=
'<?xml version="1.0" standalone="yes"?>
<map_definition>
<theme name="THEME_DEMO_STATES_3785" />
<theme name="THEME_DEMO_COUNTIES_3785" min_scale="3000000.0" max_scale="0.0" scale_mode="RATIO"/>
<theme name="THEME_DEMO_STATES_LINE_3785" min_scale="3000000.0" max_scale="0.0" scale_mode="RATIO"/>
<theme name="THEME_DEMO_HIGHWAYS_3785"/>
<theme name="THEME_DEMO_CITIES_3785" min_scale="6000000.0" max_scale="0.0" scale_mode="RATIO"/>
<theme name="THEME_DEMO_BIGCITIES_3785"/>
</map_definition>'
where name='DEMO_MAP_3785';
commit;

The third step is to create a map tile layer. Navigate to MapViewer’s Admin console (i.e. go to http://localhost:8888/mapviewer/ , click on the Admin button, log in as a oc4jadmin) and then on the Manage Map Tile Layers tab. Then on Create link in the left panel and then the Continue button. Name the map tile layer, e.g. demo_map_3785, choose mvdemo as the data source and demo_map_3785 as the basemap. Check the transparent checkbox next to the Background color selection box. The default color will be ignored and the map tiles will be rendered on a transparent background. Then set # Zoom Levels to 20 (the number used by Google Maps), ignore the scale level settings, SRID to 3785, Min X to -20037508.3427, Max X to 20037508.3427, Min Y to -20037508.3427, and Max Y to 20037508.3427. Leave the tile size as 256 by 256 and format as PNG. Do NOT click submit. Click XML Mode instead.

To workaround a bug in the UI, which does not let you enter specific tile sizes, we will manually enter this information in XML mode. So click on XML mode and copy and paste the following XML fragment over the existing <zoom levels> … </zoom_levels> fragment. (Note: the following values have been revised and are correct as of April 09 2009)

<zoom_levels levels="20" min_scale="0.0" max_scale="0.0" min_tile_width="76.43702697753906" min_tile_height="4.0075016E7">
<zoom_level level="0" name="" description="" scale="0.0" tile_width="4.0075016E7" tile_height="4.0075016"/>
<zoom_level level="1" name="" description="" scale="0.0" tile_width="2.0037508E7" tile_height="2.0037508E7"/>
<zoom_level level="2" name="" description="" scale="0.0" tile_width="1.0018754E7" tile_height="1.0018754E7"/>
<zoom_level level="3" name="" description="" scale="0.0" tile_width="5009377.0" tile_height="5009377.0"/>
<zoom_level level="4" name="" description="" scale="0.0" tile_width="2504688.5" tile_height="2504688.5"/>
<zoom_level level="5" name="" description="" scale="0.0" tile_width="1252344.25" tile_height="1252344.25"/>
<zoom_level level="6" name="" description="" scale="0.0" tile_width="626172.125" tile_height="626172.125"/>
<zoom_level level="7" name="" description="" scale="0.0" tile_width="313086.0625" tile_height="313086.0625"/>
<zoom_level level="8" name="" description="" scale="0.0" tile_width="156543.03125" tile_height="156543.03125"/>
<zoom_level level="9" name="" description="" scale="0.0" tile_width="78271.515625" tile_height="78271.515625"/>
<zoom_level level="10" name="" description="" scale="0.0" tile_width="39135.7578125" tile_height="39135.7578125"/>
<zoom_level level="11" name="" description="" scale="0.0" tile_width="19567.87890625" tile_height="19567.87890625"/>
<zoom_level level="12" name="" description="" scale="0.0" tile_width="9783.939453125" tile_height="9783.939453125"/>
<zoom_level level="13" name="" description="" scale="0.0" tile_width="4891.9697265625" tile_height="4891.9697265625"/>
<zoom_level level="14" name="" description="" scale="0.0" tile_width="2445.98486328125" tile_height="2445.98486328125"/>
<zoom_level level="15" name="" description="" scale="0.0" tile_width="1222.992431640625" tile_height="1222.992431640625"/>
<zoom_level level="16" name="" description="" scale="0.0" tile_width="611.4962158203125" tile_height="611.4962158203125"/>
<zoom_level level="17" name="" description="" scale="0.0" tile_width="305.74810791015625" tile_height="305.74810791015625"/>
<zoom_level level="18" name="" description="" scale="0.0" tile_width="152.87405395507812" tile_height="152.87405395507812"/>
<zoom_level level="19" name="" description="" scale="0.0" tile_width="76.43702697753906" tile_height="76.43702697753906"/>
</zoom_levels>

Now click the Submit button.

Click on the Manage link and you should see the Existing Map Tile Layers list. Select demo_map_3785 and click on View Map / Manage Tiles. Enter –12000000 for Center X, and 5000000 for Center Y and Show Map. Or just click show map and pan to the left. You should see tiles being generated and displayed. Click return. Below is a screenshot of this new tile layer:

The actual display code

The following sample code uses the tile served up by MapViewer in a Google Maps application. You should save it as tileoverlay-simple.html.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:v="urn:schemas-microsoft-com:vml">
<head>
<title>Google Maps JavaScript API Example: Tile Overlays</title>
<script src="http://maps.google.com/maps?file=api&v=2&key=ABQIAAAA9WYjhAcvlemZrWK0oPA3ZBSN0IHl8d7gGlihtYuSKk78QOdoAhRRFH3M5iCXxdFN-KENpX8NdJwm0Q"
type="text/javascript"></script>
<script type="text/javascript">
function initialize() {
if (GBrowserIsCompatible()) {

// Set up the copyright information
// Each image used should indicate its copyright permissions
var myCopyright = new GCopyrightCollection("(c) ");
myCopyright.addCopyright(new GCopyright('Demo',
new GLatLngBounds(new GLatLng(-90,-180),
new GLatLng(90,180)), 0,'?2007 Google'));

// Create the tile layer overlay and
// implement the three abstract methods
var tilelayer = new GTileLayer(myCopyright);

tilelayer.getTileUrl = function(tile, zoom) {
var req=null;
req = "http://stadb32.us.oracle.com:7001/mapviewer/mcserver?" +
"request=gettile&format=PNG&"+
"zoomlevel="+(zoom)+"&mapcache=mvdemo.demo_map_3785&mx="
+(tile.x)+"&my="+(Math.pow(2,zoom)-1-tile.y)+"&";
return req;};

tilelayer.isPng = function() { return true;};
tilelayer.getOpacity = function() { return 0.75; }

var myTileLayer = new GTileLayerOverlay(tilelayer);
var map = new GMap2(document.getElementById("map_canvas"));
map.setCenter(new GLatLng(33.4419, -90.1419), 4);
map.addControl(new GSmallMapControl());
map.addOverlay(myTileLayer);
}
}
</script>
</head>
<body onload="initialize()" onunload="GUnload()">
<div id="map_canvas" style="width: 800px; height: 600px"></div>
</body>
</html>

The relevant (modified) code is the getTileUrl function. Google Maps passes in the tile coordinates for the requested tile and the current zoom level. The parameter “tile” is a GPoint object with “x” and “y” attributes. So tile.x is the x-coordinate for the tile. This is same for Oracle Maps since it also goes from left to right. The y-coordinate value however needs to be recomputed since Google’s Y values increase top to bottom while Oracle’s increase bottom to top. In both cases there are 2^Nx2^N tiles at zoom level N. So the Y value for the Oracle Maps tile will be (2^N –1 – tile.y).

You should modify the Google Maps key as well as the MapViewer server URL in the above code. Save the code as tileoverlay-simple.html, then open it from a browser, and you should see a Google Maps map with the MapViewer tile layer overlay.

Final note, while this article presented how to display MapViewer tiles in a Google Maps mash-up application, the up-coming release of MapViewer (11g R1) will include built-in support for displaying Google Maps and Microsoft Virtual Earth tiles from within your Oracle Maps application. A blog about such support will be posted here as soon as MapViewer 11g R1 is released (which hopefully is just around the corner).