1

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.

  • Did you you consider 'Extract by expression' processing algorithm from the processing toolbox and save its result directly to csv? – Jochen Schwarze Oct 4 '18 at 6:19
  • 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 Oct 4 '18 at 6:22
1

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.

  • 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 Oct 4 '18 at 8:32
1

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.

  • Did you consider using sth like sqlite manager add on for Mozilla Firefox? With this you would not need the python interpreter or qgis at all anyway. – Jochen Schwarze Oct 6 '18 at 7:58
  • 1
    That would be problematic, as i have stated in my comment, i can't install new software on the machine since it's closed network. also i used QGIS to convert another, larger csv file to sqlite so i could work with it. – Dror Bogin Oct 7 '18 at 5:33

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.