My take on using Mapviewer with Oracle 12c and Google maps API

Map viewer is a kool technology and I wanted to write about it for a long time. 

Let me first chalk the steps that we will follow. We will

1. Download Map Viewer and Map Builder.

2. Create a pluggable database (pdb), create a pdb user and grant the necessary privileges to it.

3. Configure Map viewer.

4. Insert spatial data.

5. Use Map Builder to create styles and themes.

6. Get a key from google and then use it to create a map tile layer using Map Viewer.

7. Write code to create a FOI layer on a base map using Map Viewer's JavaScript API.

Before I begin, I wish to show you the final result of our activity

Let's start

Steps to Download Map Viewer and Map Builder

Get Map Viewer and Map Builder from http://www.oracle.com/technetwork/middleware/mapviewer/downloads/index.html

Steps to Create a pluggable database (pdb), create a pdb user and grant the necessary privileges

The following statements creates a pdb and opens it

CREATE PLUGGABLE DATABASE spatial_db ADMIN USER spatial_db_admin IDENTIFIED BY spatial_db_admin;

Alter pluggable database spatial_db open;

Now connect to the mdsys schema of cdb and execute the following script

mapviewer_11p6_1.zip -> mapviewer.ear ->web.war -> WEB-INF -> admin -> mcsdefinition.sql

Execute the following statements as well.

CREATE or REPLACE type MV_STRINGLIST as TABLE of STRING;

CREATE or REPLACE type MV_NUMBERLIST as TABLE of NUMBER;

CREATE or REPLACE type MV_DATELIST as TABLE of DATE;

Now do the following after connecting as sys

alter session set container=spatial_db;

create user spatial_db_poc identified by spatial_db_poc;

grant create session, create table, create view, create trigger, create synonym, create procedure, create sequence to spatial_db_poc;

alter user spatial_db_poc quota 100M on system;

Steps to Configure Map viewer

Alright, lets install map viewer here. Map viewer is a JEE application that helps us see our maps on a webpage. Oracle documentation is good enough for this purpose of installation. Installation of mapviewer on WebLogic involves unzipping the ear, creating directories and then deploying the exploded directory on WebLogic. Read about the complete process from http://docs.oracle.com/cd/E28280_01/web.1111/e10145/vis_start.htm#BEHFEJEF

Testing the mapviewer can be done using: http://localhost:7001/mapviewer/omserver?getv=t

This will give you the mapviewer version number

The next step is to create a JDBC connection to the datasources that hold your data.

The steps are:

1. Login to http://localhost:7001/mapviewer

2. Click on the Admin link on the top right corner and login using your weblogic credentials.

3. Click on Configuration link

You will find the following section in the text area

<!-- ****************************************************************** -->

  <!-- ******************** Predefined Data Sources  ******************** -->

  <!-- ****************************************************************** -->

4. Add your datasource beneath this. An example is as follows

<map_data_source name="12CSPATIAL"

                   jdbc_host="<db_server_ip>"

                   jdbc_sid="//spatial_db"

                   jdbc_port="<db_server_port>"

                   jdbc_user="spatial_db_poc"

                   jdbc_password="!spatial_db_poc" 

                   jdbc_mode="thin"

                   number_of_mappers="3"

                   allow_jdbc_theme_based_foi="false"

                   editable="false"

   />

Note the // before //spatial_db in jdbc_sid and ! before ="!spatial_db_poc". 

5. Click on Save and Restart mapviewer button. After you restart mapviewer, the value of jdbc_password will get encoded automatically.

Steps to Insert spatial data

We can use either of the following to connect to spatial_db_poc user. 

sqlplus spatial_db_poc/spatial_db_poc@<db_server_ip>:<db_server_port>/spatial_db

conn spatial_db_poc@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <db_server_ip>)(PORT = <db_server_port>)) ) (CONNECT_DATA = (SERVICE_NAME = spatial_db) ) )/spatial_db_poc

We will first create a table with a few cities in it. We will overlay this city information on top of our base Map. We will use Google maps as our base map

CREATE TABLE cities    (

city_id integer, 

city_NAME VARCHAR2(100 BYTE), 

country_name VARCHAR2(100 BYTE),

population integer, 

city_point SDO_GEOMETRY

   )

Insert into cities (city_id,city_name,country_name,population,city_point) values (1, 'PORTLAND', 'USA', 529121,SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-122.67567, 45.51181, NULL), NULL, NULL));

Insert into cities (city_id,city_name,country_name,population,city_point) values (2, 'TIJUANA', 'MEXICO', 1148681,SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-117.01206, 32.52161, NULL), NULL, NULL));

Let us now insert the following metadata of our spatial data

INSERT INTO user_sdo_geom_metadata(table_name,   column_name,   diminfo,   srid)

VALUES('CITIES',   'CITY_POINT',   sdo_dim_array(sdo_dim_element('longitude',   -180,   180,  .0005),   sdo_dim_element('latitude',   -90,   90,  .0005)),   8307);

CREATE INDEX city_spatial_index ON cities (city_point)

INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Index creation is required for the FOI layer to appear on the base map. If this is not done then we will get ORA-13226: interface not supported without a spatial index error in mapviewer log file.

We can use the following query to get more info on our SRID

select * from SDO_COORD_REF_SYS where srid = 8307.

Steps to Use Map Builder to create styles and themes

Map Builder is a jar file that gives us the environment to create styles, themes and maps. Styles, themes and maps created by map builder are basically XML definitions and these definitions are stored in the database. These definitions can be queried from all_sdo_styles, all_sdo_themes and all_sdo_maps views. The definitions of map tiles can be queried from user_sdo_cached_maps. We will talk about map tiles in a while. For now, let’s just say that map tile layer is built on top of map, themes make up a map and styles make up a theme. In this article, instead of creating a map in mapbuilder using themes, we will get the map from google.

Themes can also be used to create FOI (Features of Interest) layer and we will create an FOI layer in this article.

Let us now create our themes and styles.

1. Double click on the jar of Oracle Map Builder and create a JDBC connection to a PDB. My JDBC connection string is

jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST 

= <db_server_ip>)(PORT = <db_listener_port>)) ) (CONNECT_DATA = (SERVICE_NAME = <my_pdb_service_name>) ) )

2. Create a Markers style called M.STAR as shown below

3. Similarly create a Texts style called T.TEXT

4. Create a Geometry theme called CITY_THEME as shown below

Select the marker which you created a little while back. Marker will be displayed on the map.

Change Attribute to CITY_NAME in the below step and select your created text style

My XML of my city_theme is
<?xml version="1.0" standalone="yes"?>
<styling_rules>
    <rule>
        <features style="M.STAR"> </features>
        <label column="CITY_NAME" style="T.TEXT"> 1 </label>
  </rule>
</styling_rules>

Open CITY_THEME now and add a info column by clicking on the pencil icon highlighted below. Note that we can also code links in the info column values. 

Put '<a href="http://www.google.com">'||CITY_NAME||'</a>' in the column text box and give a label to this link in the adjoining text box. Info columns appear when we move our mouse pointer on top of any of the values in the map. '<a href="http://www.google.com">'||CITY_NAME||'</a>' helps us put a link on the values of the info columns. CITY_NAME is one of the columns of the CITIES table on top of which we have made our CITY_THEME. I have put a link to google.com but then you can code any link and can also use the columns of the table that hold spatial data to create a link.

Steps to Get a key from google and then use it to create a map tile layer using Map Viewer

1. Login to https://code.google.com/apis/console using your google id

2. Click on Services and turn on Google Maps API v3 as shown below.

3. Click on API Access and click on Generate new key... to get your key.

Let us now create a Google map tile layer using this key.

1. Login to map viewer and click on Create Tile Layer link

2. Select Google Maps in Select type of map source: dropdown and hit the Continue button.

3. Keep the defaults and enter your google key in the key text box. Here are my values.

lib_url	http://maps.google.com/maps/api/js?sensor=false
key	<my_key>
map_type_values	MVGoogleTileLayer.TYPE_ROAD;MVGoogleTileLayer.TYPE_SHADED;MVGoogleTileLayer.TYPE_SATELLITE

4.Select the datasource which you had defined before. A screenshot of the configuration is shared below.

HTML code to display the map

<html>
<head>
<META http-equiv="Content-Type" content="text/html" charset=UTF-8">
<TITLE> Google map with map viewer </TITLE>
<script language="Javascript" src="jslib/oraclemaps.js"></script>
<script language=javascript>
var foi=null;
function load_map()
{
var baseURL = "http://"+document.location.host+"/mapviewer";

var mapCenterLongitude = 0.0;
var mapCenterLatitude =  0.0;

var mpoint = MVSdoGeometry.createPoint(mapCenterLongitude,mapCenterLatitude,3785);


var mapview = new MVMapView(document.getElementById("map"), baseURL);
mapview.addMapTileLayer(new MVMapTileLayer("12CSPATIAL.GOOGLE_MAP"));


foi = new MVThemeBasedFOI('foi','12CSPATIAL.CITY_THEME');
foi.setBringToTopOnMouseOver(true);
foi.setRenderingStyle('M.STAR');
foi.setVisible(true) ;
mapview.addThemeBasedFOI(foi);
mapview.setCenter(MVSdoGeometry.createPoint(0,0,3785));
mapview.setZoomLevel(0);
//mapview.addNavigationPanel("WEST"); Adds a navigation panel. Since google gives us a navigation panel so we do not need this here but this might need it in some custom maps
//mapview.addScaleBar();
mapview.display();
}
</script>
</head>
<body onload= javascript:load_map() >
<h2>Google map with map viewer</h2>
<div id="map" style="width: 600px; height: 500px"></div>
</body>
</html>

The code is self explanatory. Leave a comment if you want me to talk about any part of this code. This code can be used in HTML region of APEX. It is however important to note that OBIEE has map view. Map view works once map viewer is configured. Map view helps us achieve the same objective without the need to code HTML and JavaScript.We  will see the process to use map view some other time.
We will talk about the newer spatial features of Oracle 12c in my next blog.