13.7.07

Google Maps in APEX on XE with spatial data

A picture says more than thousand words is well known saying, well let's have a look.

Choose your favorite country from the list, and the Google Map will pan to your country. As a bonus the selected country is colored red, the country shape is based on spatial data. You can zoom and move the map, or change the map type.

What do you need to build this application:

  • An Oracle XE database or better. The good news is that XE already contains the MDSYS schema full of spatial utilities. The bad news is that XE has no spatial data included. But ...

  • NAVTEQ provides sample data for Geocoder. Download, import and use it.

  • Apex, included with XE.

  • A Google Maps API key.

And it is all free, as in beer.

Just a remark about using the term spatial. As you can read here XE does not have the Spatial option included nor does it include Oracle Locator with or without Oracle Mapviewer, but XE contains the MDSYS schema and that is all we need.

Include Google Maps

Sign up for a Google Map API key here. For XE use the following URL http://127.0.0.1:8080/. to sign up. Take a look at the sample page shown after generation of your key.

Now create in APEX a page (in a new or existing application) and navigate to the page attributes.

In the Display Attributes section set the cursor focus to Do not focus cursor. In the HTML Header section insert the following code, replace ... with your Google maps API key.

<script src=http://maps.google.com/maps?file=api&amp;v=2&key=...&sensor=false
type="text/javascript"></script>

In the HTML Body Header (section Header and Footer) insert the following code:

<script type="text/javascript">
//<![CDATA[
function load() {
if (GBrowserIsCompatible()) {
var map = new GMap2(document.getElementById("map"));
map.setCenter(new GLatLng(37.4419, -122.1419), 13);
}
}
//]]>
</script>

In the Page HTML Body attribute (section HTML Body attribute) insert the following code:

onload="load()" onunload="Gunload()"

Add a HTML Text region to the page with the following region source:

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

For Internet Explorer a small change of the page template is needed otherwise Internet Explorer stops responding. The first line of header (section Definition) should look like this:

<html lang="&BROWSER_LANGUAGE." xmlns:htmldb="http://htmldb.oracle.com" xmlns:v="urn:schemas-microsoft-com:vml">

Run the page and if everything is alright you will see a map of Palo Alto. This is the 'hello world' example of Google Maps. Note that it is required to issue the SetCenter statement otherwise only a grey pane is shown on the place of the map. You can also add some bells and whistles: map controls and polylgons (points connected by lines with a fill color as well).

Prepare APEX

Let us finish the web front end first before retrieving the geodata from the database. Modify the page created in the previous section.

Change the pagetype in PL/SQL (anonymous block). Insert the following in Region Source (section Source):

p_gis_data.draw_map(p_country=>:P1_COUNTRY);

The package p_gis_data is discussed later.

Add two items to the page: P1_COUNTRY (select list with submit) with the following dynamic list of values query:

SELECT initcap(country) display_value,
country return_value
FROM m_world
ORDER BY 1

And add a placeholder for the map P1_DIV (Display as text, does save state) with the following value for Source value of expression (section Source):

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

In order to initialize the Google Map add a before region process. This process sets the default value of the select list when there is no item selected.

IF :P1_COUNTRY IS NULL
THEN
:P1_COUNTRY := 'Netherlands';
END IF;

Load sample data

Download the sample data and unzip. Create a separate user and tablespace for the sample data:

SQL> CREATE TABLESPACE gis_data DATAFILE 'C:\ORACLEXE\ORADATA\XE\GIS_DATA.DBF' SIZE 600M ONLINE;
SQL> CREATE USER gis_data IDENTIFIED BY gis_data DEFAULT TABLESPACE gis_data TEMPORARY TABLESPACE temp;
SQL> GRANT CONNECT, RESOURCE TO gis_data;

Only execute steps 1 and 2 from the README. You do not need to install Mapviewer. After some time all the data is loaded.

Retrieve spatial data

It's time to retrieve spatial data from the database. Take a look at the M_WORLD table, there is a column named GEOMETRY with datatype SDO_GEOMETRY which stores all the spatial data. The GEOMETRY column stores the position (property GEOMETRY.SDO_POINT), more or less the center, of a country and one of more polygons. A polygon consists of points connected by (straight) lines. Remember the Google polygons?

Our task is to retrieve all polygons from the GEOMETRY column. Property GEOMETRY.SDO_ELEM_INFO tells us how many polygons the property GEOMETRY.SDO_ORDINATES stores and of which kind they are. The function SDO_UTIL.GETNUMELEM retrieves the number of elements for a given geometry, in our case the number of polygons. The SDO_UTIL.EXTRACT function extracts the nth element from a geometry returning a geometry object as well. And the function SDO_UTIL.GET_VERTICES retrieves all points of geometry. Combining these two functions will result in a number of geometry objects, each object corresponds with one polygon.

We can retrieve this information in one SQL statement:

SELECT country.geometry.sdo_point.x x,
country.geometry.sdo_point.y y,
country.element,
CURSOR
(SELECT x,
y
FROM TABLE(sdo_util.getvertices(sdo_util.EXTRACT(country.geometry, country.element)))
ORDER BY id) area
FROM
(SELECT iv.*,
LEVEL element
FROM
(SELECT *
FROM m_world
WHERE country = cp_name) iv
CONNECT BY LEVEL <= sdo_util.getnumelem(geometry)) country
/

The inline view iv selects one row from the M_WORLD table and for each element of the corresponding geometry a copy of this row is generated. In the country select each element is stored in a cursor expression. The next thing is to write some PL/SQL code to generate javascript based on this query i.e. p_gis_data.

Further reading

The idea for this blog entry came after reading Creating Thematic Google Mapping Applications (for Business Intelligence) using Oracle Locator/Spatial and Application Express and Auf den Ort kommt es an: Geodaten in Application Express-Anwendungen nutzen (in german) and of course Oracle Spatial User's Guide and Reference.

1.7.07

APEX 3.0.1 on XE

On 29 Februari 2008 Oracle released Application Express 3.1 . With this release you can upgrade the Application Express within Oracle XE to 3.1. For more information on this topic see Oracle Database XE and Application Express 3.1 and APEX 3.1 on XE .

Oracle released Application Express 3.0.1 recently. With this release you can upgrade APEX within Oracle XE from 2.1 to 3.0.1 (see Oracle Database XE and Application Express 3.0.1). But how to upgrade when you already installed APEX 3.0 on XE?

There is a patch available on Metalink to upgrade from APEX 3.0 to 3.0.1. According to the Patch Set Notes Oracle9i release 2 and higher support this patch if you have APEX 3.0.0.00.20 already installed (the Patch Set Notes also includes a list of fixed bugs). In order to download the patch from Metalink, you will need a support contract (which is not included with XE).

Another option is to remove APEX 3.0 completely. After the removal, XE has APEX 2.1. (still) installed and the next step is of course to install APEX 3.0.1. The big advantage of this option is you following the main route of upgrading XE/APEX as proposed by Oracle. However there are some things to keep in mind.

  • As always start with making a backup of your database.

  • Export all applications you would like to see again in APEX 3.0.1. If you remove APEX 3.0 completely, you also remove all applications stored in the FLOWS_030000(APEX 3.0) schema! Note that applications stored in the FLOWS_020100 schema will be migrated to FLOWS_030000 (APEX 3.0.1).

  • Make a backup of changed or added cascading style sheets (CSS), images and java scripts separately.

  • Run the apex_3.0.1\apxremov.sql script (the script is the same for both versions).

  • Run the apex_3.0.1\apxins.sql script. Check the installation log for errors.

  • Copy the apex_3.0.1\images directory to the /i/directory. Also copy the previous backed up CSS, images and java scripts to the /i/ directory.

  • Import all applications exported in the first step. Note that existing applications in APEX 2.1 are migrated to APEX 3.0.1 as part of the upgrade process.

  • Time to make a backup again.

Have a look at the APEX reports in SQL Developer, no errors any more.