Update 2016-10-16: Found and error in procedure, see below.
In preparation for a presentation on Oracle Spatial for DBAs I decided to import some GPS-data. I have an app called GPSLogger on my Android mobile that logs GPS data to a file. The format for this is GPS Exchange Format (GPX). Since I spent some time understanding the XML side of this I decided to blog about it.
Note, there are two versions of the GPX schema, 1.0 and 1.1. My app uses the version 1.0, if you try to import a GPX file, have a look in it to verify which schema definition it uses (Look for xsi:schemaLocation in the head of the GPX file).
I followed the information from this blog post (that actually uses version 1.1) together with info from Oracle Support, and lots of googling.
The app can upload to Dropbox which makes it easy to transfer the file to the database server. In addition I needed the schema definition, which you can get from Topographix.com, you’ll find the link to the XSD file in the GPX file as mentioned, in my case it was http://www.topografix.com/GPX/1/0/gpx.xsd
However, the file needs some adjustment in order to have Oracle parse values of type xsd:dateTime correctly. Open it in an editor and look for the following block:
<xsd:schema
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:gpx="http://www.topografix.com/GPX/1/0"
targetNamespace="http://www.topografix.com/GPX/1/0"
elementFormDefault="qualified">
At the end of this, add one line so it looks like this:
<xsd:schema
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:gpx="http://www.topografix.com/GPX/1/0"
targetNamespace="http://www.topografix.com/GPX/1/0"
elementFormDefault="qualified"
xmlns:xdb="http://xmlns.oracle.com/xdb>"
Then you have to search through the file and search for every definition that uses type “xsd:dateTime” and add:
xdb:sqltype=“TIMESTAMP WITH TIME ZONE”
so it looks like this:
xsd:element minoccurs="0" name="time" type="xsd:dateTime"
xdb:sqltype=“TIMESTAMP WITH TIME ZONE”
There are four entries, so this manual job does not take a long time. I transferred this file together with my GPX file to a directory on my server (/u01/app/oracle/load_dir). Here is the first part to get started, the first part is executed as SYSTEM:
create user oyvind identified by oracle
default tablespace users temporary tablespace temp
quota unlimited on users;
grant create session, create table, create view, create procedure to oyvind;
grant alter session to oyvind;
grant create type to oyvind;
create directory LOAD_DIR as ‘/u01/app/oracle/load_dir’;
grant read, write on directory load_dir to oyvind;
As you can see, I did not granted XDBADMIN to this user. Update 2016-10-16: _The use of DBMS_XDB does indeed require being SYS, the owner of the package (XDB), or having one of the roles XDBADMIN / DBA. Since I did not want an ordinary user to have an admin role, I executed the first part that uses the DBMS_XDB package as SYSTEM. The function below was created in the schema of the ordinary user as well. I must have been experimenting with granting and revoking the XDBADMIN without noticing that the first part did nothing since the resource was already there._
The first step is to load the XML schema definition to the database. I copied the function getClobDocument from the mentioned blog, it is used to load files as CLOB:
create or replace function getClobDocument(
p_directoryname In varchar2,
p_filename In varchar2,
p_charset In varchar2 default NULL)
return CLOB deterministic
is
v_file bfile := bfilename(p_directoryname,p_filename);
v_charContent CLOB := ' ‘;
v_targetFile bfile;
v_lang_ctx number := DBMS_LOB.default_lang_ctx;
v_charset_id number := 0;
v_src_offset number := 1 ;
v_dst_offset number := 1 ;
v_warning number;
begin
if p_charset is not null then
v_charset_id := NLS_CHARSET_ID(p_charset);
end if;
v_targetFile := v_file;
DBMS_LOB.fileopen(v_targetFile, DBMS_LOB.file_readonly);
DBMS_LOB.LOADCLOBFROMFILE(v_charContent,
v_targetFile,
DBMS_LOB.getLength(v_targetFile),
v_src_offset,
v_dst_offset,
v_charset_id,
v_lang_ctx,
v_warning);
DBMS_LOB.fileclose(v_targetFile);
return v_charContent;
end;
/
The following code worked for me to create resource:
declare
schemaURL varchar2(256) := ‘http://www.topografix.com/GPX/1/0/gpx.xsd';
schemaDoc varchar2(30) := ‘gpx.xsd’;
xmlSchema xmlType;
res boolean;
begin
xmlSchema := XMLTYPE(getCLOBDocument(‘LOAD_DIR’,schemaDoc,‘UTF8’));
if (dbms_xdb.existsResource('/home/’ ||schemaDoc)) then
dbms_xdb.deleteResource('/home/’ ||schemaDoc);
end if;
res := dbms_xdb.createResource('/home/' || schemaDoc,xmlSchema);
if res then
dbms_output.put_Line(‘OK’);
else
dbms_output.put_Line(‘NOK’);
end if;
end;
/
The rest of this is executed as user oyvind (Update: also create the getClobDocument from above to this user). Next step is to register the schema, I used the following code:
declare
schemaURL varchar2(256) := ‘http://www.topografix.com/GPX/1/0/gpx.xsd';
schemaDoc varchar2(30) := ‘gpx.xsd’;
begin
dbms_xmlschema.registerSchema
(
schemaURL,
XMLTYPE(getCLOBDocument(‘LOAD_DIR’,schemaDoc,‘AL32UTF8’)),
local => true,
genTypes => true,
genTables => false,
enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
);
end;
/
CREATE TABLE gpx
OF XMLType (CHECK (XMLIsValid(object_value) = 1))
XMLSCHEMA “http://www.topografix.com/GPX/1/0/gpx.xsd"
ELEMENT “gpx”;
Then, finally, you can load the GPX file with this:
INSERT INTO GPX
VALUES (XMLTYPE(getCLOBDocument(‘LOAD_DIR’,‘20161001.gpx’)));
commit;
To see some of the data, you can extract what you find interesting with:
SELECT
to_number(EXTRACTVALUE(VALUE(t), ‘trkpt/@lon’)) longitude,
to_number(EXTRACTVALUE(VALUE(t), ‘trkpt/@lat’)) latitude,
TO_NUMBER(EXTRACTVALUE(VALUE(t), ‘trkpt/ele’)) Elevation,
EXTRACTVALUE(value(t),‘trkpt/src’) src
FROM GPX g,
TABLE(XMLSequence(extract(g.OBJECT_VALUE,'/gpx/trk/trkseg/trkpt’,‘xmlns=“http://www.topografix.com/GPX/1/0"'
))) t;
This is an example of what comes out (it was a short walk):
LONGITUDE
LATITUDE
ELEVATION
SRC
10.79831903
59.83451229
85
gps
10.80009538
59.83263633
170
gps
10.80033655
59.83156553
146
gps
10.8003321
59.8303762
network
In next post I plan to use the Spatial datatype to store these points. I may do another test with GPX version 1.1 in the mean time since that is the version used by my Garmin GPS.
Some tips if you need to clean up in case of errors; if you have the recyclebin enabled, do a "purge recyclebin" after dropping the table with the XMLType. I used to following command to delete the schema:
purge recyclebin;
exec dbms_xmlschema.deleteschema(schemaurl => ‘http://www.topografix.com/GPX/1/0/gpx.xsd');
Get in touch if you need a copy of the final gpx.xsd file.