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