SpatiaLite + QGIS, data portability

774 views
Skip to first unread message

ludwa6

unread,
Mar 15, 2012, 9:14:22 AM3/15/12
to SpatiaLite Users
I posted this earlier today on the qgis-user list, whereupon i was
told to inquire of the good people here -so with my apologies for the
cross-post, here ya go! /w

Following the consensus opinion of all those who kindly answered my
inquiry (Re: QGIS Farm Application), i have installed Spatialite and
managed to export all the data from a layer to a .SQLITE file, and
then view it from several different SQLite browsers, which confirms
integrity of data & database structure. This already is a constructive
step forward (thanks to all those who helped, too numerous already to
mention :).

Moreover, i have managed to create a database in LibreOffice based
upon that .sqlite file (after (re)learning more than i really wanted
to know about ODBC drivers!), which displays all the data as it
should. This is another good step forward, because -tho the business
of my farm is predicated on geo-located resources (e.g. a paddock on
the ground) having certain attributes that are best defined in QGIS
(e.g. perimeter, area, etc.)- i will still be working with that data
in spreadsheets on a much more dynamic basis.

However: When i try to edit those .sqlite files from QGIS in another
tool, i'm running into an error message that varies slightly,
depending on the editor i use (anything but OO at this point: ODBC is
too much pain, until i get these other mechanics worked out), but the
message is essentially this (from SQLiteStudio):
"Error while updating cell value; no such function: RTreeAlign".

Presuming that what i am trying to do is in fact possible*; then i am
left scratching my head as to why all these SQL tools are having this
trouble, and where that problematic function is coming from. Does that
error message suggest anything to you?

* (Of course, if what i am trying to do with such round-trip editing
is in fact not possible, i would sure appreciate if someone could let
me know :)

/wl

a.fu...@lqt.it

unread,
Mar 15, 2012, 9:30:50 AM3/15/12
to spatiali...@googlegroups.com
> However: When i try to edit those .sqlite files from QGIS in another
> tool, i'm running into an error message that varies slightly,
> depending on the editor i use (anything but OO at this point: ODBC is
> too much pain, until i get these other mechanics worked out), but the
> message is essentially this (from SQLiteStudio):
> "Error while updating cell value; no such function:
> RTreeAlign".
>


please note: SpatiaLite isn't simply SQLite.
you have to load libspatialite someway if you really want to get a
full and comprehensive support.

a brief technical explanation:
any Spatialite's own DB extensively relies on several TRIGGERs so
to ensure the proper level of data integrity (geometry validation
and spatial index synchronization).
RTreeAlign is exactly the SQL function taking care of R*Tree (spatial
index) synchronization. but RTreeAlign is an "extension function",
so you are required to load anyway the libspatialite dynamic extension
in order to get the corresponding code implementation.

suggestions and hints:
1) you can try this one:
SELECT load_extension('libspatialite-2.dll');
(or whatever is appropriate for your actual platform)
may be this is supported by the sw tool you are using,
or may be not ... check and try by yourself :-D
2) use spatialite_gui: a nice GUI tool offering full support
to both SQLite and SpatiaLite (no installation, no configuration;
just copy somewhere and start working).

http://www.gaia-gis.it/gaia-sins/windows-bin-x86/spatialite_gui-1.5.0-stable-win-x86.zip


bye Sandro


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

ludwa6

unread,
Mar 19, 2012, 11:37:32 AM3/19/12
to SpatiaLite Users
Hey Sandro: Thanks very much for your clarification and pointers (and
for your giving to the commons such great software, b/t/w). I've been
digging into this technology bit-by-bit as time permits (my day-job is
very much an offline thing :), and uncovering new capabilities every
step i go. Exciting!

The one thing i really need to know at this point is: can it be
possible to have one SQLite database that integrates my geographic
data (from QGIS+SpatiaLite) with my business data (from my spreadsheet
manager: currently LibreOffice -which connects with SQLite via ODBC),
such that each application can EDIT tables with references to tables
edited by the other application?

For the time being, i have laid my spreadsheet applications aside and
am working in SpatiaLite GUI to define those PK-FK relations that will
be necessary for this to work. It is slow going, mainly because my SQL
chops are so rusty; this i know i can overcome... But i would like to
hear from someone that knows, before i spend many more additional
hours, that there should be no problem in principle with SpatiaLite
sharing files that will be modified in this limited way by some other
application. Can you say for sure about this, Sandro?

Thanks again for listening! /wl
> http://www.gaia-gis.it/gaia-sins/windows-bin-x86/spatialite_gui-1.5.0...

a.furieri

unread,
Mar 19, 2012, 12:10:40 PM3/19/12
to SpatiaLite Users
When one says "kill two birds with one stone" ;-)

please, read my other recent post:
http://groups.google.com/group/spatialite-users/browse_thread/thread/dc899f5e6edc7695/068b56ec7e38df41#068b56ec7e38df41

I suppose it could be useful for any other tool
accessing a SpatiaLite's own DB-file via an ODBC
connection.

bye Sandro
Reply all
Reply to author
Forward
0 new messages