Oracle Spatial, APEX and Google Maps

In this post I will demonstrate how to use Oracle Spatial to store shapes, and utilize Oracle APEX and Google Maps API to visualize and manipulate the shapes.

The code is tested for Oracle 11g, but should work equally well for Oracle XE (no advanced Spatial operations), although I have noticed that Oracle XE is a bit more picky when it comes to valid shapes.

Demo Application
If there is too much text for you to read through, check out my demo Application on apex.oracle.com to see it in action :-)

The demo application is tested on Firefox ONLY, so expect issues when using other browsers!

Database objects
The code below will create a simple table with one column containing a sdo_geometry column called shapes, and a supporting package to mediate between APEX/Google Maps and the database table:

create table sample_polygon
(
   sample_polygon_id     number not null
 , name                  varchar2 (80) not null
 , description           varchar2 (4000)
 , zoom_level            number
 , shape                 mdsys.sdo_geometry
)
/

alter table sample_polygon add (
  constraint sample_polygon_pk
 primary key
 (sample_polygon_id))
/ 

create sequence sample_polygon_pk_seq
/

create or replace trigger trg_sample_polygon_bi
   before insert
   on sample_polygon
   referencing new as new old as old
   for each row
declare
   l_new_id       number;
begin
   if :new.sample_polygon_id is null
   then
      select   sample_polygon_pk_seq.nextval
        into   l_new_id
        from   dual;
      :new.sample_polygon_id   := l_new_id;
   end if;
exception
   when others
   then
      raise;
end trg_sample_polygon_bi;
/

create or replace package sample_polygon_p
as
   -- return string with coordinates
   function get_ordinates (
      p_shape                 in    sample_polygon.shape%type
   ) return varchar2;
   -- update table with shape
   procedure create_geometry (
       p_sample_polygon_id    in    sample_polygon.sample_polygon_id%type
   ,   p_name                 in    sample_polygon.name%type
   ,   p_description          in    sample_polygon.description%type
   ,   p_zoom_level           in    sample_polygon.zoom_level%type
   ,   p_shape                in    varchar2);
end;   
/

create or replace package body sample_polygon_p
as
   function get_ordinates (
      p_shape                 in    sample_polygon.shape%type
   ) return varchar2 is       
      l_poly   mdsys.sdo_ordinate_array;
      l_coords varchar2(4000);
      l_point  varchar2(20);
   begin
      l_poly := p_shape.sdo_ordinates;
      if l_poly.exists(1)
      then
         for i in l_poly.first..l_poly.last
         loop
            l_coords := l_coords ||','||replace(l_poly(i), ',', '.'); 
         end loop;
         l_coords := substr(l_coords, 2, length(l_coords));
      end if;
      return l_coords;
   end;
   procedure create_geometry (
       p_sample_polygon_id    in    sample_polygon.sample_polygon_id%type
   ,   p_name                 in    sample_polygon.name%type
   ,   p_description          in    sample_polygon.description%type
   ,   p_zoom_level           in    sample_polygon.zoom_level%type
   ,   p_shape                in    varchar2
   ) is
      l_sql       varchar2(32000);
      l_sample_polygon_id     sample_polygon.sample_polygon_id%type := p_sample_polygon_id;
   begin
      if l_sample_polygon_id is null
      then
         insert into sample_polygon
         (  name
         ,  description
         ,  zoom_level
         ) values
         (  p_name
         ,  p_description
         ,  p_zoom_level) returning sample_polygon_id into l_sample_polygon_id;
      else
         update sample_polygon
         set    name = p_name
         ,      description = p_description
         ,      zoom_level = p_zoom_level
         where  sample_polygon_id = l_sample_polygon_id;
      end if;
      -- 2003: Two dimentional polygon
      -- 4326: SRID
      -- 1,1003,1: one polygon (exterior polygon ring)
      l_sql := 'update sample_polygon'||chr(10)||
               'set    shape = sdo_geometry (2003'||chr(10)||
               '                            , ''4326'''||chr(10)||
               '                            , null'||chr(10)||
               '                            , sdo_elem_info_array(1,1003,1)'||chr(10)||
               '                            , sdo_ordinate_array( '|| p_shape ||')'||chr(10)||
               '                            )'||chr(10)||
               'where  sample_polygon_id = :1'; 
      execute immediate l_sql using in l_sample_polygon_id;
   end;
end;
/

You must have Oracle Spatial installed in your database (is part of default installation, including XE), and schema must have appropriate rights to invoke Spatial functions. If the above script delivers no error-messages you should be OK.

Why Spatial?
Well, why not? You already paid for it in you license, and quite frankly I can see no easier way to store your shapes. There are also a number of valuable functions that comes with Spatial, such as:

• Location inside shape
• Distance to nearest part of shape from location
• Conversion between coordinate types
• The ability to view shapes in other client tools like ESRI

I have very limited experience with Oracle Spatial, and the syntax for creating shapes as an SDO_GEOMETRY-data type was (is) a bit daunting.

Lessons learned are:
Take a close look at which coordinate system you are storing the points in. I have used the most common 4326, but if I understood correctly Google actually uses 3857 or 900913. This requires some further investigation.

The Google Maps API
There are already some examples on how to integrate Oracle APEX and Google Maps, and likewise, there are many examples on how to interact with Google Maps using the Google Maps API. I will list some of the resources on the subject:

• Official Oracle APEX/Google Maps integration written by Jason Straub
  
• Collection of map integrations with Oracle APEX by Patrick Wolf
  
• Google Maps API Tool by Kjell Scharning
  
• Official Google Maps API Reference
  

The first thing to do when trying out this code, is to sign up and get a Google Maps API Key!

A tiny bit of Javascript
The meaning of "tiny" being somewhat stretched here. Even if you are not familiar with Javascript, it is surprisingly easy to pick up. This script might not be the best place to start, but I encourage you to look at the source code from my demo application in detail to see what it does.

I will not do a full listing here, but highlight some of the more important functions and give a brief top-down view of what goes on.

On page rendering:

• Javascript function buildMap is called
• Invokes Google Maps API
• Draws a map
• Adds onClick-listener to the map to enable the user to create shapes

On click in map:

• Draws a new marker on the map
• Adds a new point to points array (this is what gets stored in the database)
• Redraws the polygon on the map

On click on a marker in the map

• Removes the marker
• Removes the point from the points array
• Redraws the map

Edit mode:

• Redraws map to ensure completeness of polygon
• Removes markers
• Makes polygon editable
• Removes onClick-listener on Map (Adding new points/markers in the map is no longer possible)

Exit edit mode:

• Repopulates points array from polygon
• Makes polygon uneditable (is that even a word?)
• Redraws polygon on map with markers

Create new shape:

• Removes all overlays from map (markers and polygon)
• Resets all arrays and variables
• Reattaches onClick-listener to map

For more details, you have to dig into the source code (view source in the demo application).

Merging Oracle Spatial and Google Maps
This not advanced mathematics, I have two functions in the database:

• One which extracts ordinates (as they are called, don't ask me why) from the stored SDO_GEOMETRY-object, and return the ordinates as a comma-separated string
• One which accepts a shape as a comma-separated string and creates a SDO_GEOMETRY-object

I also have two javascript functions:

• One which accepts a string of comma separated coordinates, splits it into an array og "Google points"
• One which accepts an array of "Google points", and returns a comma separated string (well, not exactly, it "returns" by setting an APEX item...)

Bringing it All Together
Oracle Apex is the glue between Google Maps and Oracle Spatial, but the actual coding in APEX to achieve this is quite little.

In my demo application I have:

• Region containing a DIV and all necessary Javascrips to show and interact with Google Maps
• Region based on a stored procedure to store shapes
• Region containing a report of stored shapes

That's it, enjoy :-)