Help - import shapefile and/or layer in spatialite

[Alexander Palummo]

Hello everybody,

I'm just starting to learn pyQGIS and I'm looking for some suggestions about this branch of code:

https://github.com/ander2712/xander/blob/master/UploadInDB

I think there must be one or more errors, because it doesn't work...

Is anybody able to help me, somehow?

Thank you in any case.

Bye

[a.fu...@lqt.it]

Hi Alexander,

my personal skills about Python programming are very rudimentary,
anyway I notice a puzzling issue in that code:

> # creating/connecting SQL database object
> con = sqlite3.connect(DBname)
> # con = sqlite3.connect(":memory:") if you want write it in RAM
> con.enable_load_extension(True)
> cur = con.cursor()
>

all right, since now we've connected the database, and have
than enabled this connection to eventually load some dynamic
extension.


> # Initialise spatial db
> # Insert a control for existing table or not
> cur.execute("SELECT InitSpatialMetaData();")
>

immediately after we are trying to execute InitSpatialMetaData();
and I strongly suspects that the Python script will immediately
raise an error (the Python code seems to systematically ignore
any actual check after executing some SQL statement ... a very
dangerous and unsafe programming style).
and the same identical action is repeated yet again on the
immediately following instruction.

InitSpatialMetaData() is a SQL function requiring extended
support form the SpatiaLite extension; unhappily no action has
been taken since this point in the code so to dynamically
load the SpatiaLite extension module.
Consequently this SQL statement will surely fail.

suggestion #1: loading SpatiaLite as an extension is absolutely
required immediately after establishing the DB connection;
something like this:

con.enable_load_extension(True)
cur = con.cursor()
cur.execute("SELECT load_extension('mod_spatialite');")

suggestion #2: calling InitSpatialMetaData() with no
arguments will certainly work, but will run painfully slow.
always using the following form is strongly recommended:

SELECT InitSpatialMetaData(1);

bye Sandro

[Alexander Palummo]

2015-02-22 20:43 GMT+01:00 <a.fu...@lqt.it>:

Hi Alexander,

....

Hi Sandro,

first of all thank you for your fast response.

suggestion #1: loading SpatiaLite as an extension is absolutely
required immediately after establishing the DB connection;
something like this:

con.enable_load_extension(True)
cur = con.cursor()
cur.execute("SELECT load_extension('mod_spatialite');")

- suggestion #1:  In this form works

cur.execute("SELECT load_extension('libspatialite.so.5');") 

but I don't know really what I done ☺

suggestion #2: calling InitSpatialMetaData() with no
arguments will certainly work, but will run painfully slow.
always using the following form is strongly recommended:

SELECT InitSpatialMetaData(1);

- suggestion #2:  Yes now is better, fast like a computer not like a human (as before).

Bye

[Helo Worlld]

Hi,

I Use geotools libraty to import Shapefile in spatialite local db

File dataBaseFile = new File(project.getDir(), "localDB.db");
Map params = new HashMap();
params.put(SpatiaLiteDataStoreFactory.DBTYPE.key, "spatialite");
params.put(SpatiaLiteDataStoreFactory.DATABASE.key, dataBaseFile.getAbsolutePath());
DataStore datastore = new SpatiaLiteDataStoreFactory().createDataStore(params);
ShapefileDataStore shapeDS = new ShapefileDataStore(shape.toURI().toURL());
String type = shapeDS.getTypeNames()[0];
SimpleFeatureSource fs = shapeDS.getFeatureSource(type);
//creation schema
datastore.createSchema(fs.getSchema());
//add feature
SimpleFeatureStore store = (SimpleFeatureStore) datastore.getFeatureSource(type);
addFeatureInStore(store,fs);
//close
shapeDS.dispose();
datastore.dispose();



public void addFeatureInStore(SimpleFeatureStore store, SimpleFeatureSource fs) throws IOException {
        Transaction transaction = new DefaultTransaction("Add data");
        store.setTransaction(transaction);
        store.addFeatures(fs.getFeatures());
        transaction.commit();
        transaction.close();
    }