[GRASS-dev] db.join script wanted

neteler · March 8, 2016, 12:06am

Hi,

anyone willing to write a db.join addon? Of course table joining is
not a complex task but an addon offers the advantage to also perform
as desired when the user is tired - less error prone.

Perhaps just derive from
https://trac.osgeo.org/grass/browser/grass/trunk/scripts/v.db.join/v.db.join.py
?

thanks
Markus

martinl · March 8, 2016, 8:31am

2016-03-08 1:06 GMT+01:00 Markus Neteler <neteler@osgeo.org>:

Perhaps just derive from
https://trac.osgeo.org/grass/browser/grass/trunk/scripts/v.db.join/v.db.join.py

or v.db.join could call new db.join (?) Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa

neteler · March 8, 2016, 3:49pm

On Tue, Mar 8, 2016 at 9:31 AM, Martin Landa <landa.martin@gmail.com> wrote:

2016-03-08 1:06 GMT+01:00 Markus Neteler <neteler@osgeo.org>:

Perhaps just derive from
https://trac.osgeo.org/grass/browser/grass/trunk/scripts/v.db.join/v.db.join.py

or v.db.join could call new db.join (?) Ma

Yes, this might be the best to avoid redundancy.

thanks
Markus

neteler · March 9, 2016, 10:13am

Hi

I uploaded a first db. join script to addons. r68028.

Something is missing, can anyone please revise it?

Thanks
Markus

ecodiv · March 9, 2016, 11:12am

Thanks Markus, this looks useful. I’ll give it a try when appearing in g.extension. In the meantime, a quick questions: how does this extension differ from v.db.join? With db.join, is it possible to join two tables, including tables that are not attribute tables of a vector layer?

Cheers,

Paulo

···

On Wed, Mar 9, 2016 at 11:13 AM, Markus Neteler <neteler@osgeo.org> wrote:

Hi

I uploaded a first db. join script to addons. r68028.

Something is missing, can anyone please revise it?

Thanks
Markus

grass-dev mailing list
grass-dev@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-dev

neteler · March 9, 2016, 12:09pm

On Mar 9, 2016 12:12 PM, “Paulo van Breugel” <p.vanbreugel@gmail.com> wrote:

Thanks Markus, this looks useful. I’ll give it a try when appearing in g.extension.

It should be there…

In the meantime, a quick questions: how does this extension differ from v.db.join?

Once db.join works, v.db.join should call it, and the respective part be removed from v.db.join.

With db.join, is it possible to join two tables, including tables that are not attribute tables of a vector layer?

Yes, exactly. It is not related to maps at all. It fills the gap of handling table joins.

Just it needs to be reviewed…

Best
Markus

ecodiv · March 9, 2016, 12:35pm

On Wed, Mar 9, 2016 at 1:09 PM, Markus Neteler <neteler@osgeo.org> wrote:

On Mar 9, 2016 12:12 PM, "Paulo van Breugel" <p.vanbreugel@gmail.com>
wrote:
>
> Thanks Markus, this looks useful. I'll give it a try when appearing in
g.extension.

It should be there...

It is in svn I saw, but it doesn't show up in g.extension.. perhaps because
I am on 7.1?

> In the meantime, a quick questions: how does this extension differ from
v.db.join?

Once db.join works, v.db.join should call it, and the respective part be
removed from v.db.join.

> With db.join, is it possible to join two tables, including tables that
are not attribute tables of a vector layer?

Yes, exactly. It is not related to maps at all. It fills the gap of
handling table joins.

Cool, very welcome function

Just it needs to be reviewed...

Will try later today or tomorrow

Best
Markus

neteler · March 9, 2016, 1:02pm

On Mar 9, 2016 1:36 PM, “Paulo van Breugel” <p.vanbreugel@gmail.com> wrote:

…

It is in svn I saw, but it doesn’t show up in g.extension… perhaps because I am on 7.1?

This does not matter. I didn’t yet add it to the parent Make file because it is not ready.

Just take it from trac/svn from now…

Markus

ecodiv · March 9, 2016, 4:25pm

Hoi Markus

Still not there via g.extension, but I copied the script to the script folder.

I tried with two tables (both created in an external sqlite editor),

Table 1: columns A (integer, key) and B (float)
Table 2: columns A (integer, key) and C (float)

First observation: using the GUI, the drop down menu for the identifier column shows the column names of Table 1 instead of Table 2.

I tried to link the two on the first columns and on the second columns. In both cases I got an error message, copied below:

db.join table=test1 column=A other_table=test2 other_column=A

Traceback (most recent call last):

  File "/home/paulo/.grass7/addons/scripts/db.join", line 170, in <module>    sys.exit(main())

  File "/home/paulo/.grass7/addons/scripts/db.join", line 152, in main columns=colspec)

  File "/usr/local/grass7/grass-7.1.svn/etc/python/grass/script/core.py", line 408, in run_command ps = start_command(*args, **kwargs)

  File "/usr/local/grass7/grass-7.1.svn/etc/python/grass/script/core.py", line 377, in start_command return Popen(args, **popts)

  File "/usr/local/grass7/grass-7.1.svn/etc/python/grass/script/core.py", line 74, in __init__ subprocess.Popen.__init__(self, args, **kwargs)

  File "/usr/lib/python2.7/subprocess.py", line 710, in __init__  errread, errwrite)

  File "/usr/lib/python2.7/subprocess.py", line 1327, in _execute_child raise child_exception

OSError: [Errno 2] No such file or directory

Paulo

···

On 09-03-16 14:02, Markus Neteler wrote:

On Mar 9, 2016 1:36 PM, “Paulo van Breugel” <p.vanbreugel@gmail.com> wrote:

…

It is in svn I saw, but it doesn’t show up in g.extension… perhaps because I am on 7.1?

This does not matter. I didn’t yet add it to the parent Make file because it is not ready.

Just take it from trac/svn from now…

Markus

neteler · March 9, 2016, 4:30pm

On Mar 9, 2016 5:25 PM, “Paulo van Breugel” <p.vanbreugel@gmail.com> wrote:

Hoi Markus

Still not there via g.extension, but I copied the script to the script folder.

I tried with two tables (both created in an external sqlite editor),

Table 1: columns A (integer, key) and B (float)
Table 2: columns A (integer, key) and C (float)

First observation: using the GUI, the drop down menu for the identifier column shows the column names of Table 1 instead of Table 2.

Mhh, perhaps I mixed something up there.

I tried to link the two on the first columns and on the second columns. In both cases I got an error message, copied below:

db.join table=test1 column=A other_table=test2 other_column=A

Traceback (most recent call last):

File “/home/paulo/.grass7/addons/scripts/db.join”, line 170, in sys.exit(main())

File “/home/paulo/.grass7/addons/scripts/db.join”, line 152, in main columns=colspec)

File “/usr/local/grass7/grass-7.1.svn/etc/python/grass/script/core.py”, line 408, in run_command ps = start_command(*args, **kwargs)

File “/usr/local/grass7/grass-7.1.svn/etc/python/grass/script/core.py”, line 377, in start_command return Popen(args, **popts)

File “/usr/local/grass7/grass-7.1.svn/etc/python/grass/script/core.py”, line 74, in init subprocess.Popen.init(self, args, **kwargs)

File “/usr/lib/python2.7/subprocess.py”, line 710, in init errread, errwrite)

File “/usr/lib/python2.7/subprocess.py”, line 1327, in _execute_child raise child_exception

OSError: [Errno 2] No such file or directory

That one I fixed 30min ago or so in Svn.

Please check again.

Markus

Paulo

On 09-03-16 14:02, Markus Neteler wrote:

On Mar 9, 2016 1:36 PM, “Paulo van Breugel” <p.vanbreugel@gmail.com> wrote:

…

It is in svn I saw, but it doesn’t show up in g.extension… perhaps because I am on 7.1?

This does not matter. I didn’t yet add it to the parent Make file because it is not ready.

Just take it from trac/svn from now…

Markus

ecodiv · March 9, 2016, 4:53pm

Thanks, db.join table=test1 column=A other_table=test2 other_column=A now works. When trying to link the two tables on column B and C, it doesn’t, which is because both tables have a column A.

GRASS 7.1.svn (AEA):~ > db.join table=test1 column=B other_table=test2 other_column=C
WARNING: SQLite driver: unable to parse decltype: REAL(20)
WARNING: SQLite driver: unable to parse decltype: REAL(20)
WARNING: SQLite driver: column 'C', SQLite type 2 is not supported
WARNING: SQLite driver: unable to parse decltype: REAL(20)
WARNING: SQLite driver: unable to parse decltype: REAL(20)
WARNING: SQLite driver: column 'C', SQLite type 2 is not supported
DBMI-SQLite driver error:
Error in sqlite3_prepare():
duplicate column name: A

DBMI-SQLite driver error:
Error in sqlite3_prepare():
duplicate column name: A

ERROR: Error while executing: 'ALTER TABLE test1 ADD COLUMN A INTEGER(20)'
ERROR: Unable to add column <A>.

The warnings were not shown when linking via columns A, so I assume they are not a problem? As for the problem with the duplicate column name, would it be possible to add the option to define a prefix which is added to the column of the second table if a column with the same name exists in the first table (with a default prefix if none is given by the user)?

Works now

···

On 09-03-16 17:30, Markus Neteler wrote:

On Mar 9, 2016 5:25 PM, “Paulo van Breugel” <p.vanbreugel@gmail.com> wrote:

Hoi Markus

Still not there via g.extension, but I copied the script to the script folder.

I tried with two tables (both created in an external sqlite editor),

Table 1: columns A (integer, key) and B (float)
Table 2: columns A (integer, key) and C (float)

First observation: using the GUI, the drop down menu for the identifier column shows the column names of Table 1 instead of Table 2.

Mhh, perhaps I mixed something up there.

I tried to link the two on the first columns and on the second columns. In both cases I got an error message, copied below:

db.join table=test1 column=A other_table=test2 other_column=A

Traceback (most recent call last):

File “/home/paulo/.grass7/addons/scripts/db.join”, line 170, in sys.exit(main())

File “/home/paulo/.grass7/addons/scripts/db.join”, line 152, in main columns=colspec)

File “/usr/local/grass7/grass-7.1.svn/etc/python/grass/script/core.py”, line 408, in run_command ps = start_command(*args, **kwargs)

File “/usr/local/grass7/grass-7.1.svn/etc/python/grass/script/core.py”, line 377, in start_command return Popen(args, **popts)

File “/usr/local/grass7/grass-7.1.svn/etc/python/grass/script/core.py”, line 74, in init subprocess.Popen.init(self, args, **kwargs)

File “/usr/lib/python2.7/subprocess.py”, line 710, in init errread, errwrite)

File “/usr/lib/python2.7/subprocess.py”, line 1327, in _execute_child raise child_exception

OSError: [Errno 2] No such file or directory

That one I fixed 30min ago or so in Svn.

Please check again.

Markus

Paulo

On 09-03-16 14:02, Markus Neteler wrote:

On Mar 9, 2016 1:36 PM, “Paulo van Breugel” <p.vanbreugel@gmail.com> wrote:

…

It is in svn I saw, but it doesn’t show up in g.extension… perhaps because I am on 7.1?

This does not matter. I didn’t yet add it to the parent Make file because it is not ready.

Just take it from trac/svn from now…

Markus

neteler · March 9, 2016, 5:01pm

Hi

I hope that another developer picks it up add I am traveling…

Thanks
Markus

On Mar 9, 2016 5:53 PM, “Paulo van Breugel” <p.vanbreugel@gmail.com> wrote:

Thanks, db.join table=test1 column=A other_table=test2 other_column=A now works. When trying to link the two tables on column B and C, it doesn’t, which is because both tables have a column A.
GRASS 7.1.svn (AEA):~ > db.join table=test1 column=B other_table=test2 other_column=C
WARNING: SQLite driver: unable to parse decltype: REAL(20)
WARNING: SQLite driver: unable to parse decltype: REAL(20)
WARNING: SQLite driver: column 'C', SQLite type 2 is not supported
WARNING: SQLite driver: unable to parse decltype: REAL(20)
WARNING: SQLite driver: unable to parse decltype: REAL(20)
WARNING: SQLite driver: column 'C', SQLite type 2 is not supported
DBMI-SQLite driver error:
Error in sqlite3_prepare():
duplicate column name: A
DBMI-SQLite driver error:
Error in sqlite3_prepare():
duplicate column name: A
ERROR: Error while executing: 'ALTER TABLE test1 ADD COLUMN A INTEGER(20)'
ERROR: Unable to add column <A>.
The warnings were not shown when linking via columns A, so I assume they are not a problem? As for the problem with the duplicate column name, would it be possible to add the option to define a prefix which is added to the column of the second table if a column with the same name exists in the first table (with a default prefix if none is given by the user)?

On 09-03-16 17:30, Markus Neteler wrote:

On Mar 9, 2016 5:25 PM, “Paulo van Breugel” <p.vanbreugel@gmail.com> wrote:

Hoi Markus

Still not there via g.extension, but I copied the script to the script folder.

I tried with two tables (both created in an external sqlite editor),

Table 1: columns A (integer, key) and B (float)
Table 2: columns A (integer, key) and C (float)

First observation: using the GUI, the drop down menu for the identifier column shows the column names of Table 1 instead of Table 2.

Mhh, perhaps I mixed something up there.

I tried to link the two on the first columns and on the second columns. In both cases I got an error message, copied below:

db.join table=test1 column=A other_table=test2 other_column=A

Traceback (most recent call last):

File “/home/paulo/.grass7/addons/scripts/db.join”, line 170, in sys.exit(main())

File “/home/paulo/.grass7/addons/scripts/db.join”, line 152, in main columns=colspec)

File “/usr/local/grass7/grass-7.1.svn/etc/python/grass/script/core.py”, line 408, in run_command ps = start_command(*args, **kwargs)

File “/usr/local/grass7/grass-7.1.svn/etc/python/grass/script/core.py”, line 377, in start_command return Popen(args, **popts)

File “/usr/local/grass7/grass-7.1.svn/etc/python/grass/script/core.py”, line 74, in init subprocess.Popen.init(self, args, **kwargs)

File “/usr/lib/python2.7/subprocess.py”, line 710, in init errread, errwrite)

File “/usr/lib/python2.7/subprocess.py”, line 1327, in _execute_child raise child_exception

OSError: [Errno 2] No such file or directory

That one I fixed 30min ago or so in Svn.

Works now

Please check again.

Markus

Paulo

On 09-03-16 14:02, Markus Neteler wrote:

On Mar 9, 2016 1:36 PM, “Paulo van Breugel” <p.vanbreugel@gmail.com> wrote:

…

It is in svn I saw, but it doesn’t show up in g.extension… perhaps because I am on 7.1?

This does not matter. I didn’t yet add it to the parent Make file because it is not ready.

Just take it from trac/svn from now…

Markus

lucadelu · March 9, 2016, 5:56pm

On 9 March 2016 at 18:01, Markus Neteler <neteler@osgeo.org> wrote:

Hi

Hi,

I hope that another developer picks it up add I am traveling...

I'm working on it, but for me it doesn't work

I'll update you

Thanks
Markus

--
ciao
Luca

http://gis.cri.fmach.it/delucchi/
www.lucadelu.org

neteler · March 9, 2016, 6:11pm

On Mar 9, 2016 6:57 PM, “Luca Delucchi” <lucadeluge@gmail.com> wrote:

On 9 March 2016 at 18:01, Markus Neteler <neteler@osgeo.org> wrote:

Hi

Hi,

I hope that another developer picks it up add I am traveling…

I’m working on it, but for me it doesn’t work

Yeah… I think
LEFT OUTER JOIN
must be used, at least with sqlite backend.

I’ll update you

Thanks

Thanks
Markus

–
ciao
Luca

http://gis.cri.fmach.it/delucchi/
www.lucadelu.org

lucadelu · March 9, 2016, 6:12pm

On 9 March 2016 at 18:56, Luca Delucchi <lucadeluge@gmail.com> wrote:

On 9 March 2016 at 18:01, Markus Neteler <neteler@osgeo.org> wrote:

Hi

Hi,

I hope that another developer picks it up add I am traveling...

I'm working on it, but for me it doesn't work

I'll update you

Ok, I was wrong it works, but there is a problem.

I'm not able to read the new columns. I try to explain you

I imported the two attached csv files (table1 without header and
table2 with header)

db.in.ogr input=/tmp/table1.csv output=table1
db.in.ogr input=/tmp/table2.csv output=table2

db.join table=table1 column=field_1 other_table=table2 other_column=id

db.select table=table1
WARNING: SQLite driver: unable to parse decltype: TEXT(1000)
WARNING: SQLite driver: unable to parse decltype: TEXT(1000)
WARNING: SQLite driver: unable to parse decltype: TEXT(1000)
WARNING: SQLite driver: column 'id', SQLite type 3 is not supported
WARNING: SQLite driver: unable to parse decltype: TEXT(1000)
WARNING: SQLite driver: column 'label', SQLite type 3 is not supported
field_1|field_2
1|100
2|100
3|200
4|100
5|200
6|100
7|200
8|200
9|100
10|100

but the table is correct in sqlite3

sqlite3 grassdata/gauss_boaga/lucadelu/sqlite/sqlite.db
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
sqlite> select * from table1;
1|100|1|terra
2|100|2|mare
3|200|3|mare
4|100|4|terra
5|200|5|terra
6|100|6|mare
7|200|7|mare
8|200|8|terra
9|100|9|mare
10|100|10|terra
sqlite> .schema table1
CREATE TABLE table1(field_1 CHARACTER, field_2 CHARACTER, id
TEXT(1000), label TEXT(1000));

The problem is the different type of columns, I'll look on the code later

--
ciao
Luca

http://gis.cri.fmach.it/delucchi/
www.lucadelu.org

(attachments)

table1.csv (61 Bytes)
table2.csv (85 Bytes)

lucadelu · March 9, 2016, 6:14pm

On 9 March 2016 at 19:11, Markus Neteler <neteler@osgeo.org> wrote:

Yeah.. I think
LEFT OUTER JOIN
must be used, at least with sqlite backend.

no, the code is working quiet well, look to m other mail. it seems a
column type problem

--
ciao
Luca

http://gis.cri.fmach.it/delucchi/
www.lucadelu.org

ecodiv · March 9, 2016, 7:11pm

On Wed, Mar 9, 2016 at 7:14 PM, Luca Delucchi <lucadeluge@gmail.com> wrote:

On 9 March 2016 at 19:11, Markus Neteler <neteler@osgeo.org> wrote:
>
>
> Yeah.. I think
> LEFT OUTER JOIN
> must be used, at least with sqlite backend.

The code now assumes a 1:1 relationship, is that right?

so if Table 1
A|B
1|2
2|2

and Table 2
A|C
1|3
1|4
2|6

After joining Table 1 looks like

A|B|C
1|2|3
2|2|6

If correct, it might be good to clearly explain this in the documentation?
I wouldn't mind doing that if a final version is ready. At some point it
might be a nice feature to have the option to select the kind of
relationship. On the other hand, that may complicate things to the point
that one may as well use db.execute.

>

no, the code is working quiet well, look to m other mail. it seems a
column type problem

--
ciao
Luca

http://gis.cri.fmach.it/delucchi/
www.lucadelu.org

neteler · March 9, 2016, 10:14pm

On Wed, Mar 9, 2016 at 8:11 PM, Paulo van Breugel
<p.vanbreugel@gmail.com> wrote:

On Wed, Mar 9, 2016 at 7:14 PM, Luca Delucchi <lucadeluge@gmail.com> wrote:

On 9 March 2016 at 19:11, Markus Neteler <neteler@osgeo.org> wrote:
>
>
> Yeah.. I think
> LEFT OUTER JOIN
> must be used, at least with sqlite backend.

The code now assumes a 1:1 relationship, is that right?

so if Table 1
A|B
1|2
2|2

and Table 2
A|C
1|3
1|4
2|6

After joining Table 1 looks like

A|B|C
1|2|3
2|2|6

If correct, it might be good to clearly explain this in the documentation? I
wouldn't mind doing that if a final version is ready. At some point it might
be a nice feature to have the option to select the kind of relationship.

Yes - with a flag that could be solved or better a "method" parameter.

Consider this SQLite example: JOIN versus LEFT OUTER JOIN:

########
# Preparation
sqlite3 joinexample.db

# create first table holding data:
CREATE TABLE data (year, month, day, value);
INSERT INTO data VALUES(2003,10,12,12);
INSERT INTO data VALUES(2003,11,24,10);
INSERT INTO data VALUES(2003,11,25,10);
INSERT INTO data VALUES(2003,11,26,10.9);
INSERT INTO data VALUES(2003,11,27,11);
INSERT INTO data VALUES(2003,11,28,11.6);
INSERT INTO data VALUES(2003,12,2,8);

#create second table holding names
CREATE TABLE monthnames (number, month);
INSERT INTO monthnames VALUES(11,'November');
INSERT INTO monthnames VALUES(12,'December');

# check
sqlite> .schema
CREATE TABLE data (year, month, day, value);
CREATE TABLE monthnames (number, month);

sqlite> SELECT * FROM data;
2003|10|12|12
2003|11|24|10
2003|11|25|10
2003|11|26|10.9
2003|11|27|11
2003|11|28|11.6
2003|12|2|8

sqlite> SELECT * FROM monthnames;
11|November
12|December

########
# JOIN: (--> this will omit all non-matching lines!)
sqlite> SELECT data.year, data.month, monthnames.month, data.day, data.value
FROM data,monthnames
WHERE monthnames.number=data.month;
2003|11|November|24|10
2003|11|November|25|10
2003|11|November|26|10.9
2003|11|November|27|11
2003|11|November|28|11.6
2003|12|December|2|8

########
# LEFT JOIN (sort of): (--> this will keep also non-matching lines)
sqlite> SELECT data.year, data.month, monthnames.month, data.day, data.value
FROM data LEFT OUTER JOIN monthnames
ON monthnames.number=data.month;
2003|10||12|12
2003|11|November|24|10
2003|11|November|25|10
2003|11|November|26|10.9
2003|11|November|27|11
2003|11|November|28|11.6
2003|12|December|2|8

# save result to table:
sqlite> CREATE TABLE datajoin AS
        SELECT data.year, data.month, monthnames.month, data.day, data.value
        FROM data LEFT OUTER JOIN monthnames
        ON monthnames.number=data.month;

sqlite> SELECT * FROM datajoin;
2003|10||12|12
2003|11|November|24|10
2003|11|November|25|10
2003|11|November|26|10.9
2003|11|November|27|11
2003|11|November|28|11.6
2003|12|December|2|8

# Done.

So, I would like to see both methods supported one day

Markus

hellik · March 9, 2016, 10:23pm

Markus Neteler wrote

On Wed, Mar 9, 2016 at 8:11 PM, Paulo van Breugel
<

p.vanbreugel@

> wrote:

On Wed, Mar 9, 2016 at 7:14 PM, Luca Delucchi <

lucadeluge@

> wrote:

On 9 March 2016 at 19:11, Markus Neteler <

neteler@

> wrote:

>
>
> Yeah.. I think
> LEFT OUTER JOIN
> must be used, at least with sqlite backend.

The code now assumes a 1:1 relationship, is that right?

so if Table 1
A|B
1|2
2|2

and Table 2
A|C
1|3
1|4
2|6

After joining Table 1 looks like

A|B|C
1|2|3
2|2|6

If correct, it might be good to clearly explain this in the
documentation? I
wouldn't mind doing that if a final version is ready. At some point it
might
be a nice feature to have the option to select the kind of relationship.

Yes - with a flag that could be solved or better a "method" parameter.

Consider this SQLite example: JOIN versus LEFT OUTER JOIN:

########
# Preparation
sqlite3 joinexample.db

# create first table holding data:
CREATE TABLE data (year, month, day, value);
INSERT INTO data VALUES(2003,10,12,12);
INSERT INTO data VALUES(2003,11,24,10);
INSERT INTO data VALUES(2003,11,25,10);
INSERT INTO data VALUES(2003,11,26,10.9);
INSERT INTO data VALUES(2003,11,27,11);
INSERT INTO data VALUES(2003,11,28,11.6);
INSERT INTO data VALUES(2003,12,2,8);

#create second table holding names
CREATE TABLE monthnames (number, month);
INSERT INTO monthnames VALUES(11,'November');
INSERT INTO monthnames VALUES(12,'December');

# check
sqlite> .schema
CREATE TABLE data (year, month, day, value);
CREATE TABLE monthnames (number, month);

sqlite> SELECT * FROM data;
2003|10|12|12
2003|11|24|10
2003|11|25|10
2003|11|26|10.9
2003|11|27|11
2003|11|28|11.6
2003|12|2|8

sqlite> SELECT * FROM monthnames;
11|November
12|December

########
# JOIN: (--> this will omit all non-matching lines!)
sqlite> SELECT data.year, data.month, monthnames.month, data.day,
data.value
        FROM data,monthnames
        WHERE monthnames.number=data.month;
2003|11|November|24|10
2003|11|November|25|10
2003|11|November|26|10.9
2003|11|November|27|11
2003|11|November|28|11.6
2003|12|December|2|8

########
# LEFT JOIN (sort of): (--> this will keep also non-matching lines)
sqlite> SELECT data.year, data.month, monthnames.month, data.day,
data.value
        FROM data LEFT OUTER JOIN monthnames
        ON monthnames.number=data.month;
2003|10||12|12
2003|11|November|24|10
2003|11|November|25|10
2003|11|November|26|10.9
2003|11|November|27|11
2003|11|November|28|11.6
2003|12|December|2|8

# save result to table:
sqlite> CREATE TABLE datajoin AS
        SELECT data.year, data.month, monthnames.month, data.day,
data.value
        FROM data LEFT OUTER JOIN monthnames
        ON monthnames.number=data.month;

sqlite> SELECT * FROM datajoin;
2003|10||12|12
2003|11|November|24|10
2003|11|November|25|10
2003|11|November|26|10.9
2003|11|November|27|11
2003|11|November|28|11.6
2003|12|December|2|8

# Done.

So, I would like to see both methods supported one day

Markus
_______________________________________________
grass-dev mailing list

grass-dev@.osgeo

http://lists.osgeo.org/mailman/listinfo/grass-dev

+1

-----
best regards
Helmut
--
View this message in context: http://osgeo-org.1560.x6.nabble.com/db-join-script-wanted-tp5254985p5255479.html
Sent from the Grass - Dev mailing list archive at Nabble.com.