OpenSUSE on VirtualBox

openSUSE-logo

After some Memorial Day weekend trial and error I’ve settled on a Linux distribution for my Oracle MapViewer lab. Neither Ubuntu 14.04 nor Fedora 20 performed well under VirtualBox. In the case of Ubuntu it was the lack of 3D support for Unity, which I think can be fixed by rebuilding the VirtualBox Guest Additions from the kernel source, but I wasn’t able get VirtualBox to find the source. Based on some other distro instructions I think I just needed to do something like this: “KERN_DIR=/usr/src/kernels/`uname -r`.”

In any case, I had good luck with openSUSE 13.1. Oracle SQL Developer connects to Oracle 12c and the OS performance is good even with both VMs running. Now, my Oracle database and Oracle client environments are both running in virtual machine images that can be moved from one computer to another. More importantly, I can run Java 7 on openSUSE and Java 6 on Ubuntu, my host OS, where I do some customer development.

This illustration depicts the resulting spatial lab environment.spatiallab

Picking a Linux distro took longer than I thought it would. I’m reading to move on now.

Is Oracle MapViewer a Fit for Me?

Recently I began evaluating Oracle MapViewer to determine if it is the right tool for me. I’m looking for a mapping API that will allow me to manage vector graphics, points and polylines, independently from the base map. Today, I write too much repetitive code for map providers like Google Maps and OSM.  Oracle MapViewer it is a good tool for people working in a Java EE environment. It also has a place as part of Oracle’s Business Intelligence Suite, and is a very handy adhoc spatial data viewer, but does it belong in my website development toolbox?

current_map_stackMy personal preference is open source and lightweight, as this graphic illustrates, with a client-side JavaScript MVC framework making AJAX calls to JAX-RS web services. The RESTful web services should run in a standard Java web container and not depend on Java EE server containers.

In order to do vector graphics and event handling for polylines and points independently from the base map, my map API abstraction layer must be replaced. Ideally, the replacement framework should be purely JavaScript and should not require any server-side changes, something like Leaflet.

MapViewer Components

MapViewer is much more than just a JavaScript framework for maps. It has several server-side components that support its client-side AJAX-based JavaScript API:

  • A core rendering engine Java library, SDOVIS, that performs cartographic rendering. A servlet exposes the rendering functions to Web applications.
  • Application programming interfaces for programming MapViewer features. These APIs include XML, Java, PL/SQL, and an AJAX-based JavaScript API.
  • A graphical Map Builder tool that enables you to create map symbols, define spatial data rendering rules, and create and edit MapViewer objects.
  • Oracle Maps, which includes map cache and FOI (feature of interest) servers that facilitate the development of interactive geospatial Web applications.

The MapViewer architecture is not targeted towards my ideal environment of simple Java web containers. The prefered environment is Oracle WebLogic or Glassfish.

That being said, MapViewer does support Tomcat, but to what extent I’ve yet to discover. The following table is from the Map Viewer J2EE Servers Support page:

J2EE Server Name and Version

 Oracle WebLogic Server  10g and Later
 GlassFish  3.1.2 or later
 Oracle Application Server  10g (10.1.3.x)
 WebLogic  9
 Oracle OC4J Standalone  10g (10.1.3.x)
 Apache Tomcat  6.x and Later
 WebSphere  6.x and 7.x
 JBoss  4.x and Later

This architecture is illustrated in this graphic from the Oracle Fusion Middleware MapViewer Primer.

mapviewerIn my current work environment, pools of servers support different domains of our site. There is nothing to say that one pool of servers cannot include Java EE servers to handle the mapping load. It would still serve to isolate the mapping services from other server pools as well reduce the workload on the other servers. The business justification for the new pool of mapping servers depends on how many MapViewer features add value to our site, things like dynamic heatmaps, for example.

I’ll start my deep dive with the tutorials that come with the project, and then will explore some of the extensive features list in MapViewer data sheet.

How to Fix SQL Developer Start-up Error on Ubuntu

sqldevicon

After my upgrade from Ubuntu 12.04 to Ubuntu 14.04 failed I was forced to re-installed Ubuntu. While rebuilding my machine I ran into a problem running Oracle SQL Developer 4.0.2. While I was in set-up mode, I also took the time to add SQL Developer to the applications list, as pictured above.

I downloaded Oracle SQL Developer 4.0.2, but it caused a Java dump when I tried to start it, despite having installed Java 7, exported JAVA_HOME, and having defined the JDK path in /home/smitchell/.sqldeveloper/4.0.0/product.conf.

SetJavaHome /usr/lib/jvm/java-7-oracle

This post in the Oracle Community about the issue recommends adding the following “unset” command to sqldeveloper.sh. That fixed the start-up crash.

#!/bin/bash
unset -v GNOME_DESKTOP_SESSION_ID
cd "`dirname $0`"/sqldeveloper/bin && bash sqldeveloper $*

I do still see a stack trace in the console, “java.net.MalformedURLException: unknown protocol: sqldev.temp,” but it doesn’t stop SQL Developer from running.

Next, to get SQL Developer to show up in Ubuntu’s Applications list, I added a desktop file for SQL Developer to the applications directory with the following content.

sudo gedit /usr/share/applications/sql-developer.desktop
[Desktop Entry]
Version=1.0
Name=SQL Developer
Comment=Oracle SQL Developer is an integrated development environment that simplifies the development and management of Oracle Database.
Exec=/home/smitchell/Applications/sqldeveloper/sqldeveloper.sh
Path=/home/smitchell/Applications/sqldeveloper/
Icon=/home/smitchell/Applications/sqldeveloper/icon.png
Terminal=false
Type=Application
Categories=Utility;Application;Development;

The result is the image at the beginning of this post. It’s not the prettiest icon in the world, but it will do.

 

2014 Location Intelligence Conference Report

Screenshot from 2014-05-23 15:18:18

The Location Intelligence conference in Washington, D.C. this week reinvigorated me. Tim Gerber, my database administrator/coworker, and I gave a presentation, How Garmin Connect Manages and Analyzes 5-billion Miles of User Activities , and we attended some very informative sessions.

It was exciting to meet both authors of Applying and Extending Oracle Spatial, Siva Ravada, and Simon Greener. You may know Simon from his web site spatialdbadvisor.com.

Dan Geringer, who has been an invaluable spatial resource to Garmin, arranged a meeting for Tim and I with LJ Qian from Oracle’s Map Viewer team and Jayant Sharma, an Oracle Spatial Product Manager. We talked at length about generating dynamic heat maps from Oracle Spatial data, and we discussed using Oracle Map Viewer as an abstraction layer above base maps from Google, OSM, and other providers. I also enjoyed visiting with Oracle Product Manager, Jean Ihm.

My top priority coming out of the conference is to see if I can do with the Oracle Map Viewer HTML5 map client what I had hoped to accomplish with Leaflet. I want to separate our map code, line and point interaction, from the base map provider selected by the user, so that we can reduce the code written specifically for Google, Bing, OSM, and Baidu maps.

Since returning home I’ve been trying to find a Linux distro that runs well under Oracle VirtualBox to use as my test environment for Oracle SQL Developer, Oracle MapViewer quick start, and possibly Oracle WebLogic. Ubuntu 14.04 did poorly due to an issue with Unity 3D. Fedora 20 also seemed slow. OpenSUSE 13.1 looks promising, but I’m not far enough along in the set-up know for sure.

Spring JDBC and Ehcache

A couple of months ago I started refactoring a client’s code to bring it up to current standards. The client had three projects talking to the same database three different ways: MyBatis, Hibernate, and Spring JDBC. Also, two of the projects were still using ANT. I set out to switch all the projects to Maven, Spring, and a single, shared DAO layer using Spring JDBC.

One of the client’s projects exports millions of rows of data. Small changes can greatly affect export time. There was one heavily called block of code that used a HashMap in an instance variable to cache some objects. I decided to move caching into the DAO instead. That change caused the export to run three times longer than before. Why was Ehcache so slow? Had I done something wrong?

I set-up a test project to measure how long it took to randomly read 10,000 rows from a 340 row table. Here are the results.

Cache Performance

Cache Performance

It took 0.043 seconds to perform 10,000 random reads from a HashMap of 340 objects. By contrast, it took almost 27 seconds to fetch the same set of 10,000 random keys directly from MySQL. The H2 database only took 0.58 seconds. The elapsed times were about the same using Ehcache backed by either H2 or MySQL, taking 2.36 seconds and 2.42 seconds, respectively.

Example Project on GITHub

To experiment with my configuration I created a simple test project that has H2 Database and MySQL. You can find the code on GITHub if you want to run the tests yourself.

https://github.com/smitchell/SpringJdbcEcache

Setting up Spring JDBC and Ehcache

I used annotations to set-up Spring JDBC and Ehcache.

Here is how I set-up the cache in the Spring context.

    <bean id="ehcache" class="org.springframework.cache.ehcache.EhCacheManagerFactoryBean"
          p:configLocation='ehcache.xml'
          p:shared="true" />
    <bean id="cacheManager" class="org.springframework.cache.ehcache.EhCacheCacheManager" p:cacheManager-ref="ehcache" />
    <cache:annotation-driven />

Here is an example from the Country DAO in the sample project.

@Repository
public class CountryDaoImpl implements CountryDao {

    @Cacheable("countries")
    public Country findByPrimaryKey(final Long id) {
        final MapSqlParameterSource params = new MapSqlParameterSource("value", id);
        final String sql = selectSQL.concat(" where ").concat(CountryColumn.COUNTRY_ID.name()).concat(" = :value");
        final List results = jdbcTemplate.query(sql, params, countryRowMapper);
        if (results.size() > 0) {
            return results.get(0);
        }
        return null;
    }

    @CacheEvict(value = "countries", allEntries=true)
    public void save(Country instance) {
        final Date now = new Date();
        instance.setLastUpdateDate(now);
        if (instance.getId() ==  null) {
            instance.setCreateDate(now);
            insert(instance);
        } else {
            update(instance);
        }
    }
...
}

The Ehcache configuration was equally straightforward.

<?xml version="1.0" encoding="UTF-8"?>
<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://ehcache.org/ehcache.xsd"
         updateCheck="true" monitoring="autodetect" dynamicConfig="true">
    <diskStore path="java.io.tmpdir"/>
    <defaultCache maxEntriesLocalHeap="10000" eternal="false" timeToIdleSeconds="120" timeToLiveSeconds="120"
                  diskSpoolBufferSizeMB="30" maxEntriesLocalDisk="10000000" diskExpiryThreadIntervalSeconds="120"
                  memoryStoreEvictionPolicy="LRU">
        <persistence strategy="localTempSwap"/>
    </defaultCache>
    <cache name='countries' maxElementsInMemory='400' eternal='true' overflowToDisk='false' />

</ehcache>

Conclusions

I’m still figuring out how best to leverage Ehcache, but it is clear that for small collections of simple objects, like categories, types, countries, states, roles, etc, Ehcache is overkill. For those types of objects I will add a HashMap to my service object to act as a simple cache.

The place for Ehcache will probably turn out to be those compound object graphs assembled from calls to multiple tables. I’m going selectively leverage Ehcache in my service (@Component) implementations to hang on to those complex, composite objects.

If I have success I’ll add a follow-up to this post.

Help Wanted: QGIS Mentor

quantum

HELP WANTED: QGIS Mentor

My goal was simple, generate a heat map in QGIS that looked similar to the heat map that I generated using Google Fusion Tables with just a couple of clicks, or similar to what was eventually implemented on Garmin Connect.

This is what my test data set of 350,000 GPS points looks like in QGIS 1.9.0 on a PostGIS layer:

PostGIS Layer in Quantum GIS 1.9.0

PostGIS Layer in Quantum GIS 1.9.0

Several attempts resulted in either no visible heat map (above) or a large blobbish, weather map looking effect, not what I wanted.

I talked to the cartographer who took my Google Fusion Table prototype from concept to production. His process bears little resemblances to my prototype. Instead of a loose collection of GPS points, he extracted running and cycling activity polylines. Instead of using a heat map plugin he wrote custom C++ code to convert the vector data into raster data.

So, I’ll switch from loose points to polylines too. Oracle Spatial and Graph has a SDO_UTIL.FROM_GML311GEOMETRY function to export the polylines in GML format. Likewise, PostGIS has a ST_GeomFromGML to import GML data into a geometry. I’ll work on creating a data set of 20,000 – 30,000 polylines.

I sure could use a QGIS mentor to answer my basic questions and to help me avoid making wrong turns. It might be time to reach out some cartographers in other departments at work.  I also plan on reading “Learning QGIS 2.0,” by Anita Grase, when that comes out later this month. I guess Anita can be my virtual mentor.

Loading GPS Data into PostGIS

postgis-logo

The next area I will explore is heat maps using 8 Mb of GPS data collected from fitness devices near Olathe, KS.  My data source is a CSV file with three columns: latitude, longitude, and intensity. Latitude and longitude were rounded to four decimal places and I kept track of how many rounded GPS locations fell into each rounded latitude/longitude cell. Intensity is a decimal number between zero and one reflecting the number of hits for each latitude/longitude cell.

The first step was to create a table with a column for intensity and a spatially enabled column to hold the rounded GPS location:

CREATE TABLE gps_points (INTENSITY Real);
SELECT AddGeometryColumn('','gps_points','LOCATION', 4326, 'POINT', 2);
CREATE INDEX gps_points_location_idx ON gps_points USING GIST (location);
ALTER TABLE gps_points ADD CONSTRAINT gps_points_location_uniq UNIQUE (location);

The GPS_POINTS Table with Index and Constraints

The GPS_POINTS Table with Index and Constraints

Next, I set-up a Spring Batch project to import the data. The reason that I took the time to create a Spring Batch project is so that I can add more item writers to load the data set into other spatial repositories like Oracle Spatial and Graph or MySQL.

Here is the item reader and item writer definition from my Spring context:

Spring Batch Item Reader and Item Writer Definitions

Spring Batch Item Reader and Item Writer Definitions

I was not able to format the SQL correctly in the item writer bean in-line, so I implemented an ItemWriter class in Java.

Custom Item Writer to Insert PostGIS Points

Custom Item Writer to Insert PostGIS Points

The data and the code is available from github: https://github.com/smitchell/SpatialLoader

In my next post I’ll take a look at the data with a PostGIS layer in QGIS 1.9.