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 :-)
