Wish list

[Micha]

Note: a somewhat long post coming. Be forewarned...

I had the good fortune to take part in a some meetings over the past
few weeks with a group of entomologists and agronomists who use GIS to
monitor insect pests in agriculture. The initiative for the meetings
was to choose a set of open source tools to replace the usual Arcview
+ Access combination.
I suggested the combination of QGIS and Spatialite-gui, and ran a few
short tutorials to demonstrate how to replace the current users'
workflow with these programs. I'd like to point out the difficulties
we encountered, and bring up a short bullet list of how to make this
pair of FOSS GIS tools even more attractive to user's of proprietary
software.
I must mention that the participants of these meetings were all very
computer literate, well versed with Access and designing tables and
queries. However, whenever I switched to the command line and started
typing SELECT statements, I *lost 95% of them in 5 seconds*. Whatever
we know and understand about the advantages and efficiency of CLI, we
must face the reality that FOSS will spread only with a full featured
GUI. Luckily Spatialite has the spatialite-gui program that so far
seems to overshadow both of the newer QGIS plugins (QSpatialite and RT
SQL [2] ) that I have tested.

First the problems:
* Working with negative coordinate values, imported from a CSV text
file doesn't work. This has been mentioned on the list [1]. From the
CLI the .import command works fine, but importing a CSV file from the
GUI, negative values turn into text columns, rendering them useless as
coordinates.
* There doesn't seem to be any way, in the GUI, to append values from
a CSV into an existing table.
* There is no simple "one-click" way to create a new empty spatial
table. The Query Editor does a nice job to create a spatial view
(automatically inserting the required line into the
views_geometry_columns table). Why not the same thing for creating a
spatial table, automatically running AddGeometryColumn(...) ?
* Continuing the same point, there is no easy way to import a table of
X-Y values, and automatically create a spatial table, similar to the
"Delimited Text Plugin" in QGIS
* Using the "Edit table rows" option, inserting a new row seems rather
unnatural. It's not at all obvious that you must right-click on the
new black row in order to start entering values, and only then click
the "Insert row" button. This was cause for much dismay with the
meeting trainees. Furthermore, there's no way to limit the data entry,
no mechanism to create a real data entry form with controls, that
users are familiar with.

From those shortcomings, we make our wish list:
* There needs to be an easy way to create a data entry and update
form. The participants of the meetings I mentioned work with pest
control programs where they enter 1000-1500 new rows of data each
week. Any solution I cook up for them must include a proper data entry
form.
* Improvements are needed in the importing of data from external
files: to correct the minus values, to allow appending rows to an
existing table, to match input columns to existing table columns, and
to use X-Y values to automatically create the geometry of a POINT
layer.

Thanks for listening,
Micha



[1]
http://groups.google.com/group/spatialite-users/browse_thread/thread/e0b2bedc24f10947/7e32636c254aed8b?lnk=gst&q=negative#7e32636c254aed8b
[2] Neither of these plugins have any way to edit rows or insert new
data into existing tables, so they are not applicable to these
projects.

[Noli Sicad]

Hi Micha,

I use SQLite Manager add-on in Firefox in importing data to spatialite
database. You will have greater control in importing you data, you can
change / select text, numeric, or whatever you like in data type.

https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/

It also use it to manage (i.e. clean up) after using CLI.

Your "Wish List", you can probably create Pest Control plugin in QGIS
with desire data input form you have in mind.

You can ask your group - "group of entomologists and agronomists", if
anybody knows Python and PyQt (for GUI).

You can probably ask some students to help you create this plugin. I
presumed that some of the entomologists and agronomists are from
Universities and Colleges and have some students working with them.

You can start creating / prototype your form using Qt Creator.
http://qt.nokia.com/products/developer-tools/

Noli

> --
> 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.
>
>

[Noel Frankinet]

Hello Micha,

I'm developping (very slowly) an alternative viewer on top of spatialite, using Qt instead of wxwidget.
Using the javascript engine build in Qt, it is possible to develop a data entry system customized for each user needs.
Of course, a lot of work needs to be done.

I'm ready to discuss this further with other developpers ..

Best wishes

Noel

--
Noël Frankinet
Strategis sprl
0478/90.92.54

[B Freed]

Hi Micha,

I am in the process of getting the company I work for to switch from a
non-spatial MS Access database with Lat/Long stored as text to a
Spatialite/QGIS combination. In terms of creating new spatial tables,
I've found the QSpatialite plugin to be the best way at present. I
agree with you that overall I much prefer Spatialite-GUI, and do most
of my work there. But when digitizing in the field, QSpatialite's
create new table function is very fast and intuitive, and the ability
to right-click on it and load in QGIS is nice.

In my spare time, I'm trying to learn Python, and am working my way
towards a custom UI form for QGIS. There's a great post on the
subject at:
http://woostuff.wordpress.com/2011/09/05/qgis-tips-custom-feature-forms-with-python-logic/

In the meantime, what I've done is saved some QGIS layer styles out,
which I use when setting up a file for a new project, or getting a
template setup for everyone. In QGIS layer properties, I set the
pk_uid field to "hidden", set our "type" column to "value map", which
is populated only with the valid options for our database, and set our
"description" column to "text edit", which allows for multi-line
input.

The database back-end is well structured with foreign keys, not nulls
where appropriate, and a few constraints and triggers. With this
setup, as long as everyone uses the layer styles, everything "just
works." If someone loads a table without applying the layer style,
they could attempt to enter invalid data. What happens on save is
that for a manual entry in the primary key, the database ignores it
and assigns the next valid key anyway; for any other violation, QGIS
throws up an 'unable to save' error and won't let you exit edit mode
unless you fix the error or cancel the changes.
I'll be very happy when I get to a true custom form with validation
logic so that error messages can specify what the actual problem is.
But in the meantime, the QGIS layer styles do allow for customization
of what you see when working. It's a bit of a pain manually setting
up the value map values to populate the drop-down box, and I'd have to
update the layer style each time if we ever add other valid options
for that column. But it bridges the gap for now.

And +1 to the text input. The CSV import works great. But at this
point it's easier to say "yeah, I can do that for you" than it is to
get everyone up to speed on the: 1. make CSV, 2. import, 3. MakePoint,
4. Insert into XXXX Select from XXXX