Adding location-based search to URM (or UCM) using Google Maps

Recently I was working with an organisation that manages a large collection of physical assets and wanted to add a location-based search capability to Oracle Universal Records Management. In order to carry out a ‘proof of concept’ we built some simple integration with Google Maps.

This same simple add-on could equally be done with Oracle Universal Content Management.  The only difference would be in the way the metadata are defined and accessed in the query.  If you are using UCM, not URM, the tables will be different.  If you don’t know how to work it out, add a question to the post.

This post was built using Oracle Universal Records Management 11.1.1.2 running on Windows Server 2008 DataCenter Edition with Oracle Database 10g Express Edition.

The integration is built as a Java ServerPages (JSP) add-on.  URM has a built-in JSP server, but we have to enable it.

Connect to the URM web interface at http://yourserver:16300 and click on the Login link.

Log on as an administration, such as weblogic.

Open the Administration menu and select the link for Admin Server.  Note that your menus may look different based on user preferences, e.g. they may be drop-down menus across the top of the screen, or a different colour scheme.

A new window will open for the Admin Server.  Select the General Configuration link as highlighted in the image below.

Tick the checkbox to Enable Java Server Page (JSP) support, as shown, and provide a name for the JSP Enabled Groups.  I called mine “JSP.”  Note that this needs to match the group that we will create a little later on, and also becomes part of the URL to access the JSP pages.

Save these changes, and then restart your URM server instance.

These changes tell the URM server that when we access a JSP file in the group called JSP, it should run that page on its internal JSP server and return us the output, rather than just sending us the text in the file itself.  This is a very quick and easy way to add some new functionality to URM (or UCM for that matter.)

Once the server is restarted, return to the web interface, and open the Admin Applets in the Administration menu.  Then open the User Admin applet from that page.

Note that the admin applets will require a Java enabled browser as they are Java applets that will load and run on your local machine.

When the User Admin applet opens, select Permissions by Group… from the Security menu.

This will open a list of groups.  Click on the Add Group… button.  In the popup dialog box, enter JSP as the name for your new group.  Note that this MUST match the name you specified earlier in the Admin Server General Configuration page.  Then click on OK.

Click on the Edit Permissions… button and set the permissions for admin to RWDA as shown below.

Close the User Admin applet, saving your changes.

This completes the configuration to allow us to run JSP pages.  Now we want to define some metadata to store our location information.  In this post, we will use two fields to hold the latitude and longitude where the physical item was found.

To create our metadata, we go to the Records menu and select Configure then Metadata then Metadata Sets as shown in the image below.

On the Auxiliary metadata set information page, we provide a name and other details and the add the fields we want to define.  Complete this page using the information below:

Name:          gaMetadata
Display Name:  Location Metadata
Table Name:    gametadata
Column Prefix: ga

Add fields with the following names and types:

SurveyNumber                 Text
SurveyName                   Text
SourceLocationLatitude       Text
SourceLocationLongitude      Text
SampleType                   Text
SampleOwner                  Text
EmbargoDate                  Date
CollectionDate               Date

We will use these fields later to construct the search and display information about the search results on the map.

Now we are ready to add our actual JSP search page.  As this activity was conducted as a ‘proof of concept’ this JSP does not have all the error handling that you would want in a real production system, and it does not use connection pooling, etc.  So please be aware that you should improve the code quality if you plan to use this!

Here is the source code for the JSP.  You can put this into a file, I called mine mark.jsp, that we will upload shortly.  I wont give a line by line description of this code (unless somebody asks for it) but at a high level it is a simple HTML form that posts back to itself.  If the form has data in it, the page will use JDBC to connect to the database and run a query to find items within a specified distance (uncomment this if you want it) of a given location.  It then loops through the result set and creates markers on a Google Map to display the search results, along with some hover text and a popup box.  The marker also allows you to click on the search result to go to it’s details page in the web interface.  Note that you will need to set the database URL, userid, password, and your server address correctly.  These are highlighted in bold in the code below.

Note also that the code uses the SDO_GEOM package to do the spatial calculations – in this case the distance between two points in kilometres.  These are written to work on a standard projection with the normal everyday latitude and longitude coordinate system that most people are familiar with.  The Oracle database supports many other coordinate systems, measurement scales and spatial routines.

<%@page language="java" import="java.sql.*"%>
<%
  System.out.println("+++ Entering mark.jsp");
  java.sql.Connection conn;
  java.sql.Statement stmt;
  java.sql.ResultSet rs;
  if (request.getParameter("xLat") != null) {
    try {
      // query the database
      Class.forName("oracle.jdbc.driver.OracleDriver");
      conn = DriverManager.getConnection(
        "jdbc:oracle:thin:@localhost:1521:ecm11g", "dev_urmserver", "welcome1");
      String query = "select g.did, sdo_geom.sdo_distance( SDO_GEOMETRY( 2001, "
        + "8307, SDO_POINT_TYPE(" + request.getParameter("xLong") + ", " + request.getParameter("xLat")
        + ", NULL), NULL, NULL), SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(gacol_4, gacol_3, "
        + "NULL), NULL, NULL), 0.001, 'UNIT=KM') distance_in_km, gacol_4, gacol_3, gacol_1, gacol_2, "
        + "gacol_5, gacol_6, gacol_7, gacol_8, x.ddocname from gametadata g, extitems x ";
        //+ "where g.did = x.did and distance_in_km < " + request.getParameter("xDist");
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
%>
<head>
<title>test</title>
<link rel="stylesheet" type="text/css" href="/urm/resources/yui/reset-fonts-grids/reset-fonts-grids.css" />
<link rel="stylesheet" type="text/css" href="/urm/resources/yui/container/assets/container.css" />
<link rel="stylesheet" type="text/css" href="/urm/resources/yui/treeview/assets/treeview-core.css" />
<link rel="stylesheet" type="text/css" href="/urm/resources/yui/treeview/assets/treeview-skin.css" />
<link rel="stylesheet" type="text/css" href="/urm/resources/yui/treeview/assets/skins/sam/treeview-skin.css" />
<link rel="stylesheet" type="text/css" href="/urm/resources/yui/menu/assets/menu-core.css" />
<link href="/urm/resources/layouts/Trays/Oracle/skin.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=false"></script>
<script type="text/javascript">
function initialize() {
  var latlng = new google.maps.LatLng(<%
  if (request.getParameter("xLat") != null) {
%><%= request.getParameter("xLat") %><%
  } else {
%>-35.282019<%
  }
%>,<%
  if (request.getParameter("xLong") != null) {
%><%= request.getParameter("xLong") %><%
  } else {
%>149.128748<%
  }
%>);
  var myOptions = {
    zoom: 12,
    center: latlng,
    mapTypeId: google.maps.MapTypeId.ROADMAP
  };
  var map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
<%
    int i = 1;
    while (rs.next()) {
%>
    var latlng<%= i %> = new google.maps.LatLng(<%= rs.getString(4) %>,<%= rs.getString(3) %>);
    var marker<%= i %> = new google.maps.Marker({
      position: latlng<%= i %>,
      map: map,
      title: "<%= rs.getString(11) %> from Survey <%= rs.getString(5) %>, <%= rs.getString(6) %>. A <%= rs.getString(7) %> submitted by <%= rs.getString(8) %>"
    });
    var contentString<%= i %> = '<div id="content">' +
      '</div>' +
      '<h1 id="firstHeading"><%= rs.getString(11) %></h1>' +
      '<div id="bodyContent">' +
      '<p><%= rs.getString(11) %> from Survey <%= rs.getString(5) %>, <%= rs.getString(6) %>.<br>A <%= rs.getString(7) %> submitted by <%= rs.getString(8) %>. </p>' +
      '<p><a href="http://yourserver:16300/urm/idcplg?IdcService=INFO_EXTERNAL_ITEM_FORM&dSource=Physical&dID=<%= rs.getString(1) %>">View details</a></p></div></div>';
    var infowindow<%= i %> = new google.maps.InfoWindow({
      content: contentString<%= i %>
    });
    google.maps.event.addListener(marker<%= i %>, 'click', function() {
      infowindow<%= i %>.open(map,marker<%= i %>);
    });
<%
    i++;
  }
%>
}
</script>
</head>
<body onload="initialize()" onunload="GUnload()">
<div >
<h1>Search by Location</h1>
<form method="POST" action="#">
Latitude: <input type="text" name="xLat" value="<%
if (request.getParameter("xLat") != null) {
%><%= request.getParameter("xLat") %><%
} else {
%>-35.282019<%
}
%>"><br/>
Longitude: <input type="text" name="xLong" value="<%
if (request.getParameter("xLong") != null) {
%><%= request.getParameter("xLong") %><%
} else {
%>149.128748<%
}
%>"<br/>
Distance: <input type="text" name="xDist" value="<%
if (request.getParameter("xDist") != null) {
%><%= request.getParameter("xDist") %><%
}
%>"<br/>
<input type="submit" value="Search">
</form>
<p/>
</div>
<div id="map_canvas" style="width: 800px; height: 450px"></div>
</body>
<%
      rs.close();
      stmt.close();
      conn.close();
    } catch (Exception e) {
%><%= e.toString() %><%
    }
  } else {
%>
<head>
<title>test</title>
<link rel="stylesheet" type="text/css" href="/urm/resources/yui/reset-fonts-grids/reset-fonts-grids.css" />
<link rel="stylesheet" type="text/css" href="/urm/resources/yui/container/assets/container.css" />
<link rel="stylesheet" type="text/css" href="/urm/resources/yui/treeview/assets/treeview-core.css" />
<link rel="stylesheet" type="text/css" href="/urm/resources/yui/treeview/assets/treeview-skin.css" />
<link rel="stylesheet" type="text/css" href="/urm/resources/yui/treeview/assets/skins/sam/treeview-skin.css" />
<link rel="stylesheet" type="text/css" href="/urm/resources/yui/menu/assets/menu-core.css" />
<link href="/urm/resources/layouts/Trays/Oracle/skin.css" rel="stylesheet" type="text/css" />
<body onload="initialize()" onunload="GUnload()">
<div >
<h1>Search by Location</h1>
<form method="POST" action="#">
Latitude: <input type="text" name="xLat" value="-35.282019"><br/>
Longitude: <input type="text" name="xLong" value="149.128748"<br/>
Distance: <input type="text" name="xDist" value="50"><br/>
<input type="submit" value="Search">
</form>
<p/>
</div>
<div id="map_canvas" style="width: 800px; height: 450px"></div>
</body>
<%
}
%>

Once you have the code saved in a file, you are ready to check that file into the URM server.

From the New Check-In menu, select your URM instance and then Standard Check In.  A form similar to the one below will open.  Set the fields as shown below:

Type:            Document - Any generic document
Title:           mark
Author:          weblogic
Security Group:  JSP
Primary File:    (click on Choose File and point to your mark.jsp)
Content ID:      MARK
Revision:        1

When you have completed the fields, click on the Check In button.  Note that the Security Group MUST match the one we defined earlier, which in turn matched the one we set in the Admin Server General Configuration page.  You can use a different title or content ID if you wish, however you will need to adjust your URL (coming soon) to match the values you choose.

Now we will set up a quick link to our page so it is easy to access it from the URM web interface.  In the My Content Server menu, click on the My URLs link.  This will open a page where you can define links, as shown below.  Add a new link.  I gave mine the title Search by Location and the URL shown below.  Note that you will need to change the URL (where highlighted in bold) if you have used a different filename or security group.  You will also need to put your server name into the URL.

http://yourserver:16300/urm/groups/jsp/documents/document/mark.jsp

Press Update once you are done.  You will notice that a Search by Location link appears under the My URLs folder in your menu now.

Finally, we will need some actual physical assets in the system so that we have some search results to look at!  The image below shows two sample records I used.

Check some physical assets into your system, if you have not already.  Make sure you add the Location Metadata Auxiliary Metadata Set to your new record using the drop down box at the top right.  You might want to do this before you start filling out the other fields…

Provide some values as shown above.  Note that this item has its SurveyName, SourceLocationLatitude, SourceLocationLongitude and SampleType set.  Note the values, you will see them later in the search results.

Now we are all set to run our add-in.  Click on the Search by Location link under My URLs in the My Content Server menu.  You should see your JSP page appear in the main content pane on the right, as shown below.  Enter some search terms and press the Search button.  Here I searched for -33.8122, 151.1755, the location of a large tunnel near Oracle’s office in Sydney, Australia.

The search results will be displayed on the map, as shown in the digram below.  Note that you can hover over the markers to see details.  You can also click on the marker to make the balloon appear (as has been done below).  The View details link in the balloon will take you to the subject item’s page in the URM web interface.  Note that you can also pan and zoon the map, and select the mode (Map, Satellite, Hybrid).

That concludes the example!  Happy searching by location.

About Mark Nelson

Mark Nelson is an Architect ("IC6") in the Platform Architecture Team in Oracle Development. Mark's focus area is continuous delivery, configuration management and provisioning - making it simple to manage the configuration of complex environments and applications built with Oracle Database, Fusion Middleware and Fusion Applications, on-premise and in the cloud. Before joining the Platform Architecture team, Mark was a senior member of the A-Team since 2010, and worked in Sales Consulting at Oracle since 2006 and various roles at IBM since 1994.
This entry was posted in Uncategorized and tagged , , , , . Bookmark the permalink.

2 Responses to Adding location-based search to URM (or UCM) using Google Maps

  1. jerrycashman says:

    Interesting post Mark… you mentioned that something similar might be done with UCM… any significant changes for the 11G release of UCM? I understood that it provided significantly tighter WLS and ADF integration… ?

    • markxnelson says:

      Hey Jerry, Yeah UCM 11g has much tighter integration with WebLogic. You could do this kind of thing with UCM too (10g or 11g) – the main difference is the way metadata are defined. In UCM you use an admin applet to do it, not the web interface, and it stores it in different tables, so the query would be different. DOCUMENTS is the key table, not EXTITEMS.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s