Importing GPX files to Oracle database, part 2

The previous post showed how to import a file in GPX-format into a table that uses the XMLType. This post shows how to import the waypoints from the GPS logger into a table with the native SDO_GEOMETRY type.

First create  a table:

create table gps_log (
  log_time timestamp with time zone,
  waypoint sdo_geometry,
  src varchar2(20));

The datatype for log_time is chosen like shown because the type used in the XML-file (xsd:dateTime) maps to this, which makes importing the time data much easier. (Have a look at the previous post to see this mapping.)

Not strictly necessary at this point, but I want to add metadata about the spatial column (aka layer). This is done by doing an insert on a view that resides in the MDSYS-schema (with a public synonym added). Insert on the view works by instead-of triggers defined on the view, in case you wondered how this works:

insert into user_sdo_geom_metadata(TABLE_NAME,COLUMN_NAME,diminfo,srid)
  values ('GPS_LOG','WAYPOINT',
    sdo_dim_array(sdo_dim_element('Latitude',-90,90,3),
      sdo_dim_element('Longitude',-180,180,3)),
    8307);
commit;

If you don't want to write this SQL yourself, you can use a nice feature in SQL Developer. Bring up the menu for the table in the Connections window, select Spatial and then Update Spatial Metadata...:

Then you can add the information in the pop-up window:

You can verify that this actually worked with:

select * 
from user_sdo_geom_metadata;

The app I was using used the position from the mobile network sometimes. I guess there was problem with the GPS reception at times. Anyway, the position reported from the mobile network does not include elevation, so I decided to leave it out. This has an implication when choosing the spatial reference ID; 8307 is commonly used for waypoints given by latitude and longitude (and without elevation), see this chapter for details. Another detail is the chosen tolerance; the app reported that the accuracy was around 4 meters from the GPS, usually worse from the mobile network, so I figured 3 meters is OK. (For geodetic coordinate systems the unit for tolerance is meter.)

Inserting the data into the table is pretty easy by adapting the SQL code at the end of previous post:

insert into gps_log(log_time,waypoint,src) 
  SELECT  EXTRACTVALUE(VALUE(t), 'trkpt/time'),
    sdo_geometry(2001,
      8307,
      sdo_point_type(EXTRACTVALUE(VALUE(t), 'trkpt/@lat'),
       EXTRACTVALUE(VALUE(t), 'trkpt/@lon'),null),
      null,
      null),
    EXTRACTVALUE(value(t),'trkpt/src') 
 FROM GPX g,
      TABLE(XMLSequence(extract(g.OBJECT_VALUE,
        '/gpx/trk/trkseg/trkpt',
        'xmlns="http://www.topografix.com/GPX/1/0"'))) t;
commit;

The following creates a spatial index on the WAYPOINT-column, but before you execute this, make sure the user has the privilege CREATE SEQUENCE in addition to CREATE TABLE:

create index gps_log_si
on gps_log(waypoint) indextype is mdsys.spatial_index

The statement above will return an error stack including ORA-29855 and ORA-13203 if metadata on the column is missing in USER_SDO_GEOM_METADATA. Probably it is a good habit to add it right after the creation of the table as shown previously. Note, if the statement failed, it may have created the index object (although not a useful one), and you may need to drop the index before you try again.