Search

Navigation

Top 60 Oracle Blogs

First Steps in Spatial Data

Original article

Submitted by davidkurtz on Wed, 2021-02-03 12:20

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

Problem Statement

A map reading stop!

When I am not working with Oracle databases, I am a keen cyclist and I ride with a touring 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. 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 have also started to 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 then be uploaded, compared and analysed. Every time I go out on the bike I upload the activity. I also uploaded my back catalogue of GPS data. As a result of the Coronavirus lockdowns, I bought an indoor trainer by that I use with Zwift and that also posts data to Strava. My most recent toy is a heart monitor. Both Strava and Zwift also capture data from that. 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.

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

I ride up Swain's Lane in Highgate most days. How long do I take, and am I getting faster or slower?
I want to go for a ride in the Chilterns, so 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.