Saturday 14 September 2013

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


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
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.


16 comments:

Unknown said...

Hi,
good post but do you have any problem with the cache and the refresh of the maps when you add a city on your table?

Thanks

Simone Morassutto

Vishal Pathak said...

Nopes no such trouble

Regards,
Vishal

Unknown said...

Hi Vishal,
Actually I am using oracle 12.1.0.2.0 while installing I didn't check PDB but it is installed successfully.
Now I am trying to create Map Viewer in 12C. I followed the Map Viewer 11g document. I can able to import all layers into the Administration Manage Map Data from the sample data like "mvdemo","world_sample".
But the "Demo_Map","Customer_Map",Google_Map" is not loaded into the analytics.
Later I search in google I got only your blog for Map Viewer Configuration 12c.
When I following your blog for 2nd step you mentioned
"CREATE PLUGGABLE DATABASE"

When I trying this one I am getting error like "ORA-65090: Operation only allowed in a container database"

"So mandatory to create pdb while installation or can we configure pdb in 11g"

If we can configure in 11g, Please can you provide me a syntax?

I need your input to solve this one.

Thanks in Advance

Vishal Pathak said...

The blog assumes that your db is installed correctly

Antonio said...

Hello,

Is it necessary to configure MapViewer in order to display a Google Maps?

If no MapViewer configured, can I use a narrative view and insert the code there? (assuming I have latitude and longitude columns in my repository)

Thank you

Vishal Pathak said...

Well. JQuery and JavaScript is always an alternative to everything. I haven't tried it out myself for maps.

Antonio said...

Ok, I was thinking the way to user gmaps on OBIEE was by using the Gmaps API coding the javascript.

My question then is: if I configure the MapViewer to use Gmaps as background map, then the native map visualization of OBIEE will work using Gmaps?

And, where should I put the HTML code shown in this article?

Thank you!

Vishal Pathak said...

this mechanism is not related to OBIEE. OBIEE has BI data which has to be overlayed on the map and the tool gives the feature to do it. Google map (or maps from other providers such as navteq) can be used as a base map and BI data can be overlayed on top of it. You will not have to do javascript programming for it.
Read oracle documentation for details on the same.

Unknown said...

Hi Vishal Pathak,

This post was amazing, I have been working on OBIEE since 4 Years and i haven't created any Map Viewer but i dont have any idea about it in your blog you have mentioned that for OBIEE "we will see the process to use map view some other time.
Have you created any thing for that.
Can you Please Let me know about it.

Thanks in Advance.

Regards,
R K

Vishal Pathak said...

Im sorry but this is the only blog I wrote on Map Viewer

Unknown said...

hi vishal

i juste need to khow where I placed the html code ,thanks

Vishal Pathak said...

Depends on what tool you are using.

You can put the HTML code at any place where your tool allows you to put the code.

Tools such as OBIEE have a map view feature and hence you will not have to put explicit HTML in it.

Unknown said...

ok,i use obiee 12c and when i create a google map at mapViewer server,it is created and it is displayed at mapViwer server but in analytics it is didn't displayed,it display error MAPVIEWER-09001 and it say that Map tile layer not found,i didn't undrestand this error

Unknown said...

hi vishal,

i use obiee 12 ,i work with mapviewer server ana analytic server,i need to khonw how i create th FOI Layer using the mapViwer JavaScript Api,,please I am blocked,thanks

Vishal Pathak said...

You dont need HTML code for OBIEE. You will have to follow the steps till map builder though.
Go though oracle documentation to know the process to use map viewer in obiee.

Unknown said...

Custom map creator When your website or blog goes live for the first time, it is exciting. That is until you realize no one but you and your.