1

I have a CSV that contains some data relating to hikes undertaken by various people. For each hike, there is an ID, max_elevation, length, start_time, end_time, and GPX field (along with some other data - see image below).

csv_sample

The XML that makes up a GPX-file is stored for each hike under the GPX column in the CSV.

Essentially, my goal is to bring this CSV into SQL Server and conduct spatial operations on it. In order to do so, I would like to convert each GPX for each row to a geography data type (WKB or WKT)

Is there any way I can convert each GPX into a WKT or WKB as a line geometry?

Open to solutions in Python, SQL, or anything else you guys can think of.

Essentially, I'd like the output to look something like this:

desitred_ourput

Here is a link to the complete dataset i am using (found on Kaggle).

  • 2
    Could you add a sample of the csv file to your question? For example the line you are showing – BERA Feb 21 at 6:06
  • @BERA - It tried adding a sample to the question itself, but the text in the GPX column is much too large (thousands of characters)... I've linked the original dataset instead (it can be downloaded for free from Kaggle).... if you know of a better way to include an example, please let me know... Thanks!! – user3081547 Feb 21 at 16:48
0

Using python you could parse the gpx data with gpxpy:

import pandas as pd
import gpxpy

def to_linestring(gpx_val):
    try:
        gpx_parsed = gpxpy.parse(gpx_val)
    except:
        return ""
    pts = ((point.latitude, point.longitude) 
            for track in gpx_parsed.tracks 
            for segment in track.segments 
            for point in segment.points)
    pt_strs = [" ".join(map(str, pt)) for pt in pts]
    if len(pt_strs) == 0:
        return 'LINESTRING EMPTY'
    return "LINESTRING (" + ", ".join(pt_strs) + ")"


df = pd.read_csv('gpx-tracks-from-hikr.org.csv')
df['wkt'] = df.gpx.apply(to_linestring)

This is the simplest solution where all tracks and segments are merged in one linestring. It might be better to create a multilinestring that separates each track. There are also elevation on many points that could be included in a 3d linestring. Some points have missing elevation so you would have to use a default elevation value for those points.

The gpxpy.parse function seems to be pretty slow, so you can probably get better performance by parsing the gpx data with a xmlparser.

  • Thanks so much! When I run this on the CSV, i get this error: GPXXMLSyntaxException: Error parsing XML: AttValue: " or ' expected.... how would i modify the code to skip these records, and assign a value of 0 or 'NaN' to WKT column when these types of errors are encountered? – user3081547 Feb 22 at 23:20
  • @user3081547: I have edited my answer to handle cases when the parsing throws an Exception – Dataform Feb 23 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.