I have this python script which allows me to convert delimited text file to spatialite . I used a loop to export attributes table to the output file and it takes over 2 hours when I execute the code for big files (for example 100 000 records in csv or txt file). Is there any other and more efficient way to do that?

from qgis.core import *
import ogr
import csv
import os
import sys
import time
import datetime
from getpass import getuser
from osgeo import osr
import qgis.utils
from PyQt4.QtCore import *
from PyQt4.QtGui import * 




app = QApplication([],True)
QgsApplication.setPrefixPath("C:/Program Files (x86)/QGIS 2.18/apps/qgis", T 
rue)
QgsApplication.initQgis()

sys.path.append(sys.path.append('C:/Program Files (x86)/QGIS 2.18/apps/qgis/python/plugins'))
from processing.core.Processing import Processing

Processing.initialize()



Input_Table = 'file:///source'+str(sys.argv[1])+'?encoding=System&type=csv&delimiter=%5Ct;&xField=wsp_geo_x&yField=wsp_geo_y&spatialIndex=yes&subsetIndex=no&watchFile=no' 


lon_field = 'wsp_geo_x' 
lat_field = 'wsp_geo_y' 


crs = 4326


Output_Layer =  'C:/dst'+str(sys.argv[1])+'.sqlite'


spatRef = QgsCoordinateReferenceSystem(4326,QgsCoordinateReferenceSystem.EpsgCrsId)
inp_tab = QgsVectorLayer(Input_Table ,'Input_Table', 'delimitedtext')
fields = inp_tab.pendingFields()
prov = inp_tab.dataProvider()
pt = QgsPoint()
outFeature = QgsFeature()



outLayer = QgsVectorFileWriter(Output_Layer, "UTF-8", fields, QGis.WKBPoint, 
spatRef,"SQLite",["SPATIALITE=YES"])



for feat in inp_tab.getFeatures() :


    attrs = feat.attributes()
    pt.setX(float(feat[lon_field]))

    pt.setY(float(feat[lat_field]))

    outFeature.setAttributes(attrs)

    outFeature.setGeometry(QgsGeometry.fromPoint(pt))

    outLayer.addFeature(outFeature)
  • The code you attached can't be reproduced because it seems there are some missing parts in it.So, maybe next time you should at least copy the whole code from the original source, i.e. this one. – mgri Jul 19 '17 at 13:59
  • i just attatched the part i am worried about, my mistake . I'll edit it right now – Maciej Szczypek Jul 19 '17 at 14:07
  • Moreover, I suggest to quantify what does takes ages means for you because the dataset I used in my example took more or less 7 seconds to run for 40k points about (I admit it, not the best solution but certainly not an age). It is not much appreciated receiving this kind of critiques when you didn't anything for solving the issue on your own. – mgri Jul 19 '17 at 14:10
  • ogr2ogr -f SQLite -oo X_POSSIBLE_NAMES=wsp_geo_x -oo Y_POSSIBLE_NAMES=wsp_geo_y -oo KEEP_GEOM_COLUMNS=NO in.csv out.sqlite – RoVo Jul 19 '17 at 14:18
  • Why are you re-creating point features in the for loop when the points are already created in this statement, inp_tab = QgsVectorLayer(Input_Table ,'Input_Table', 'delimitedtext') – artwork21 Jul 19 '17 at 14:39

It looks like you are missing the input parameter (inp_tab) on the QgsVectorFileWriter.writeAsVectorFormat() statement:

outLayer = QgsVectorFileWriter(inp_tab, Output_Layer, "UTF-8", fields, QGis.WKBPoint, spatRef,"SQLite",["SPATIALITE=YES"])

This will allow you to not have to use the for loop for adding features. They should be brought over from delimited text layer to sqlite/spatialite table.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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