Mixing
Save query result from QGIS to csv
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
I have a layer saved to spatialite which I want to query and save part of to csv.
Even the query result takes a very long time to load to the browser (2.5M rows) even if I load it without geometry.
Is there a way to use the SQL engine DB Manager has to save the query result to file?
I already tried the sqlite way with the example I found here.
and the COPY TO way PostgreSQL uses specified here.
Is there another way I am missing? I thought QGIS DB Manager uses an SQLite engine.
Using QGIS 3.2.1 on Windows.
qgis sql dbmanager
edited 2 hours ago
Jochen Schwarze
5,73731251
asked 3 hours ago
Dror Bogin
1217
add a comment |Â
up vote
1
down vote
favorite
I have a layer saved to spatialite which I want to query and save part of to csv.
Even the query result takes a very long time to load to the browser (2.5M rows) even if I load it without geometry.
Is there a way to use the SQL engine DB Manager has to save the query result to file?
I already tried the sqlite way with the example I found here.
and the COPY TO way PostgreSQL uses specified here.
Is there another way I am missing? I thought QGIS DB Manager uses an SQLite engine.
Using QGIS 3.2.1 on Windows.
qgis sql dbmanager
edited 2 hours ago
Jochen Schwarze
5,73731251
asked 3 hours ago
Dror Bogin
1217
Did you you consider 'Extract by expression' processing algorithm from the processing toolbox and save its result directly to csv?
– Jochen Schwarze
2 hours ago
the file is too large to work with, that's why i'm using SQL. Other than exporting it to sqlite i couldn't do anything with the original layer. my problem is the fact that i don't have and can't install even sqlite itself on the machine that has to work with the data.
– Dror Bogin
2 hours ago
add a comment |Â
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I have a layer saved to spatialite which I want to query and save part of to csv.
Even the query result takes a very long time to load to the browser (2.5M rows) even if I load it without geometry.
Is there a way to use the SQL engine DB Manager has to save the query result to file?
I already tried the sqlite way with the example I found here.
and the COPY TO way PostgreSQL uses specified here.
Is there another way I am missing? I thought QGIS DB Manager uses an SQLite engine.
Using QGIS 3.2.1 on Windows.
qgis sql dbmanager
edited 2 hours ago
Jochen Schwarze
5,73731251
asked 3 hours ago
Dror Bogin
1217
I have a layer saved to spatialite which I want to query and save part of to csv.
Even the query result takes a very long time to load to the browser (2.5M rows) even if I load it without geometry.
Is there a way to use the SQL engine DB Manager has to save the query result to file?
I already tried the sqlite way with the example I found here.
and the COPY TO way PostgreSQL uses specified here.
Is there another way I am missing? I thought QGIS DB Manager uses an SQLite engine.
Using QGIS 3.2.1 on Windows.
qgis sql dbmanager
qgis sql dbmanager
edited 2 hours ago
Jochen Schwarze
5,73731251
asked 3 hours ago
Dror Bogin
1217
edited 2 hours ago
Jochen Schwarze
5,73731251
asked 3 hours ago
Dror Bogin
1217
edited 2 hours ago
Jochen Schwarze
5,73731251
edited 2 hours ago
Jochen Schwarze
5,73731251
edited 2 hours ago
Jochen Schwarze
5,73731251
5,73731251
asked 3 hours ago
Dror Bogin
1217
asked 3 hours ago
Dror Bogin
1217
asked 3 hours ago
Dror Bogin
1217
1217
Did you you consider 'Extract by expression' processing algorithm from the processing toolbox and save its result directly to csv?
– Jochen Schwarze
2 hours ago
the file is too large to work with, that's why i'm using SQL. Other than exporting it to sqlite i couldn't do anything with the original layer. my problem is the fact that i don't have and can't install even sqlite itself on the machine that has to work with the data.
– Dror Bogin
2 hours ago
add a comment |Â
Did you you consider 'Extract by expression' processing algorithm from the processing toolbox and save its result directly to csv?
– Jochen Schwarze
2 hours ago
the file is too large to work with, that's why i'm using SQL. Other than exporting it to sqlite i couldn't do anything with the original layer. my problem is the fact that i don't have and can't install even sqlite itself on the machine that has to work with the data.
– Dror Bogin
2 hours ago
Did you you consider 'Extract by expression' processing algorithm from the processing toolbox and save its result directly to csv?
– Jochen Schwarze
2 hours ago
Did you you consider 'Extract by expression' processing algorithm from the processing toolbox and save its result directly to csv?
– Jochen Schwarze
2 hours ago
the file is too large to work with, that's why i'm using SQL. Other than exporting it to sqlite i couldn't do anything with the original layer. my problem is the fact that i don't have and can't install even sqlite itself on the machine that has to work with the data.
– Dror Bogin
2 hours ago
the file is too large to work with, that's why i'm using SQL. Other than exporting it to sqlite i couldn't do anything with the original layer. my problem is the fact that i don't have and can't install even sqlite itself on the machine that has to work with the data.
– Dror Bogin
2 hours ago
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
1
down vote
I claim the key problem is to build the table view resulting from your query, not the query itself. 'Extract by expression' algorithm in QGIS 3 is written as a native C++ algorithm and as such VERY FAST. In contrary to 'Select by expression' you don't generate a selection in QGIS 3 and then 'Save layer...' with 'Save only selected objects' enabled. 'Extract by expression' will save the query result directly to the format you desire which may take some time too as well, but you avoid creating a temporary table view in the attribute table or some query tool.
answered 2 hours ago
Jochen Schwarze
5,73731251
this was good and i did not know the tool, but i need to run a specific SQL query on the data and the python way solved it faster than rewriting the subset in the expression tool.
– Dror Bogin
23 mins ago
add a comment |Â
up vote
1
down vote
While @Jochen Schwarze's answer is good, it did not answer the question of how to perform an SQL query on the data.
since i don't have SQLite installed i used QGIS' python interpreter to read the data from the sqlite(spatialite, since geometry existed) file and write it to csv.
sqlite_file = 'path/to/test.sqlite'
csvfile = 'path/to/test.csv'
import sqlite3
import csv
conn = sqlite3.connect(sqlite_file)
conn.enable_load_extansion(True)
conn.execute('SELECT load_extension("mod_spatialite")')
conn.execute('SELECT InitSpatialMetaData(1);')
c = conn.cursor()
data = c.cursor('select column1, column2, aswkt(GEOMETRY) geom from test;')
with open(csvfile, 'w', newline='', encoding='utf-8') as f:
writer = csv.writer(f)
writer.writerow(['column1','column2', 'geom'])
writer.writerows(data)
conn.close()
this allowed me to read and convert the large file without having to add it to the workspace in QGIS.
answered 25 mins ago
Dror Bogin
1217
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
I claim the key problem is to build the table view resulting from your query, not the query itself. 'Extract by expression' algorithm in QGIS 3 is written as a native C++ algorithm and as such VERY FAST. In contrary to 'Select by expression' you don't generate a selection in QGIS 3 and then 'Save layer...' with 'Save only selected objects' enabled. 'Extract by expression' will save the query result directly to the format you desire which may take some time too as well, but you avoid creating a temporary table view in the attribute table or some query tool.
answered 2 hours ago
Jochen Schwarze
5,73731251
this was good and i did not know the tool, but i need to run a specific SQL query on the data and the python way solved it faster than rewriting the subset in the expression tool.
– Dror Bogin
23 mins ago
add a comment |Â
up vote
1
down vote
I claim the key problem is to build the table view resulting from your query, not the query itself. 'Extract by expression' algorithm in QGIS 3 is written as a native C++ algorithm and as such VERY FAST. In contrary to 'Select by expression' you don't generate a selection in QGIS 3 and then 'Save layer...' with 'Save only selected objects' enabled. 'Extract by expression' will save the query result directly to the format you desire which may take some time too as well, but you avoid creating a temporary table view in the attribute table or some query tool.
answered 2 hours ago
Jochen Schwarze
5,73731251
this was good and i did not know the tool, but i need to run a specific SQL query on the data and the python way solved it faster than rewriting the subset in the expression tool.
– Dror Bogin
23 mins ago
add a comment |Â
up vote
1
down vote
up vote
1
down vote
I claim the key problem is to build the table view resulting from your query, not the query itself. 'Extract by expression' algorithm in QGIS 3 is written as a native C++ algorithm and as such VERY FAST. In contrary to 'Select by expression' you don't generate a selection in QGIS 3 and then 'Save layer...' with 'Save only selected objects' enabled. 'Extract by expression' will save the query result directly to the format you desire which may take some time too as well, but you avoid creating a temporary table view in the attribute table or some query tool.
answered 2 hours ago
Jochen Schwarze
5,73731251
I claim the key problem is to build the table view resulting from your query, not the query itself. 'Extract by expression' algorithm in QGIS 3 is written as a native C++ algorithm and as such VERY FAST. In contrary to 'Select by expression' you don't generate a selection in QGIS 3 and then 'Save layer...' with 'Save only selected objects' enabled. 'Extract by expression' will save the query result directly to the format you desire which may take some time too as well, but you avoid creating a temporary table view in the attribute table or some query tool.
answered 2 hours ago
Jochen Schwarze
5,73731251
answered 2 hours ago
Jochen Schwarze
5,73731251
answered 2 hours ago
Jochen Schwarze
5,73731251
answered 2 hours ago
Jochen Schwarze
5,73731251
5,73731251
this was good and i did not know the tool, but i need to run a specific SQL query on the data and the python way solved it faster than rewriting the subset in the expression tool.
– Dror Bogin
23 mins ago
add a comment |Â
this was good and i did not know the tool, but i need to run a specific SQL query on the data and the python way solved it faster than rewriting the subset in the expression tool.
– Dror Bogin
23 mins ago
this was good and i did not know the tool, but i need to run a specific SQL query on the data and the python way solved it faster than rewriting the subset in the expression tool.
– Dror Bogin
23 mins ago
this was good and i did not know the tool, but i need to run a specific SQL query on the data and the python way solved it faster than rewriting the subset in the expression tool.
– Dror Bogin
23 mins ago
add a comment |Â
up vote
1
down vote
While @Jochen Schwarze's answer is good, it did not answer the question of how to perform an SQL query on the data.
since i don't have SQLite installed i used QGIS' python interpreter to read the data from the sqlite(spatialite, since geometry existed) file and write it to csv.
sqlite_file = 'path/to/test.sqlite'
csvfile = 'path/to/test.csv'
import sqlite3
import csv
conn = sqlite3.connect(sqlite_file)
conn.enable_load_extansion(True)
conn.execute('SELECT load_extension("mod_spatialite")')
conn.execute('SELECT InitSpatialMetaData(1);')
c = conn.cursor()
data = c.cursor('select column1, column2, aswkt(GEOMETRY) geom from test;')
with open(csvfile, 'w', newline='', encoding='utf-8') as f:
writer = csv.writer(f)
writer.writerow(['column1','column2', 'geom'])
writer.writerows(data)
conn.close()
this allowed me to read and convert the large file without having to add it to the workspace in QGIS.
answered 25 mins ago
Dror Bogin
1217
add a comment |Â
up vote
1
down vote
While @Jochen Schwarze's answer is good, it did not answer the question of how to perform an SQL query on the data.
since i don't have SQLite installed i used QGIS' python interpreter to read the data from the sqlite(spatialite, since geometry existed) file and write it to csv.
sqlite_file = 'path/to/test.sqlite'
csvfile = 'path/to/test.csv'
import sqlite3
import csv
conn = sqlite3.connect(sqlite_file)
conn.enable_load_extansion(True)
conn.execute('SELECT load_extension("mod_spatialite")')
conn.execute('SELECT InitSpatialMetaData(1);')
c = conn.cursor()
data = c.cursor('select column1, column2, aswkt(GEOMETRY) geom from test;')
with open(csvfile, 'w', newline='', encoding='utf-8') as f:
writer = csv.writer(f)
writer.writerow(['column1','column2', 'geom'])
writer.writerows(data)
conn.close()
this allowed me to read and convert the large file without having to add it to the workspace in QGIS.
answered 25 mins ago
Dror Bogin
1217
add a comment |Â
up vote
1
down vote
up vote
1
down vote
While @Jochen Schwarze's answer is good, it did not answer the question of how to perform an SQL query on the data.
since i don't have SQLite installed i used QGIS' python interpreter to read the data from the sqlite(spatialite, since geometry existed) file and write it to csv.
sqlite_file = 'path/to/test.sqlite'
csvfile = 'path/to/test.csv'
import sqlite3
import csv
conn = sqlite3.connect(sqlite_file)
conn.enable_load_extansion(True)
conn.execute('SELECT load_extension("mod_spatialite")')
conn.execute('SELECT InitSpatialMetaData(1);')
c = conn.cursor()
data = c.cursor('select column1, column2, aswkt(GEOMETRY) geom from test;')
with open(csvfile, 'w', newline='', encoding='utf-8') as f:
writer = csv.writer(f)
writer.writerow(['column1','column2', 'geom'])
writer.writerows(data)
conn.close()
this allowed me to read and convert the large file without having to add it to the workspace in QGIS.
answered 25 mins ago
Dror Bogin
1217
While @Jochen Schwarze's answer is good, it did not answer the question of how to perform an SQL query on the data.
since i don't have SQLite installed i used QGIS' python interpreter to read the data from the sqlite(spatialite, since geometry existed) file and write it to csv.
sqlite_file = 'path/to/test.sqlite'
csvfile = 'path/to/test.csv'
import sqlite3
import csv
conn = sqlite3.connect(sqlite_file)
conn.enable_load_extansion(True)
conn.execute('SELECT load_extension("mod_spatialite")')
conn.execute('SELECT InitSpatialMetaData(1);')
c = conn.cursor()
data = c.cursor('select column1, column2, aswkt(GEOMETRY) geom from test;')
with open(csvfile, 'w', newline='', encoding='utf-8') as f:
writer = csv.writer(f)
writer.writerow(['column1','column2', 'geom'])
writer.writerows(data)
conn.close()
this allowed me to read and convert the large file without having to add it to the workspace in QGIS.
answered 25 mins ago
Dror Bogin
1217
answered 25 mins ago
Dror Bogin
1217
answered 25 mins ago
Dror Bogin
1217
answered 25 mins ago
Dror Bogin
1217
1217
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f297875%2fsave-query-result-from-qgis-to-csv%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Did you you consider 'Extract by expression' processing algorithm from the processing toolbox and save its result directly to csv?
– Jochen Schwarze
2 hours ago
the file is too large to work with, that's why i'm using SQL. Other than exporting it to sqlite i couldn't do anything with the original layer. my problem is the fact that i don't have and can't install even sqlite itself on the machine that has to work with the data.
– Dror Bogin
2 hours ago