Qspatialite: new qgis plugin for spatialite users

981 views
Skip to first unread message

romain

unread,
Mar 20, 2011, 3:03:04 PM3/20/11
to SpatiaLite Users
Hi,

Here you can download the first *experimental* version, for testing
and debugging purpose:

http://dl.free.fr/k554xkFf1

To test it, just unzip the file into your qgis plugin folder
I'm not a professional programmer and it's my first QGIS plugin, so
coding might be 'dirty', sorry.

It's a sort of "customDbQuery", with new user interface and new
features:
- Load QGIS vectorLayer into DB
- Load DBF File
- describe / drop table
-vacuum DB


Work is still in progress, but, I think your advices/tests might be
usefull !

Bye,

romain

unread,
Mar 24, 2011, 12:56:33 PM3/24/11
to SpatiaLite Users
Hi everyone,

The *final* version of my plugin have been uploaded to QGIS plugin
repository.
You can get it with QGIS plugin installer !

Name: QSpatiaLite
Features:
- SQL syntax highlighter (+ Spatialite functions)
- Load query/table in QGIS Map Canvas
- import QGIS Layer
- import DBF Table
- inspect/drop tables
- vacuum database
- ...

Feel free to test it and post bugs here please,
Enjoy !

Paulo

unread,
Mar 25, 2011, 6:30:21 AM3/25/11
to SpatiaLite Users
Great tool,

One question though, uploading a layer in QGIS to the spatialite
database only seems to upload the attribute table, no associated
geometry table. There is a geometry column, type BLOB, but when trying
to open the imported table in QGIS, I get the message: This is not a
Geometry Table, can not import in QGIS.

Cheers,

Paulo

romain

unread,
Mar 25, 2011, 12:56:07 PM3/25/11
to SpatiaLite Users
Oh yes,

This seems weird: it works very well if you export "select * from
mytable" to QGIS from Query Composer...

For Sandro:
Using python, it seems that recovergeometrycolumn add geometry column
to system tables, but PRAGMA table_info() still show BLOB for geometry
columns...
How can I fix that ?

romain

unread,
Mar 25, 2011, 1:45:02 PM3/25/11
to SpatiaLite Users
Okay, it is fixed, I'll upload a new version tonight.

a.fu...@lqt.it

unread,
Mar 25, 2011, 1:51:58 PM3/25/11
to spatiali...@googlegroups.com
On Fri, 25 Mar 2011 09:56:07 -0700 (PDT), romain wrote

> For Sandro:
> Using python, it seems that recovergeometrycolumn add geometry column
> to system tables, but PRAGMA table_info() still show BLOB for
> geometry columns... How can I fix that ?
>

there is no way to fix: once you've initially created
any column, SQLite doesn't allows to change *NOTHING*:
http://www.sqlite.org/lang_altertable.html
the unique operation supported by SQLite simpy is:
ALTER TABLE ... ADD COLUMN ...

please note: for SQLite data-types are absolutely
meaningless :D
the following snipper explains better what I mean:

CREATE TABLE abc (
a potato NOT NULL,
b tomato NOT NULL,
c green_salad NOT NULL);

INSERT INTO abc (a, b, c)
VALUES (1, 'a', 1.1);

INSERT INTO abc (a, b, c)
VALUES (2, 'b', 2.2);

SELECT a, b, c FROM abc;

SELECT TypeOf(a), TypeOf(b),
TypeOf(c) FROM abc;

PRAGMA table_info(abc);

so don't bother about this; all this is
absolutely *not* relevant for SQLite

bye Sandro

romain

unread,
Mar 25, 2011, 2:35:08 PM3/25/11
to SpatiaLite Users
Ok, thank you for your explanation!

Paulo

unread,
Mar 26, 2011, 9:13:27 AM3/26/11
to spatiali...@googlegroups.com, romain
That is quick, thanks.

I have a different problem now, when attempting to upload a vector map, I am getting the following error message:
An error has occured while executing Python code:

-----------
Traceback (most recent call last):
  File "/home/paulo/.qgis/python/plugins/QspatiaLite/qspatialitedialog.py", line 606, in upload_qgis_layers
    cursor.execute(vtsql)
IntegrityError: white.Geometry violates Geometry constraint [geom-type or SRID not allowed]

Python version:
2.6.6 (r266:84292, Sep 15 2010, 16:41:53)
[GCC 4.4.5]


QGIS version:
1.7.0-Trunk Trunk, 15385

Python path: ['/usr/local/qgis_trunk/share/qgis/python', '/home/paulo/.qgis/python', '/home/paulo/.qgis/python/plugins', '/usr/local/qgis_trunk/share/qgis/python/plugins', '/usr/local/lib/python2.6/dist-packages/GDAL-1.7.3-py2.6-linux-x86_64.egg', '/usr/lib/python2.6', '/usr/lib/python2.6/plat-linux2', '/usr/lib/python2.6/lib-tk', '/usr/lib/python2.6/lib-old', '/usr/lib/python2.6/lib-dynload', '/usr/local/lib/python2.6/dist-packages', '/usr/lib/python2.6/dist-packages', '/usr/lib/python2.6/dist-packages/PIL', '/usr/lib/python2.6/dist-packages/gst-0.10', '/usr/lib/pymodules/python2.6', '/usr/lib/python2.6/dist-packages/gtk-2.0', '/usr/lib/pymodules/python2.6/gtk-2.0', '/usr/lib/python2.6/dist-packages/wx-2.8-gtk2-unicode', '/home/paulo/.qgis/python/plugins/fTools/tools', '/home/paulo/.qgis/python/plugins/ziplayers/logic', '/home/paulo/.qgis/python/plugins/ziplayers/gui', '/home/paulo/.qgis/python/plugins/openlayersov/logic', '/home/paulo/.qgis/python/plugins/openlayersov/gui', '/home/paulo/.qgis/python/plugins/mmqgis/forms', '~/.qgis/python']


romain riviere

unread,
Mar 26, 2011, 11:23:47 AM3/26/11
to spatiali...@googlegroups.com
Hi, 

Thanks for your feedback. 
Did you get this error trying to import a shp "polygon" as "point", or is the geometry type correct ?
I'll fixe that soon: a dialog will replace python error when geometry type isn't correct.


Romain,




2011/3/26 Paulo <p.vanb...@gmail.com>

Paulo

unread,
Mar 26, 2011, 11:47:42 AM3/26/11
to spatiali...@googlegroups.com, romain riviere
I tried to import a polygon layer as a polygon, i.e., the geometry type is correct.

romain riviere

unread,
Mar 26, 2011, 12:31:51 PM3/26/11
to spatiali...@googlegroups.com
Ok, I think your polygons may be not valid, so recovergeometrycolumn() doesn't work.
I've uploaded a new version that upload the layer even if geometry is weird, but without recovering geometry column.

Romain,




2011/3/26 Paulo <p.vanb...@gmail.com>

Paulo

unread,
Mar 26, 2011, 3:01:15 PM3/26/11
to spatiali...@googlegroups.com, romain riviere
Hi Romain

Unfortunately that doesn't work. I get the new message that it "Can't create geometry column: INCORRECT GEOMETRY TYPE". When clicking OK, the attribute table is loaded, but no geometry info.

The strange thing is, I can load the same shapefil in a spatial database using the Spatialite-gui (version 1.40) and subsequently open it in QGIS without problems. I also tried it with a newly created and very simple (one polygon only) shapefile, but no luck.

Paulo

a.furieri

unread,
Mar 26, 2011, 3:35:11 PM3/26/11
to SpatiaLite Users
Hi Paulo and Romain, just a hint:

are you actually creating a POLYGON or a MULTIPOLYGON
Geometry column ?

Shapefiles are insidious: they invariably presents
themselves as 'polygons' or 'polylines' layers:
but this is absolutely misleading in standard OGC terms.

a Shapefile of the 'polygon' type usually contains
an ugly mix of both POLYGON and MULTIPOLYGON
geometries (or LINESTRING and MULTILINESTRING
for 'polyline' ones).

And obviously SpatiaLite will raise a constraint
violation when you attempt to insert a MULTIPOLYGON
into a Geometry column declared to be of the POLYGON
type. The opposite too will raise an exception, i.e.
attempting to insert a POLYGON into a MULTIPOLYGON
Geometry column.

---------

Simple solution (the same adopted internally by
.loadshp and alike):

Pass #1: read first the whole shapefile, so to count how
many 'single' and 'multi' geometries are actually stored
withing the SHP.

Pass #2-A: if you've found uniquely 'single' geometries,
there is very little more to say.

Pass #2-B: but if you've found one or more 'multi' geometries,
the following actions are required:
- you must create a Geometry column of the type MULTILINESTRING
(or MULTIPOLYGON)
- then you must invoke CastToMulti(geom) for each Geometry to be
inserted:
* if already this is of the MULTI-xxx type, no action
is performed at all
* but when a 'single' Geometry is found, then this one will be
transformed into the corresponding MULTI-xxx type

Please note well: this is absolutely not required for POINT
and MULTIPOINT. Shapefiles cannot physically store both at
the same time; so in this case you can safely trust the
'label' declaration.

bye Sandro

Paulo

unread,
Mar 26, 2011, 4:35:47 PM3/26/11
to spatiali...@googlegroups.com, a.furieri
Hi Sandro

I wouldn't be suprised if some of the layers I tried to upload were a mix of both POLYGON and MULTIPOLYGON geometries. However, I also tried it with a shapefile I just created for this purpose in QGIS and which consists of one polygon only. Although I guess we can assume that to be a layer with POLYGON geometry (?), it gave the same error message.

Cheers,

Paulo

romain

unread,
Mar 27, 2011, 2:04:41 AM3/27/11
to SpatiaLite Users
Hi, thismake sense to me now Paulo

In QSpatiaLite, when you choose POLYGON, in fact the shapefile is
imported like a MULTIPOLYGON...I tought it would resolve any problems,
but Sandro is absolutly right.

I'll fix that before Monday.

Paulo

unread,
Mar 27, 2011, 5:19:31 AM3/27/11
to spatiali...@googlegroups.com, romain
Great, thanks Romain and Sandro, looking forward to the update :-)

romain

unread,
Mar 27, 2011, 1:22:13 PM3/27/11
to SpatiaLite Users
Hi,

Within QGIS, even if my Spatialite version is 2.4.0 .... multi() and
casttomulti() doesn't exist...don't know how to do in this case...
perhaps with WKB....

a.furieri

unread,
Mar 27, 2011, 1:47:40 PM3/27/11
to SpatiaLite Users
Hi Romain,

I strongly suspect you are using an outdated QGIS;
or some old version of the pyspatialite connector.

on my own pc (win7) using the latest 'nightly build' QGIS
downloaded from OSGeo4W CastToMulti() is fully
supported (tested on the QGIS internal Python console).

If I remember well, there was an issue in any previous
QGIS version, because the pyspatialite connector
was always built on the top of the obsolete splite v.2.3.1,
even when the QGIS data provider used a most recent
version.

But (at least on QGIS-trunk) AFAIK now the pyspatialite
connector shipped within QGIS surely uses the same identical
spatialite's version used by the QGIS data provider.

Anyway you can easily check which spatialite version is actually
supported by your pyspatialite, simply excuting (from Python):
SELECT spatialite_version()

And if you are working on Linux, this too can help you (maybe ...):
http://www.gaia-gis.it/spatialite-2.4.0-4/spatialite-cookbook/html/python.html

bye, Sandro





romain

unread,
Mar 28, 2011, 1:47:55 AM3/28/11
to SpatiaLite Users
Hi,

The issue should be resolve paulo. I've uploaded a new version.

I'm on ubuntu 10.04 Netbook remix, with QGIS 1.6 from unstable ppa
(distribution package). Pyspatialite also install from distribution
package.
All functions with st_ , or rtreeintersects, etc ... doesn't work
within QGIS.
SpatiaLite version is 2.4.0 in qgis python console, not 2.3.
I'm pretty sure it's because it's not 2.4.0-RC4, I'll try to build a
new version of pyspatialite tonight,
Thanks Sandro

Bye
> And if you are working on Linux, this too can help you (maybe ...):http://www.gaia-gis.it/spatialite-2.4.0-4/spatialite-cookbook/html/py...
>
> bye, Sandro

romain

unread,
Mar 28, 2011, 6:30:09 AM3/28/11
to SpatiaLite Users
Hi everyone,

New QSpatiaLite version 0.7 is able in QGIS repository:
- fix qgis layer import issue
- syntax highlighter improved
- select SRID search button
- GUI: windows are rezisable

Now, it should be quite stable and up to work,
Please let me know if something doesn't work,
I'll try to fix it as soon as possible.

romain

unread,
Mar 28, 2011, 7:30:13 AM3/28/11
to SpatiaLite Users
Just a little question: are you able to see icons next to table
names ?

Vladimir Vyskocil

unread,
Mar 28, 2011, 9:22:00 AM3/28/11
to spatiali...@googlegroups.com
On MacOS SnowLeopard, with QGis 1.7 r15622 I get this error when trying to lauch this plugin :

Traceback (most recent call last):

File "/Users/vlad/.qgis/python/plugins/QspatiaLite/qspatialite.py", line 56, in run
dlg = QspatiaLiteDialog(self.load_to_canvas,self.get_layer_names,self.save_layer, self.iface.mainWindow())
File "/Users/vlad/.qgis/python/plugins/QspatiaLite/qspatialitedialog.py", line 62, in __init__
self.prepare_tree()
File "/Users/vlad/.qgis/python/plugins/QspatiaLite/qspatialitedialog.py", line 214, in prepare_tree
tableList = self.get_tables() # for each table: tablename, reltype, geom_col, geom_type, geom_dim, geom_srid
File "/Users/vlad/.qgis/python/plugins/QspatiaLite/qspatialitedialog.py", line 365, in get_tables
self.connection = self.connect_db(connectionSettings)
File "/Users/vlad/.qgis/python/plugins/QspatiaLite/qspatialitedialog.py", line 421, in connect_db
connectionObj = self.connect_to_spatialite(connectionSettings)
File "/Users/vlad/.qgis/python/plugins/QspatiaLite/qspatialitedialog.py", line 432, in connect_to_spatialite
return sqlite.connect(connectionSettings["sqlitepath"])
KeyError: 'sqlitepath'

Python version:
2.6.1 (r261:67515, Jun 24 2010, 21:47:49)
[GCC 4.2.1 (Apple Inc. build 5646)]

> --
> You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
> To post to this group, send email to spatiali...@googlegroups.com.
> To unsubscribe from this group, send email to spatialite-use...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/spatialite-users?hl=en.
>

romain

unread,
Mar 28, 2011, 10:45:45 AM3/28/11
to SpatiaLite Users
Ok, I'll try to fix that soon ...
Apparently, QGIS1.7 seems to manage spatialite differently , Am I
wrong ?

romain

unread,
Mar 28, 2011, 10:54:47 AM3/28/11
to SpatiaLite Users
Ok, I think it's might work for you know Vladimir ( remove/reinstall
Qspatialite from QGIS Server).
In fact MAC OS seems to care about word case .. SpatiaLite !=
spatialite

Vladimir Vyskocil

unread,
Mar 28, 2011, 11:06:12 AM3/28/11
to spatiali...@googlegroups.com
Yes it works now, thank you very much !
However, the labels and text fields at the right above Run Query are almost unreadables (too small), even if I resize the window.

Vlad.

Dan Putler

unread,
Mar 28, 2011, 1:16:22 PM3/28/11
to spatiali...@googlegroups.com
Hi romain,

I just installed the latest version of the Qspatialite plugin (0.8) and this is the warning message QGIS returns:

The plugin seems to have been installed but I don't know where. Probably the plugin package contained a wrong named directory.
Please search the list of installed plugins. I'm nearly sure you'll find the plugin there, but I just can't determine which of them it is. It also means that I won't be able to determine if this plugin is installed and inform you about available updates. However the plugin may work. Please contact the plugin author and submit this issue.

I'm on Ubuntu 10.04 64bit. If I look in my .qgis/python/plugins directory, the Qspatialite directory is there. I think this is a letter case issue. The QGIS manager indicates that the plugin's name is QSpatiaLite, while its directory is Qspatialite, and when the installer can't find a QSpatiaLite directory, it issues the warning.

Dan

--- On Sun, 3/27/11, romain <romain.ri...@gmail.com> wrote:

romain

unread,
Mar 28, 2011, 1:25:08 PM3/28/11
to SpatiaLite Users
thank you for feedback Dan,

This is because I've uploaded a zip with a different name
(QSpatialitev8) from the plugin.
Take a look at version 0.9, it should work properly !

New features:
- SQL query history
- Elapsed Time

On 28 mar, 21:16, Dan Putler <put...@yahoo.com> wrote:
> Hi romain,
>
> I just installed the latest version of the Qspatialite plugin (0.8) and this is the warning message QGIS returns:
>
> The plugin seems to have been installed but I don't know where. Probably the plugin package contained a wrong named directory.
> Please search the list of installed plugins. I'm nearly sure you'll find the plugin there, but I just can't determine which of them it is. It also means that I won't be able to determine if this plugin is installed and inform you about available updates. However the plugin may work. Please contact the plugin author and submit this issue.
>
> I'm on Ubuntu 10.04 64bit. If I look in my .qgis/python/plugins directory, the Qspatialite directory is there. I think this is a letter case issue. The QGIS manager indicates that the plugin's name is QSpatiaLite, while its directory is Qspatialite, and when the installer can't find a QSpatiaLite directory, it issues the warning.
>
> Dan
>
> --- On Sun, 3/27/11, romain <romain.riviere....@gmail.com> wrote:

Paulo

unread,
Mar 28, 2011, 2:20:45 PM3/28/11
to spatiali...@googlegroups.com, romain
I am impressed with the speed you have corrected and improved things.. it works perfectly now (admittedly with some limited testing only). Thanks, this was really something missing from QGIS in my opinion.

If I may make one suggestion though, it might be nice to add a close button. I know it is equally easy to close it with the X button in the upper left (or right, depending on your OS) corner, but my fingers seem to expect a close button at the bottom. So I have been hitting the help button a few times already :-/

But that is really a very minor thing, overall, great tool!

christian

unread,
Mar 28, 2011, 7:37:29 PM3/28/11
to SpatiaLite Users
Hi Romain,

I have tried some versions of your plugin but It never works for me...

I always get back an error and your plugin doesn't fire up.

I work with Qgis 1.6 on Windows xp home edition sp 2.

I have installed Qgis 1.6 by using the standalone installer (http://
www.qgis.org/wiki/Download#Standalone_Installer_.28recommended_for_new_users.29)

Here's my error:
Errore durante l'esecuzione di codice Python:

Traceback (most recent call last):
File "C:/Documents and Settings/pc/.qgis/python/plugins\QspatiaLite
\qspatialite.py", line 56, in run
dlg =
QspatiaLiteDialog(self.load_to_canvas,self.get_layer_names,self.save_layer,
self.iface.mainWindow())
File "C:/Documents and Settings/pc/.qgis/python/plugins\QspatiaLite
\qspatialitedialog.py", line 62, in __init__
self.prepare_tree()
File "C:/Documents and Settings/pc/.qgis/python/plugins\QspatiaLite
\qspatialitedialog.py", line 240, in prepare_tree
tableList = self.get_tables() # for each table: tablename,
reltype, geom_col, geom_type, geom_dim, geom_srid
File "C:/Documents and Settings/pc/.qgis/python/plugins\QspatiaLite
\qspatialitedialog.py", line 402, in get_tables
self.connection = self.connect_db(connectionSettings)
File "C:/Documents and Settings/pc/.qgis/python/plugins\QspatiaLite
\qspatialitedialog.py", line 457, in connect_db
connectionObj = self.connect_to_spatialite(connectionSettings)
File "C:/Documents and Settings/pc/.qgis/python/plugins\QspatiaLite
\qspatialitedialog.py", line 468, in connect_to_spatialite
return sqlite.connect(connectionSettings["sqlitepath"])
OperationalError: unable to open database file

Versione Python:
2.5.2 (r252:60911, Feb 21 2008, 13:11:45) [MSC v.1310 32 bit (Intel)]


Versione di QGIS:
1.6.0-Capiapo Capiapo, exported

Percorso Python: ['C:/PROGRA~1/QUANTU~1/apps/qgis/./python', 'C:/
Documents and Settings/pc/.qgis/python', 'C:/Documents and Settings/
pc/.qgis/python/plugins', 'C:/PROGRA~1/QUANTU~1/apps/qgis/./python/
plugins', 'C:\\PROGRA~1\\QUANTU~1\\apps\\gdal-17\\pymod', 'C:\
\Programmi\\Quantum GIS Copiapo', 'C:\\PROGRA~1\\QUANTU~1\\bin\
\python25.zip', 'C:\\PROGRA~1\\QUANTU~1\\apps\\Python25\\DLLs', 'C:\
\PROGRA~1\\QUANTU~1\\apps\\Python25\\lib', 'C:\\PROGRA~1\\QUANTU~1\
\apps\\Python25\\lib\\plat-win', 'C:\\PROGRA~1\\QUANTU~1\\apps\
\Python25\\lib\\lib-tk', 'C:\\PROGRA~1\\QUANTU~1\\apps\\qgis\\bin', 'C:
\\PROGRA~1\\QUANTU~1\\apps\\Python25', 'C:\\PROGRA~1\\QUANTU~1\\apps\
\Python25\\lib\\site-packages', 'C:\\PROGRA~1\\QUANTU~1\\apps\
\Python25\\lib\\site-packages\\win32', 'C:\\PROGRA~1\\QUANTU~1\\apps\
\Python25\\lib\\site-packages\\win32\\lib', 'C:\\PROGRA~1\\QUANTU~1\
\apps\\Python25\\lib\\site-packages\\Pythonwin', 'C:\\PROGRA~1\
\QUANTU~1\\apps\\Python25\\lib\\site-packages\\wx-2.8-msw-unicode', 'C:
\\PROGRA~1\\QUANTU~1\\apps\\qgis\\python\\plugins\\fTools\\tools', 'C:\
\Documents and Settings\\pc\\.qgis\\python\\plugins\\imgshowhide\
\logic', 'C:\\Documents and Settings\\pc\\.qgis\\python\\plugins\
\imgshowhide\\gui', 'C:\\Documents and Settings\\pc\\.qgis\\python\
\plugins\\fTools\\tools']

romain

unread,
Mar 29, 2011, 3:31:36 PM3/29/11
to SpatiaLite Users
Big work done today,
The new version (1.1) is now released:
- contextual menu on Treeview
- more like spatialite-gui

Hi christian,
Still working on your issue...

On 29 mar, 03:37, christian <canselm...@gmail.com> wrote:
> Hi Romain,
>
> I have tried some versions of your plugin but It never works for me...
>
> I always get back an error and your plugin doesn't fire up.
>
> I work with Qgis 1.6 on Windows xp home edition sp 2.
>
> I have installed Qgis 1.6 by using the standalone installer (http://www.qgis.org/wiki/Download#Standalone_Installer_.28recommended_for_ne...)

christian

unread,
Mar 30, 2011, 3:47:56 PM3/30/11
to SpatiaLite Users
Hi Romain,

> Still working on your issue...

I have just downloaded the latest version of your plugin (v. 1.3) and
now *everything* works as expected!

THANKS a bunch indeed for your great work ;-)

Christian

romain

unread,
Mar 31, 2011, 3:21:55 PM3/31/11
to SpatiaLite Users
New version 1.6:
- speed improvement
- Load TXT/CSV
- compatibile for linux, windows, mac

Feel free to test, comment, and make suggestions.

Bye,

romain

unread,
Apr 4, 2011, 1:32:23 PM4/4/11
to SpatiaLite Users
First Stable version released tonight: V2.1

- manage SpatiaLite DB (create/rename table, views, recover geometry
column, ....)
- Import QGIS Layer (SHP,TAB,GRASS,POSTGIS,.....everything supported
by QGIS)
- Import DBF/TXT/CSV - Export CSV/TXT (table, view and resultset)
- Export query result into QGIS mapCanvas
- SQL syntax highlighter + function list + columns unique values

Hope it will help people to use SpatiaLite.

To see what it looks like:
http://www.imagup.com/data/1116603743.html

To test it:
Install QSpatialite plugin from QGIS repository (require PySpatiaLite
python plugin)

romain

unread,
Apr 11, 2011, 7:35:28 AM4/11/11
to SpatiaLite Users
*New version available* on QGIS python plugin repository: (QSpatiaLite
V2.9)

- New look, more intuitive
- Highlighting & autocompletion for SQLeditor
- New *Query Builder*, inspired by MapInfo SQL Query Panel (+ query
column's unique values & spatial index)

Enjoy,

I'm still working on it

Paulo

unread,
Apr 16, 2011, 4:08:23 PM4/16/11
to spatiali...@googlegroups.com
Hi Romain,

Just a suggestion/question: How difficult would it be to implement the option to create a new Spatialite database (and automatically create a connection in QGIS to this database)?

Paulo

romain

unread,
Apr 17, 2011, 2:06:09 PM4/17/11
to SpatiaLite Users
Hi,
I've not a lot of time right now, but i'll keep your idea in mind. I
think it should be possible.

romain

unread,
May 2, 2011, 4:44:28 AM5/2/11
to SpatiaLite Users
New version Uploaded today: v3.3
- create new empty spatialite db / load and existing one
- automaticly save query builder contents when closed

I'm actually abroad, and will not be able to work on SpatiaLite as
often as I use to.

Enjoy,
And feel free to report any bug/suggestions

romain

unread,
May 22, 2011, 5:26:57 PM5/22/11
to SpatiaLite Users
Be carefull,

With spatialite 2.3.1 ( default in QGIS 1.6), there are some bugs:
- Creating a new DB leads to an error and recovergeometrycolumn()
doesn't work( for Alessandro: select initspatialmetadata() doesn't
seems to work for 2.3 within python....whereas it's ok for 2.4....very
weird)
- Importing a table with an existing field PKUID will lead to an error
( for Alessandro: this is due to the use of 'virtualtable'). Work fine
with 2.4
- Right clic menu doesn't work very well: some functions are missing

Anyway, the plugin still works well for basic use.

I'm trying to resolve those pb, but I don't understand why they
doesn't work fine with 2.3. The SQL queries used don't contain any 2.4
functions and should be working on both versions...

So, I think every QSpatialite user may try to update PYSPATIALITE, as
described here:
http://www.gaia-gis.it/spatialite-2.4.0-4/splite-python.html

It's very easy for UNIX users, but less for windows'ones...

Best Regards,
and thanks all for feedback

a.furieri

unread,
May 23, 2011, 6:14:06 AM5/23/11
to SpatiaLite Users
Hi Romain,

this is a well known general issue:
oldest QGIS versions deployed two different
libspatialite copies: one supporting the main
core (v.2.4), the other supporting python
(v.2.3.1).
such odd and impaired condition caused several
inconsistencies, because v.2.3.1 and v.2.4 have
quite different implementations of library functions
supporting TRIGGERs.

AFAIK all this has been definitively solved
in QGIS-trunk and QGIS v.1.7: now QGIS always
take care to deploy an unique libspatialite
copy, so to ensure a full consistent state.

bye Sandro

romain

unread,
May 23, 2011, 9:15:50 PM5/23/11
to SpatiaLite Users
Thanks,
That's what I taught.
Hope they will release v1.7 soon.

romain

unread,
Jun 1, 2011, 7:29:41 PM6/1/11
to SpatiaLite Users
QSpatiaLite V3.6 is now available. It now allows to import any QGIS
Layer without using virtualshape.
So even MAPINFO Files with long fields'name (>10 characters) can be
uploaded.

I've also created a google code project for QSpatiaLite:
http://code.google.com/p/qspatialite/

Feel Free to come and see. And Ask me if you want to help me for the
Documentation (WIKI)
You can also use this site to report any bug, suggestions, etc...

Next version:
- import a whole directory in QGIS
- improve queryBuilder
- and much more ...

Noli Sicad

unread,
Jun 1, 2011, 7:46:37 PM6/1/11
to spatiali...@googlegroups.com
Hi Romain,

Nice job!.

I suggest that you create some screenshots and then, just put it in
the Wiki page of your google code so you can see what is new in
qspatialite.

Screenshots are good just to see what is going on in this plugin.

Regards, Noli

Stefan Keller

unread,
Jun 2, 2011, 7:56:47 AM6/2/11
to spatiali...@googlegroups.com
Hi Romain

I'm investigating SQL and Query builders and found QSpatiaLite quite
interesting.

Just an idea about a new feature: A "Load" button wouldbe nice, which
loads the selected data into a QGIS layer - very much like the PostGIS
SQL Editor does.

Yours, Stefan

2011/6/2 romain <romain.ri...@gmail.com>:

romain

unread,
Jun 3, 2011, 6:24:00 AM6/3/11
to SpatiaLite Users
New version (3.7) on line:
One can now load several layers (any layer supported by OGR) at once,
with possibility to set a new srid.

Next implementation:
- button "load" selected data into QGIS


On 2 juin, 15:56, Stefan Keller <sfkel...@gmail.com> wrote:
> Hi Romain
>
> I'm investigating SQL and Query builders and found QSpatiaLite quite
> interesting.
>
> Just an idea about a new feature: A "Load" button wouldbe nice, which
> loads the selected data into a QGIS layer - very much like the PostGIS
> SQL Editor does.
>
> Yours, Stefan
>
> 2011/6/2 romain <romain.riviere....@gmail.com>:

Andrea P.

unread,
Jun 4, 2011, 8:55:44 AM6/4/11
to SpatiaLite Users
Hi,
great tools.
I'm testing it.

I see you split the tables/views in MyTables/SpatialIndex/SysTables

great idea.

To follow this idea I guess the spatialite_history table should be put
in the SysTables.

regards,

Andrea.

romain

unread,
Jun 8, 2011, 10:42:46 PM6/8/11
to SpatiaLite Users
QSpatiaLite v3.9 available:
- Load ResultSet into QGIS Canvas (right click on ResultSet)
- Minor bugs fixed

romain

unread,
Jun 14, 2011, 10:46:27 PM6/14/11
to SpatiaLite Users
Version 4.0.2 online:

- load resultset in QGIS
- (Batch) Export spatial tables to OGR layers
Reply all
Reply to author
Forward
0 new messages