Re: [sqlite] Simple way to import GPX file?
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 wrote: > > Hello, > > I need to importe a GPX file that contains a few thousand waypoints, eg. > Some name > > 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple way to import GPX file?
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 '^(.*)!$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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple way to import GPX file?
Thanks to both of you. Problem solved :-) -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple way to import GPX file?
On 9 Dec 2018, at 10:16pm, Winfried 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple way to import GPX file?
Winfried wrote: < Hello, < < I need to importe a GPX file that contains a few thousand waypoints, eg. < Some name < < 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple way to import GPX file?
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple way to import GPX file?
On 9 Dec 2018, at 9:44pm, Winfried wrote: > I need to importe a GPX file that contains a few thousand waypoints, eg. > Some name 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suitability for Macintosh OS 9.2?
Hello, I use SQLite on a Macintosh (snow white) with OS 9.2. However SQLite actually runs on a new mac mini and is accessed via virtual network computing, using VNCthing 2.2. On the MacOS side, only desktop sharing must be switched on. I thought I should mention it here as it is exclusively to run SQLite, in a terminal window. I had to overcome two issues: vi does not behave properly. When typing a '+' it opens a new line. This is overcome by using MacVIM. Since upgrading the Mac mini to OS X Mojave (10.14.2), VNC hangs when logging in for the second time, after disconnecting. This is overcome by logging out before disconnecting. Thanks, E. Pasma ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Simple way to import GPX file?
Hello, I need to importe a GPX file that contains a few thousand waypoints, eg. Some name 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE gives incorrect results for 'NOT IN' query if partial index exists
On 8-12-2018 23:47, Richard Hipp wrote:
On 12/8/18, Deon Brewis wrote:
I'm curious how that test that you added works? i.e. What causes the test
to fail if the results are wrong?
The particular test case you are referring to is written in in the TCL
language. The TCL tests are the oldest set of tests for SQLite since
SQLite is really a TCL-extension that escaped into the wild.
Everything in TCL is a command followed by zero or more arguments. In
this sense, TCL is very much like Bourne shell. COMMAND ARG1 ARG2
ARG3 Where TCL excels is in how it quotes the arguments. Curly
braces {...} are quoting characters that nest. Take, for example,
the "if" command in TCL:
if {$a<0} {
set a [expr {-$a}]
} else {
set a [expr {$a+10}]
}
In this case, the "if" command has four arguments
if EXPR SCRIPT else SCRIPT
When the "if" command runs, it evaluates its first argument EXPR. If
EXPR is true, then the if command runs the SCRIPT given in the second
argument. Otherwise it runs the SCRIPT in the fourth argument. The
magic, you see, is in the use of nested curly braces for quoting.
The test command you refer to is this:
do_execsql_test index6-12.1 {
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1(a,b);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t1 VALUES(2,2);
CREATE TABLE t2(x);
INSERT INTO t2 VALUES(1);
INSERT INTO t2 VALUES(2);
SELECT 'one', * FROM t2 WHERE x NOT IN (SELECT a FROM t1);
CREATE INDEX t1a ON t1(a) WHERE b=1;
SELECT 'two', * FROM t2 WHERE x NOT IN (SELECT a FROM t1);
} {}
The name of the command is "do_execsql_test". That command takes
three arguments:
do_execsql_test TESTNAME SQL-SCRIPT EXPECTED-RESULT
This command simply runs the SQL found in its second argument and
accumulates the results. The accumulated result should exactly match
the third argument. If it does not match, then it prints an error
message and increments the error counter.
If you start with the canonical SQL source code, you can generate the
appropriate TCL interpreter by typing
./configure; make testfixture
Or on windows:
nmake /f Makefile.msc testfixture.exe
Then you say "./testfixture test/index6.test" to run that particular test file.
You 'forgot' to add the example that Olivier Mascia gave?:
select * from bar WHERE x IN (SELECT y from foo); -- this will wrongly return 1.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
