ESRI map with Google map and map viewer in APEX

This article is in continuation of my previous work on Oracle Map Viewer/Map Builder and Oracle 12c database.

This article demonstrates the conversion of ESRI maps to datatype (SDO_GEOMETRY) that can then be used in Map Viewer. The article ends with an HTML code that can be put in APEX's HTML region to display the map with ESRI info on it.

Let’s start

1.    Get an ESRI map. I got it from http://www.mapmakerdata.co.uk.s3-website-eu-west-1.amazonaws.com/library/stacks/Africa/Africa_SHP.zip. Unzip this file to get the shape file.

2.    Get ojdbc6.jar, sdoutl.jar and sdoapi.jar. I got ojdbc6.jar from <Oracle12cHome>/jdbc/lib and sdoutl.jar and sdoapi.jar from <Oracle12cHome>/md/jlib.

3.    Use the following command to know about the possible argument options.
java -cp <path_of_ojdbc6.jar>\ojdbc6.jar;<path_of_sdoutl.jar>\sdoutl.jar;<path_of_sdoapi.jar>\sdoapi.jar oracle.spatial.util.SampleShapefileToJGeomFeature

Use the following command to do the necessary transformation. Note that you should run this command from the directory that holds your Africa shape file which was downloaded in step 1.

java -cp <path_of_ojdbc6.jar>\ojdbc6.jar;<path_of_sdoutl.jar>\sdoutl.jar;<path_of_sdoapi.jar>\sdoapi.jar oracle.spatial.util.SampleShapefileToJGeomFeature -h <12cdb_host_name> -p <12cdb_port_no> -sn spatial_db -u spatial_db_poc -d spatial_db_poc -t esri_africa_table -f Africa  -r 8307 -g sdo_geom_column

You will get the following message once the conversion is complete

762 record(s) converted.

Done.

In the above java command, spatial_db is my pdb service and spatial_db_poc is my pdb user. Know the process of creation of a pdb and its user from http://obiee-oracledb.blogspot.in/2013/09/my-take-on-using-mapviewer-with-oracle.html

According to the above command, the results of the transformation will be stored in esri_africa_table and sdo_geom_column of esri_africa_table will hold the geometry information. 8307 is my srid here and Africa is the name of my ESRI map that I had downloaded from http://www.mapmakerdata.co.uk.s3-website-eu-west-1.amazonaws.com/library/stacks/Africa/Africa_SHP.zip. The following link can give you the entire Africa library http://www.mapmakerdata.co.uk.s3-website-eu-west-1.amazonaws.com/library/stacks/Africa/index.htm

The above java command also inserts metadata which can be queried from user_sdo_geom_metadata.

4.    Create spatial index

create index esri_africa_spatial_index on esri_africa_table (sdo_geom_column) indextype is mdsys.spatial_index;

5.    Once the data is inserted, we can follow the process described in http://obiee-oracledb.blogspot.in/2013/09/my-take-on-using-mapviewer-with-oracle.html to create a Line style (mine is L.BORDER_LINE) then create a geometry theme (mine is ESRI_AFRICA_THEME) that uses this line style as feature rendering style. Feature rendering style is defined in step 2 of defining a geometry theme in map builder. We will use this theme as a FOI (Features Of Interest) layer. 
Have a look at http://obiee-oracledb.blogspot.in/2013/09/my-take-on-using-mapviewer-with-oracle.html to see the process of using Google maps as map tile layer in Oracle map viewer and creating themes in Oracle map builder.

The definition of my L.BORDER_LINE line style is

<?xml version="1.0" standalone="yes"?>

<svg width="1in" height="1in">

  <desc/>

  <g class="line" style="fill:#6633FF;stroke-width:1">

    <line class="base"/>

  </g>

</svg>

The definition of my ESRI_AFRICA_THEME geometry theme built on top of esri_africa_table is

<?xml version="1.0" standalone="yes"?>

<styling_rules>

    <rule>

        <features style="L.BORDER_LINE"> </features>

  </rule>

</styling_rules>

If you want to use ESRI info in OBIEE then stop right here. The data is there in the database and you can now use OBIEE 11G map views to present this info to the user. It is however also possible to put the below HTML+JavaScript in a narrative view to get the same result.
If you are using any other technology then continue further.

 6.    Once the theme is created, we can use the following code to overlay the theme on top of the base map

<html>

<head>

<META http-equiv="Content-Type" content="text/html" charset=UTF-8">

<TITLE>ESRI map with Google map and 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.ESRI_AFRICA_THEME');

foi.setBringToTopOnMouseOver(true);

foi.setRenderingStyle('L.BORDER_LINE');

foi.setVisible(true) ;

mapview.addThemeBasedFOI(foi);

mapview.setCenter(MVSdoGeometry.createPoint(0,0,3785));

mapview.setZoomLevel(0);

mapview.display();

}

</script>

</head>

<body onload= javascript:load_map() >

<h2>ESRI map with Google map and viewer</h2>

<div id="map" style="width: 600px; height: 500px"></div>

</body>

</html>

The result of overlaying this layer is as follows. Have a look at http://www.mapmakerdata.co.uk.s3-website-eu-west-1.amazonaws.com/library/stacks/Africa/index.htm to get a feel of the ESRI data. ESRI data was basically a collection of the borders of African nations. We have put this border on top of Google map using map viewer and have also managed to change certain properties such as the color of the border. We changed the color of the border in the definition of L.BORDER_LINE