Simple way to import GPX file?

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Simple way to import GPX file?

Winfried
Hello,

I need to importe a GPX file that contains a few thousand waypoints, eg.
<wpt lat="41.37824" lon="9.21024"><name>Some name</name></wpt>

I tried https://mygeodata.cloud and https://geoconverter.hsr.ch, but for
some reason, the "waypoints" table doesn't contain latitude + longitude
infos.

Before I write a Python script, is there a simple way to import GPX data
into SQLite?

Thank you.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple way to import GPX file?

Simon Slavin-3
On 9 Dec 2018, at 9:44pm, Winfried <[hidden email]> wrote:

> I need to importe a GPX file that contains a few thousand waypoints, eg.
> <wpt lat="41.37824" lon="9.21024"><name>Some name</name></wpt>

Take a copy of the file, then edit the copy in a text editing program.  Use global search/replace to turn it into csv format.  For example, the above line would become

41.37824,9.21024,"Some name"

Then use the SQLite CLI tool to import the CSV file.

I think this will be faster than writing your own code to convert it or import it.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple way to import GPX file?

Winfried
Good call, thank you.

For others' benefit:

1. Copy the file, open the copy in a text editor, use a regex to turn the
data into tab-separated columns

2. Create a new file, and create the table:
sqlite3 waypoints.sqlite

sqlite> CREATE TABLE waypoints (name text, latitude text, longitude text, id
INTEGER PRIMARY KEY);

3. Import data:
sqlite> .separator "\t"
sqlite> .import waypoints.tsv waypoints
select * from waypoints where id=1;



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple way to import GPX file?

Larry Brasfield
In reply to this post by Winfried
Winfried wrote:
< Hello,
<
< I need to importe a GPX file that contains a few thousand waypoints, eg.
< <wpt lat="41.37824" lon="9.21024"><name>Some name</name></wpt>
<
< I tried https://mygeodata.cloud and https://geoconverter.hsr.ch, but for
< some reason, the "waypoints" table doesn't contain latitude + longitude
< infos.
<
< Before I write a Python script, is there a simple way to import GPX data
< into SQLite?

At this web page, https://www.gaia-gis.it/fossil/spatialite-tools/index ,
you can see a reference to an XML import facility, spatialite_xml_load ,
which is part of spatialite-tools.  It is very generalized, and can be
used to create a SQLite database from well-formed XML.  Once you have a
DB loaded with that tool, (which will be a several-years-old version of
the SQLite3 file format, but readable by modern SQLite3), you can attach
and query that DB to get the data into whatever DB schema you favor.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple way to import GPX file?

Simon Slavin-3
In reply to this post by Winfried
On 9 Dec 2018, at 10:16pm, Winfried <[hidden email]> wrote:

> sqlite> CREATE TABLE waypoints (name text, latitude text, longitude text, id
> INTEGER PRIMARY KEY);

If you expect to use this table in the long term, rather than just extract data from it, then define latitude and longitude as REAL.  This will make searching and sorting easier.

Similarly, you should probably define name as TEXT NOCASE.  Again, it won't matter now but may do if you ever need to search or sort.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple way to import GPX file?

Winfried
Thanks to both of you. Problem solved :-)



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple way to import GPX file?

nomad
In reply to this post by Winfried
On Sun Dec 09, 2018 at 03:16:15PM -0700, Winfried wrote:
> Good call, thank you.
>
> For others' benefit:
>
> 1. Copy the file, open the copy in a text editor, use a regex to turn the
> data into tab-separated columns

If you are running some kind of unix-like environment this is something
Perl can be quite useful for:

    grep '^<wpt' source.gpx | perl -p -E \
       's!<wpt lat="(.*)" lon="(.*)"><name>(.*)</name></wpt>!$1\t$2\t$3!' \
       > waypoints.tsv

> 2. Create a new file, and create the table:
> sqlite3 waypoints.sqlite
>
> sqlite> CREATE TABLE waypoints (name text, latitude text, longitude text, id
> INTEGER PRIMARY KEY);
>
> 3. Import data:
> sqlite> .separator "\t"
> sqlite> .import waypoints.tsv waypoints
> select * from waypoints where id=1;

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple way to import GPX file?

Gabor Grothendieck
In reply to this post by Winfried
The csvfix command line utility is useful to edit such files down to a csv file
which can then be read into sqlite.

  csvfix from_xml -smq -re wpt gpx.xml

On Sun, Dec 9, 2018 at 4:44 PM Winfried <[hidden email]> wrote:

>
> Hello,
>
> I need to importe a GPX file that contains a few thousand waypoints, eg.
> <wpt lat="41.37824" lon="9.21024"><name>Some name</name></wpt>
>
> I tried https://mygeodata.cloud and https://geoconverter.hsr.ch, but for
> some reason, the "waypoints" table doesn't contain latitude + longitude
> infos.
>
> Before I write a Python script, is there a simple way to import GPX data
> into SQLite?
>
> Thank you.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple way to import GPX file?

Alex Mandel-2
In reply to this post by Winfried
On 12/9/18 14:43, Winfried wrote:
> Thanks to both of you. Problem solved :-)
>
>

Long term, look at spatial related tools. Like Spatialite built on top
of Sqlite, or ogr2ogr which is part of gdal (www.gdal.org). That can
convert gpx to csv in one command, or even sql possibly (it does work
with spatialite). Also gbsbabel is a classic GPS data conversion tool.

Thanks,
Alex
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple way to import GPX file?

John McMahon
In reply to this post by nomad
I know I am coming to this a couple of weeks late, but I have been doing
this for several years and thought I would add my 2 cents worth.
Probably too late for OP, but may be useful for someone else later.

I used gpsbabel like this initially (in a JPSoft 4nt/tcmd script),

    gpsbabel -i gpx ^
             -f %fname ^
             -x nuketypes,tracks,routes ^
             -o xcsv,style=G7W-xcsv.style ^
             -F "%@name[%fname].csv"

%fname - variable containing source file name
%@name[ ... ] - function to extract basename from full filename

with this style sheet to generate .csv files in my desired format.

# gpsbabel XCSV style file
#
# Format:   G7toWin csv format
# Author:   John McMahon
#   Date:   2005may24
# Update:   2006jun02jmcm
#

DESCRIPTION G7toWin csv file format
#
# FILE LAYOUT DEFINITIONS
#

FIELD_DELIMITER   COMMA
RECORD_DELIMITER  NEWLINE
BADCHARS          COMMA
SHORTLEN          10

PROLOGUE Version 2:CSV
PROLOGUE Datum:,WGS-84
PROLOGUE ZoneOffset:,0.000000
PROLOGUE
"Type","Name","Lat","Long","Month\#","Day#","Year","Hour","Min","Sec","Comment","Symbol#","SymbolColor","SymbolDisplay","Altitude
(Meters)","Depth (Meters)","Ref Dist","Ref units"

#
# INDIVIDUAL DATA FIELDS, IN ORDER OF APPEARANCE
#

IFIELD   CONSTANT,     "W", "%s"    # "Type",
IFIELD   SHORTNAME,     "", "%s"    # "Name",
IFIELD   LAT_DECIMAL,   "", "%f"    # "Lat",
IFIELD   LON_DECIMAL,   "", "%f"    # "Long",
IFIELD   IGNORE,        "", "%s"    # "Month#",
IFIELD   IGNORE,        "", "%s"    # "Day#",
IFIELD   IGNORE,        "", "%s"    # "Year",
IFIELD   IGNORE,        "", "%s"    # "Hour",
IFIELD   IGNORE,        "", "%s"    # "Min",
IFIELD   IGNORE,        "", "%s"    # "Sec",
IFIELD   IGNORE,    ,   "", "%s"    # "Comment",
IFIELD   IGNORE,        "", "%s"    # "Symbol#",
IFIELD   IGNORE,        "", "%s"    # "SymbolColor",
IFIELD   IGNORE,        "", "%s"    # "SymbolDisplay",
IFIELD   IGNORE,        "", "%s"    # "Altitude (Meters)",
IFIELD   IGNORE,        "", "%s"    # "Depth (Meters)",
IFIELD   IGNORE,        "", "%s"    # "Ref Dist",
IFIELD   IGNORE,        "", "%s"    # "Ref units"

However, I have recently replaced that with a perl script using the
Geo::GPX module.

John


On 10/12/2018 10:17, [hidden email] wrote:

> On Sun Dec 09, 2018 at 03:16:15PM -0700, Winfried wrote:
>> Good call, thank you.
>>
>> For others' benefit:
>>
>> 1. Copy the file, open the copy in a text editor, use a regex to turn the
>> data into tab-separated columns
>
> If you are running some kind of unix-like environment this is something
> Perl can be quite useful for:
>
>      grep '^<wpt' source.gpx | perl -p -E \
>         's!<wpt lat="(.*)" lon="(.*)"><name>(.*)</name></wpt>!$1\t$2\t$3!' \
>         > waypoints.tsv
>
>> 2. Create a new file, and create the table:
>> sqlite3 waypoints.sqlite
>>
>> sqlite> CREATE TABLE waypoints (name text, latitude text, longitude text, id
>> INTEGER PRIMARY KEY);
>>
>> 3. Import data:
>> sqlite> .separator "\t"
>> sqlite> .import waypoints.tsv waypoints
>> select * from waypoints where id=1;
>

--
Regards
    John McMahon
         [hidden email]
         04 2933 4203

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users