Qspatialite: new qgis plugin for spatialite users

[romain]

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]

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]

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]

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]

Okay, it is fixed, I'll upload a new version tonight.

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

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]

Ok, thank you for your explanation!

[Paulo]

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]

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]

I tried to import a polygon layer as a polygon, i.e., the geometry type is correct.

[romain riviere]

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]

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]

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]

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]

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]

Great, thanks Romain and Sandro, looking forward to the update :-)

[romain]

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]

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]

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]

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]

Just a little question: are you able to see icons next to table
names ?

[Vladimir Vyskocil]

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]

Ok, I'll try to fix that soon ...
Apparently, QGIS1.7 seems to manage spatialite differently , Am I
wrong ?

[romain]

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]

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]

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]

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]

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]

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]

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]

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]

New version 1.6:
- speed improvement
- Load TXT/CSV
- compatibile for linux, windows, mac

Feel free to test, comment, and make suggestions.

Bye,

[romain]

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]

*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]

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]

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]

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]

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]

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]

Thanks,
That's what I taught.
Hope they will release v1.7 soon.