[GRASS5] db.oodbfedit: a _very_ simplistic approach to dbf attribute editing

GRASS GIS GRASS GIS developer
1

Hello,

Playing around with the question of how to allow spreadsheet-style attribute
editing from the GIS Manager - mostly to satisfy demand from colleagues, I
came up with this _very_ simplistic solution using openoffice.

This is more of a proof of concept than anything else. The question for me is
whether we should try to write a specific tcl routine to access the different
types of databases available, or whether we should just use existing tools
such as openoffice (but which probably won't be installed for many users).

So here's the little bit of code. Any comments and reflections welcome.

Moritz

(attachments)

db.oodbfedit (1.35 KB)

2

On Tue, 2005-05-24 at 01:27 +0200, Moritz Lennert wrote:

Hello,

Playing around with the question of how to allow spreadsheet-style attribute
editing from the GIS Manager - mostly to satisfy demand from colleagues, I
came up with this _very_ simplistic solution using openoffice.

This is more of a proof of concept than anything else. The question for me is
whether we should try to write a specific tcl routine to access the different
types of databases available, or whether we should just use existing tools
such as openoffice (but which probably won't be installed for many users).

I have little experience with TCL, so I don't know the complexity
involved.

How difficult would it be to go with the simplistic approach (using
existing tools) for 6.x and move toward an integrated approach in 7.x?

On that note, I am making the assumption that the 7.x branch will be an
evolving development/cleanup branch, unlike the current stable 6.x
series.

--
Brad Douglas <rez@touchofmadness.com>

3

My first reaction would be: leave that to the QGIS front-end. I'm unsure
however that this is a viable solution in a production environment *today*.

Otherwise, if we wish to stick to a more internal solution, has anybody had
experience with TkTable?

The third-party tool approach may also be tackled through R, which (some)
people are already using in conjunction with GRASS.

BTW Moritz, are your colleagues by any chance ex-ArcViewers? I have the
feeling that trying to move GRASS in that visual/click/user-friendly arena is
a little off-putting, given GRASS' rather UNIXish philosophy. (Which, BTW
could be exploited a little further: not many commands are thought in terms of
piping, for instance.)

Also, note that the name is OpenOffice.org (OOo), including the ".org" for
trademark reasons.

Daniel.

-- Daniel Calvelo Aros

---------- Original Message -----------
From: Brad Douglas <rez@touchofmadness.com>
To: mlennert@club.worldonline.be
Cc: Grass Developers List <grass5@grass.itc.it>
Sent: Mon, 23 May 2005 18:28:04 -0700
Subject: Re: [GRASS5] db.oodbfedit: a _very_ simplistic approach to dbf
attribute editing

On Tue, 2005-05-24 at 01:27 +0200, Moritz Lennert wrote:
> Hello,
>
> Playing around with the question of how to allow spreadsheet-style attribute
> editing from the GIS Manager - mostly to satisfy demand from colleagues, I
> came up with this _very_ simplistic solution using openoffice.
>
> This is more of a proof of concept than anything else. The question for me is
> whether we should try to write a specific tcl routine to access the different
> types of databases available, or whether we should just use existing tools
> such as openoffice (but which probably won't be installed for many users).

I have little experience with TCL, so I don't know the complexity
involved.

How difficult would it be to go with the simplistic approach (using
existing tools) for 6.x and move toward an integrated approach in 7.x?

On that note, I am making the assumption that the 7.x branch will be
an evolving development/cleanup branch, unlike the current stable
6.x series.

--
Brad Douglas <rez@touchofmadness.com>

_______________________________________________
grass5 mailing list
grass5@grass.itc.it
http://grass.itc.it/mailman/listinfo/grass5

------- End of Original Message -------

4

Moritz Lennert wrote:

Hello,

Playing around with the question of how to allow spreadsheet-style attribute
editing from the GIS Manager - mostly to satisfy demand from colleagues, I
came up with this _very_ simplistic solution using openoffice.

This is more of a proof of concept than anything else. The question for me is
whether we should try to write a specific tcl routine to access the different
types of databases available, or whether we should just use existing tools
such as openoffice (but which probably won't be installed for many users).

So here's the little bit of code. Any comments and reflections welcome.

Moritz

To open a database table in spreadsheet is VERY BAD idea, you can be almost sure that data will be corrupted somehow.
Isn't it possible to open the table as "data source"?

Radim

5

On Tue, May 24, 2005 at 10:02:09AM +0200, Radim Blazek wrote:
...

To open a database table in spreadsheet is VERY BAD idea, you can be
almost sure that data will be corrupted somehow.

Right:
for example, long tables are destroyed in OOffice (there is a
limit of 32767/65535 rows while a DBF file/SQL DB can be much longer.

I am always "amused" when I receive Excel files with a length
of 65535 rows... :frowning: In this case there is 99% probability that
the file is already corrupted. GIS tables are often way longer.

There must be at least heavy error checking.

Markus

6

On Tue, May 24, 2005 10:02, Radim Blazek said:

Moritz Lennert wrote:

Hello,

Playing around with the question of how to allow spreadsheet-style attribute
editing from the GIS Manager - mostly to satisfy demand from colleagues, I
came up with this _very_ simplistic solution using openoffice.

This is more of a proof of concept than anything else. The question for me
is
whether we should try to write a specific tcl routine to access the
different
types of databases available, or whether we should just use existing tools
such as openoffice (but which probably won't be installed for many users).

So here's the little bit of code. Any comments and reflections welcome.

Moritz

To open a database table in spreadsheet is VERY BAD idea, you can be
almost sure that data will be corrupted somehow.

Could you explain this. I have used this procedure quite often without any
problems.

Isn't it possible to open the table as "data source"?

Yes, but I don't know how to do this automatically. This requires the user to
set up the data source in oocalc and if they already know how do this, the
whole module is useless.

BTW, trying to use the data source procedure I just corrupted a table through
a simple "update" statement. :frowning: Probable just a bad manoeuvre on my side...

Moritz

7

>> Playing around with the question of how to allow spreadsheet-style
>> attribute editing from the GIS Manager - mostly to satisfy demand
>> from colleagues, I came up with this _very_ simplistic solution
>> using openoffice.

...

> To open a database table in spreadsheet is VERY BAD idea, you can be
> almost sure that data will be corrupted somehow.

I think a good solution for small tables is to use db.select or
v.out.ascii.db (wiki addon) to export the table, modify it with whatever
tool the user likes, then reimport with db.in.ascii [*], and reconnect
with v.db.connect.

I'm a flat ascii file person not a DB person though, so add grain of salt.

[*] see:
  http://grass.itc.it/pipermail/grassuser/2004-October/026786.html
  http://grass.itc.it/pipermail/grass5/2004-November/016274.html

or db.copy, edit step, then v.db.connect?

Hamish

8

From: "Moritz Lennert" <mlennert@club.worldonline.be>
Sent: Tue, 24 May 2005 22:49:14 +0200 (CEST)

On Tue, May 24, 2005 10:02, Radim Blazek said:
> To open a database table in spreadsheet is VERY BAD idea, you can be
> almost sure that data will be corrupted somehow.

Could you explain this. I have used this procedure quite often
without any problems.

I have had very few problems with oocalc; the only possible glitch is to mess
the sort order, which is VERY bad.

With excel, however, you can end up with silent type conversions, loss or
messing of null values, columns added or cut, and generally unpredictable
things when trying to save back to dbf. In that respect, oocalc does a decent
job, especially given that type info is kept in the headers. There are also
hassles with trying to acces open files and so on. Under windows, of course.

You are doing this under linux, aren't you, Moritz?

Daniel.

9

On Tue, May 24, 2005 4:14, Daniel Calvelo Aros said:

My first reaction would be: leave that to the QGIS front-end. I'm unsure

however that this is a viable solution in a production environment *today*.

I think that not everyone wants to use QGIS as a frontend, so some internal
solutions would be helpful.

Otherwise, if we wish to stick to a more internal solution, has anybody had

experience with TkTable?

Haven't had the time to dig into it, but it looks interesting.

The third-party tool approach may also be tackled through R, which (some)

people are already using in conjunction with GRASS.

Yes, but I'm not sure table editing is much easier in R...

BTW Moritz, are your colleagues by any chance ex-ArcViewers? I have the

feeling that trying to move GRASS in that visual/click/user-friendly arena
is

a little off-putting, given GRASS' rather UNIXish philosophy. (Which, BTW

could be exploited a little further: not many commands are thought in terms
of

piping, for instance.)

Thinking about this a bit more, I agree with you. We should not try to create
such functionality in GRASS. Those who really want to use a spreadsheet can
create the link themselves (using the "data source" functionality of OOo is
explained in the GRASS6 tutorial). Most ArcViewers around me are actually
doing the same, opening the dbf files with MS Excell...

What I do think would be useful though, is a series of easy to use frontends
to db.execute.
I've already written a simple db.addcolumn and db.update. I'll send it in a
separate mail with some more questions.

Also, note that the name is OpenOffice.org (OOo), including the ".org" for

trademark reasons.

Thanks for correcting me.

Moritz

10

Moritz Lennert wrote:

To open a database table in spreadsheet is VERY BAD idea, you can be
almost sure that data will be corrupted somehow.

Could you explain this. I have used this procedure quite often without any
problems.

If you know what you are doing it is possible, however people who are not able to define 'data source' can sort one column only etc.

Isn't it possible to open the table as "data source"?

Yes, but I don't know how to do this automatically.

I thought that OO could have some scripting support and a script could be run from command line, but I dont know if anything like that exists.

Isn't it possible to open the dbf file with knoda directly?

Radim

11

On Wed, May 25, 2005 10:19, Radim Blazek said:

Moritz Lennert wrote:

To open a database table in spreadsheet is VERY BAD idea, you can be
almost sure that data will be corrupted somehow.

Could you explain this. I have used this procedure quite often without any
problems.

If you know what you are doing it is possible, however people who are
not able to define 'data source' can sort one column only etc.

Ok, agreed.

Isn't it possible to open the table as "data source"?

Yes, but I don't know how to do this automatically.

I thought that OO could have some scripting support and a script could
be run from command line, but I dont know if anything like that exists.

It might, but I haven't seen this, yet.

Isn't it possible to open the dbf file with knoda directly?

This supposes that people use knoda, which in general means that they run KDE...

All these different elements contribute to the idea that finally my idea
wasn't so great :wink:
I'll pursue the other path of creating easy frontends to db.execute.

Thanks !

Moritz

Radim

12

On Wed, May 25, 2005 at 10:33:28AM +0200, Moritz Lennert wrote:
...

All these different elements contribute to the idea that finally my idea
wasn't so great :wink:
I'll pursue the other path of creating easy frontends to db.execute.

Maybe things should go into sqlite3 direction (there are tcl/tk bindings),
and/or OGR SQL engine.

sqlite3 could be interesting to manage raster time series as well etc.

?

Markus

13

On Wed, May 25, 2005 at 02:30:41PM +0200, Markus Neteler wrote:

On Wed, May 25, 2005 at 10:33:28AM +0200, Moritz Lennert wrote:
...
> All these different elements contribute to the idea that finally my idea
> wasn't so great :wink:
> I'll pursue the other path of creating easy frontends to db.execute.

Maybe things should go into sqlite3 direction (there are tcl/tk bindings),
and/or OGR SQL engine.

sqlite3 could be interesting to manage raster time series as well etc.

There is also a browser (or several):
http://sqlitebrowser.sourceforge.net/screenshots.html

Markus

14

From: Markus Neteler <neteler@itc.it>
Sent: Thu, 4 Aug 2005 17:33:33 +0200

On Wed, May 25, 2005 at 02:30:41PM +0200, Markus Neteler wrote:
> On Wed, May 25, 2005 at 10:33:28AM +0200, Moritz Lennert wrote:
> ...
> > All these different elements contribute to the idea that finally my idea
> > wasn't so great :wink:
> > I'll pursue the other path of creating easy frontends to db.execute.
>
> Maybe things should go into sqlite3 direction (there are tcl/tk bindings),
> and/or OGR SQL engine.
>
> sqlite3 could be interesting to manage raster time series as well etc.
>

There is also a browser (or several):
http://sqlitebrowser.sourceforge.net/screenshots.html

Furthermore, OGR can be compiled with sqlite3 support. It can then store a
spatial database as one table with a WKT column.

I've been using this feature to convert dbf to sqlite: use ogr2ogr with a
NONE geometry type and you have only the attributes in your sqlite db. The
converse sqlite->dbf though cannot be accomplished through ogr2ogr since you
have no geometry. You can use sqlite's dump and db.execute instead. I thought
going this route for upgrading grass' sql capabilities, but the conversion
process in ogr is rather slow.

BTW, Moritz, have you tried the current CVS for extended SQL capabilities?
Please do so, and come up with wishes for extending it further. I've been
griping with NULL/NAN handling, but have come up with nothing concrete yet.

Daniel.

15

There is also ogr database driver in GRASS (dbmi),
Probably it is possible to use SQLite with GRASS vectors
via OGR, but I have no idea about limits/performance.

Radim

On 8/4/05, Daniel Calvelo Aros <dcalvelo@minag.gob.pe> wrote:

From: Markus Neteler <neteler@itc.it>
Sent: Thu, 4 Aug 2005 17:33:33 +0200
>
> On Wed, May 25, 2005 at 02:30:41PM +0200, Markus Neteler wrote:
> > On Wed, May 25, 2005 at 10:33:28AM +0200, Moritz Lennert wrote:
> > ...
> > > All these different elements contribute to the idea that finally my idea
> > > wasn't so great :wink:
> > > I'll pursue the other path of creating easy frontends to db.execute.
> >
> > Maybe things should go into sqlite3 direction (there are tcl/tk bindings),
> > and/or OGR SQL engine.
> >
> > sqlite3 could be interesting to manage raster time series as well etc.
> >
>
> There is also a browser (or several):
> http://sqlitebrowser.sourceforge.net/screenshots.html

Furthermore, OGR can be compiled with sqlite3 support. It can then store a
spatial database as one table with a WKT column.

I've been using this feature to convert dbf to sqlite: use ogr2ogr with a
NONE geometry type and you have only the attributes in your sqlite db. The
converse sqlite->dbf though cannot be accomplished through ogr2ogr since you
have no geometry. You can use sqlite's dump and db.execute instead. I thought
going this route for upgrading grass' sql capabilities, but the conversion
process in ogr is rather slow.

BTW, Moritz, have you tried the current CVS for extended SQL capabilities?
Please do so, and come up with wishes for extending it further. I've been
griping with NULL/NAN handling, but have come up with nothing concrete yet.

Daniel.

16

On Thu, August 4, 2005 21:38, Daniel Calvelo Aros said:

From: Markus Neteler <neteler@itc.it>
Sent: Thu, 4 Aug 2005 17:33:33 +0200

On Wed, May 25, 2005 at 02:30:41PM +0200, Markus Neteler wrote:
> On Wed, May 25, 2005 at 10:33:28AM +0200, Moritz Lennert wrote:
> ...
> > All these different elements contribute to the idea that finally my idea
> > wasn't so great :wink:
> > I'll pursue the other path of creating easy frontends to db.execute.
>
> Maybe things should go into sqlite3 direction (there are tcl/tk bindings),
> and/or OGR SQL engine.
>
> sqlite3 could be interesting to manage raster time series as well etc.
>

There is also a browser (or several):
http://sqlitebrowser.sourceforge.net/screenshots.html

This does look good, but it depends on QT. It seems a bit overkill to have
GRASS depend on QT just for a data browser...
This should be part of the general discussion of a future GRASS GUI that
Michael launched recently.

BTW, Moritz, have you tried the current CVS for extended SQL capabilities?
Please do so, and come up with wishes for extending it further. I've been
griping with NULL/NAN handling, but have come up with nothing concrete yet.

Could you remind of what the new capabilities actually are, so that I know
what to test ?

Last time I asked, this was the answer from Glynn
(http://grass.itc.it/pipermail/grassuser/2005-June/029453.html):

********

> Only a limited set of SQL functions and operators are currently supported.
>
> Addition is not among them.
>
> see the GRASS SQL help page:
> http://grass.ibiblio.org/grass61/manuals/html61_user/sql.html

Would the latest changes to the sql expression parser
(http://grass.itc.it/pipermail/grass5/2005-June/018588.html and
following) change this ?

No. Those changes just fix and extend the syntax of arithmetic
expressions within WHERE clauses.
**********

Is this still true ? One clear need was the possibility of including
expressions in the update statement.

Moritz

17

[me]

> BTW, Moritz, have you tried the current CVS for extended SQL capabilities?
> Please do so, and come up with wishes for extending it further. I've been
> griping with NULL/NAN handling, but have come up with nothing concrete
> yet.

[Moritz]

Could you remind of what the new capabilities actually are, so that
I know what to test ?

See http://grass.itc.it/pipermail/grass5/2005-July/018981.html ; I patched CVS
last week.

Last time I asked, this was the answer from Glynn
(http://grass.itc.it/pipermail/grassuser/2005-June/029453.html):

********
> > Only a limited set of SQL functions and operators are currently supported.
> >
> > Addition is not among them.
> >
> > see the GRASS SQL help page:
> > http://grass.ibiblio.org/grass61/manuals/html61_user/sql.html
>
> Would the latest changes to the sql expression parser
> (http://grass.itc.it/pipermail/grass5/2005-June/018588.html and
> following) change this ?

No. Those changes just fix and extend the syntax of arithmetic
expressions within WHERE clauses.
**********

Is this still true ? One clear need was the possibility of including
expressions in the update statement.

He, he. No, that's not true anymore. What you have now is (test please, test
please, test please):

update table foo set bar=baz/2+1/(-4*toto);

in addition to

select * from foo where 1-(-4)/bar=2+baz

Furthermore, in http://grass.itc.it/pipermail/grass5/2005-June/018589.html I
asked for testing an updated d.vect.thematic that handled (with the above
patches in the parser) thematic mapping from expressions.

Still TODO: after some more testing and certainly debugging, change the docs
to reflect current status.

Daniel.

18

On Fri, Aug 05, 2005 at 06:45:23PM +0200, Moritz Lennert wrote:

On Thu, August 4, 2005 21:38, Daniel Calvelo Aros said:
> From: Markus Neteler <neteler@itc.it>
> Sent: Thu, 4 Aug 2005 17:33:33 +0200
>>
>> On Wed, May 25, 2005 at 02:30:41PM +0200, Markus Neteler wrote:
>> > On Wed, May 25, 2005 at 10:33:28AM +0200, Moritz Lennert wrote:
>> > ...
>> > > All these different elements contribute to the idea that finally my idea
>> > > wasn't so great :wink:
>> > > I'll pursue the other path of creating easy frontends to db.execute.
>> >
>> > Maybe things should go into sqlite3 direction (there are tcl/tk bindings),
>> > and/or OGR SQL engine.
>> >
>> > sqlite3 could be interesting to manage raster time series as well etc.
>> >
>>
>> There is also a browser (or several):
>> http://sqlitebrowser.sourceforge.net/screenshots.html

This does look good, but it depends on QT. It seems a bit overkill to have
GRASS depend on QT just for a data browser...
This should be part of the general discussion of a future GRASS GUI that
Michael launched recently.

Also QGIS depends on QT - it was meant as an option, not as a
requirement.

Markus

19

Daniel Calvelo Aros wrote:

> No. Those changes just fix and extend the syntax of arithmetic
> expressions within WHERE clauses.
> **********
>
> Is this still true ? One clear need was the possibility of including
> expressions in the update statement.

He, he. No, that's not true anymore. What you have now is (test please, test
please, test please):

update table foo set bar=baz/2+1/(-4*toto);

Does this handle updating multiple fields correctly? I.e. if you do:

  update table foo set bar=bar+1, baz=bar

will baz contain the original value of bar (not the updated value)?

--
Glynn Clements <glynn@gclements.plus.com>

20

On Sat, August 6, 2005 2:20, Daniel Calvelo Aros said:

He, he. No, that's not true anymore. What you have now is (test please, test
please, test please):

update table foo set bar=baz/2+1/(-4*toto);

in addition to

select * from foo where 1-(-4)/bar=2+baz

This seems to work perfectly, at least for the expressions I've tried.

Furthermore, in http://grass.itc.it/pipermail/grass5/2005-June/018589.html I
asked for testing an updated d.vect.thematic that handled (with the above
patches in the parser) thematic mapping from expressions.

This also seems to work, after applying the following patch in v.univar.sh:

--- v.univar.sh 2005-08-06 21:35:38.814231960 +0200
+++ /usr/lib/grass/scripts/v.univar.sh 2005-08-06 22:26:33.626829856 +0200
@@ -131,7 +131,7 @@
   gsub(" ","",expression);
   split(col_list,vars,",");
   for(i in vars){
- gsub(vars[i], "\$" i, expression);
+ gsub(vars[i], "$" i, expression);
   }
   if(expression~/\//){
     theres_div=1;

With gawk the \$ is corrected automatically (but a warning is displayed), but
with mawk (debian's default awk) it fails. Without the \ everything seems to
work (again only rapid testing).

Thanks a lot Daniel: this makes both v.db.update and d.vect.thematic much more
useful !

Moritz