Bug report #13329

Error when trying to save to Sqlite a csv file with 1000 fields

Added by baditaflorin - about 5 years ago. Updated about 5 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/OGR
Affected QGIS version:2.8.3 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:21383

Description

Got another error when trying to save as SHP ( 256 columns limit )

when trying to save as a SQlite got the errors attached in the bug report
Export to vector file failed.
Error: Feature write errors:
Feature creation error (OGR error: In CreateFeature(): sqlite3_prepare(INSERT INTO 'amenity_report_database' ("country2","(multiple

bug_report_QGIS.txt Magnifier (3.2 MB) baditaflorin -, 2015-09-08 01:38 AM

History

#1 Updated by Saber Razmjooei about 5 years ago

  • Category set to Data Provider/OGR

Can you try and convert it from Osgeo4W shell using ogr2ogr?

#2 Updated by Saber Razmjooei about 5 years ago

  • Status changed from Open to Feedback

#3 Updated by Jukka Rahkonen about 5 years ago

The limit you hit is the SQLite limit "Maximum Number Of Host Parameters In A Single SQL Statement" which default to 999
http://www.sqlite.org/limits.html#max_variable_number

I believe it could be solved with some SQLite database settings. The next limit would be the maximum number of columns which defaults to 2000. About by-passing that limit from the same document:

"The default setting for SQLITE_MAX_COLUMN is 2000. You can change it at compile time to values as large as 32767. On the other hand, many experienced database designers will argue that a well-normalized database will never need more than 100 columns in a table."

#4 Updated by baditaflorin - about 5 years ago

  • Status changed from Feedback to Closed

I know that a normal database should look like that, but what i try to do it`s not a normal database setup.

I am not sure how to use ogr2ogr

I will try to generate the query from postgis in such a way that i will limit the excess columns. ( i am generating a list with all the POI that exists in OpenStreetMap, for all of the world )

I will make a count filter so that i will get to have only the TOP 200 values, by number of POI

Thanks for the quick answer

#5 Updated by Jukka Rahkonen about 5 years ago

I think you should consider to put just POI geometries and IDs into the main table and tags into another table joined with a foreign key. I know QGIS is made for a flat schema but it does have support for relations as well. Unfortunately I have never played with them yet.

Also available in: Atom PDF