# Have I created a valid Spatialite database?

I didn't manage to find the better way to populate the exisiting Spatialite database with new computed data than just creating new columns and filling them in the Python script.

You can see that the script is very straightforward:

#!/usr/bin/env python3

import json
import sqlite3
import shutil
import pprint

dbase = "./states.sqlite"
input_data = "./Real_GDP.trends"

#Add to the attribute table 'states' numerous columns with a FLOAT numbers
alterate_db = """\
ALTER TABLE states\
ADD COLUMN '{0}' REAL DEFAULT 0.0;\
"""
#Fill the cells in new columns with a new data
update_db = """\
UPDATE states SET '{0}'={1} WHERE state_name='{2}';
"""

#Function used for adding the new columns in a loop
industries = []
for state, d in data.items():
for code, _ in d.items():
if code not in industries:
industries.append(code)
pprint.pprint(industries)
con = sqlite3.connect(dbase)
cur = con.cursor()
#Every new column will match the industry code
for i in industries:
try:
cur.execute(alterate_db.format(i))
con.commit()
except Exception as e:
print("Exception: {0}".format(e))
else:
pass
cur.close()
con.close()

#Function used for filling the DB with a new data
def update_table(data):
con = sqlite3.connect(dbase)
cur = con.cursor()
counter = 0
for state, d in data.items():
for code, values in d.items():
if (values["p_value"] >= float(0.05)):
trend = values["slope"]
else:
trend = 0.0
try:
cur.execute(update_db.format(code, trend, state))
con.commit()
except Exception as e:
print("Exception: {0}".format(e))
else:
counter += 1
if (counter % 100 == 0):
print(counter)
cur.close()
con.close()

def main():
#Getting the working copy of database
shutil.copy2(dbase + ".backup", dbase)
with open(input_data, "r") as f:
update_table(data)

if __name__ == "__main__":
main()


The file that I finally get seem to be a valid Spatialite database. It can be viewed and accessed with a various tools (sqliteman, spatialite-gui, qgis and qbrowser). It's attribute table seems to be OK too. The only problem is that the data from new columns cannot be rendered (in QGis 2.2).

This map was rendered from data extracted from the one of the old columns:

But I can see nothing but empty layout when I try to render map from any of newly added columns:

Since the QGis 2.2 is working fine, I guess that my problem was caused by the wrong methods of Spatialite DB editing (from my Python script). I would be glad if someone could point at the mistakes in the code above.

P.S. It is unlikely, but still possible that someone can check the files that I work with:

• Real_GDP.trends - JSON file, the source of new data;
• states.sqlite.backup - the initial Spatialite DB (fully operational);
• states.sqlite - the resulting Spatialite DB populated with new data (partly inoperative: new columns are cannot be drawn);

From that page:

Please note well: this one is a very frequent pitfall. Many developers, GIS professionals and alike obviously feel to be much smarter than this, so they often tend to invent some highly imaginative alternative way to create their own Geometries. e.g. bungling someway the geometry_columns table seems to be a very popular practice.

May well be that such creative methods will actually work with some very specific SpatiaLite's version; but for sure some severe incompatibility will raise before or after ...

Be warned: only Geometries created using AddGeometryColumn() are fully legitimate. Any different approach is completely unsafe (and unsupported ..)

There are various triggers that are created (in addition to the work in the geometry_columns table) when AddGeometryColumn is used. Please don't work around it.

• thanks for you reply! But I did not add new geometry, I just tried to add new columns to the existing geometries. – Vitaly Isaev Apr 22 '14 at 7:22

After adding a new column you need to execute the following command to update spatialites additional column information table.

SELECT UpdateLayerStatistics('geometry_table_name');