First Steps in Spatial Data

This is the introductory blog post in a series about using Spatial Data in the Oracle database.

Caveat: Spatial Data has been a part of the Oracle database since at least version 8i.  I have been aware of it for many years, but have never previously used it myself.  Recently, I have recently had some spare time and decided to experiment with it.  These blogs document my first steps.  I have spent a lot of time reading the documentation and using Google to find other people's blogs.  Where I found useful material I have provided links to it.  It is likely that more experienced developers can point out my mistakes, and better methods to achieve results.  In which case, I will gladly publish comments and make corrections to my material.

Index

1. Loading GPX data into XML data types
2. Convert GPX Track to a Spatial Line Geometry
3. Analyse a track in proximity to a GPS route
4. Obtaining Geographical Data
5. Searching For Geometries That Intersect Other Geometries
6. Text Searching Areas by their Name, and the Names of Parent Areas

Introduction

We used to use paper maps!

When not working with Oracle databases, I am a keen cyclist and I ride with a club.  I have also always enjoyed maps having been taught to read Ordnance Survey maps at school.  It is no surprise therefore that I lead rides for my cycling club.  We used to use (and you can still buy) paper maps.  By 2005, I was starting to use a GPS.

Compaq iPaq in an expansion 
jacket with a PCMCIA GPS

Initially, I recorded rides as tracks on PDA.  By 2012, I was regularly using an Android tablet on my handlebar bag for navigation.   The market has caught up and people now attach their phones to their handlebars or have dedicated bike computers with GPS and Bluetooth links to their phones.  The cycling club website includes a library of the routes of previous rides, however, you can only search that by the structured data held for that ride.  So, for example, I can only search for rides in the Chilterns if that word appears in the description.  I cannot do a spatial search.

I also use Strava, an internet service for tracking exercise.  It is mainly used by cyclists and runners.  Activities can be recorded on a phone or other device and are then uploaded, compared, and analysed.  Every time I go out on the bike I upload the activity.  I have also uploaded my back catalogue of GPS tracks.  As a result of the Coronavirus lockdowns, I bought an indoor trainer that I use with Zwift and that also posts data to Strava.  Both Strava and Zwift capture additional data from a heart monitor.  Strava will let you see a certain amount of analysis about your activities and how you compare to other people, and more if you pay for their subscription service.  They will also allow you to export and download all of your data as a set of structured data in CSV files, and also the GPX files and photographs that you uploaded.

Problem Statement

I thought it would be interesting to try to analyse and interrogate that data.  Typical questions might include:

1. I ride up Swain's Lane in Highgate most days.  How long do I take, and am I getting faster or slower?
2. I want to go for a ride in the Chilterns, I would like to see tracks of previous rides to get some route ideas.

So I am going to upload my Strava data into an Oracle database, load the GPS tracks currently in GPX files into the database, convert them to Spatial geometries, and then process them.  To answer the first question I will need to provide a working definition of Swain's Lane.  For the second, I need definitions of various areas.  For example, I will take the Chilterns to be the area designed by Natural England as an Area of Outstanding Natural Beauty.  So I will need to import a definition of that and other areas from published data.

The following series of blogs illustrate how I dealt with these and other challenges.