[GRASS-user] v.db.join error

GRASS GRASS user
1

Hi, me again :-/

I got two vectorfiles with points - one file representing the current location of persons and the other one representing previous locations of the same persons. Persons are unique and have an id which is present in both files. I would like to calculate the distance each person has moved. Thought it would be best doing this in the datbase.

my approach:
v.db.addcolumn map=GR_xy_current col=“x_current double precision, y_current double precision, distance double precision”
v.db.addcolumn map=GR_xy_last col=“x_last double precision, y_last double precision”
v.to.db map=GR_xy_current option=coor col=x_current,y_current
v.to.db map=GR_xy_last option=coor col=x_last,y_last
v.db.join map=GR_xy_current column=id otable=GR_xy_last ocolumn=id

All commands will run except the last one, creating this error:

DBMI-Postgres driver error:
Cannot execute:
ALTER TABLE GR_xy_current ADD COLUMN x_arbeit DOUBLE PRECISION(8)
ERROR: syntax error at or near “(”
LINE 1: … TABLE GR_xy_current ADD COLUMN x_arbeit DOUBLE PRECISION(8)
^

ERROR: Error while executing: ‘ALTER TABLE GR_xy_current ADD COLUMN
x_last DOUBLE PRECISION(8)’
ERROR: Unable to add column <x_last DOUBLE PRECISION(8)>.
ERROR: Error creating column <x_last>.

The value the coord “x” has it bigger than (8), which might be the problem. But it is already in the table and I can’t define it’s length in the command. Is that a problem of v.db.join or can I set the length of the double_precision_field before creation through v.db.addcolumn?

Thanks for your hints!

Patrick

datbase: PG; Grass-version 7.0

2

Hi Patrick

··· 
-- 
Micha Silver
Arava Development Co. +972-52-3665918
[http://www.surfaces.co.il](http://www.surfaces.co.il)
3

Thanks Micha,

Yes, commands are what I typed, just renamed the filenames before sending mail to the list:
x_arbeit = x_last
x_wohn = x_current

That syntax is an error in postgres: double precision does not take a length qualifier.
There’s nothing in the v.db.join script that I can find that would add such a thing."

Is it a problem of postgres or of v.db.addcolumn which is called within v.db.join? Can someone reproduce this mistake?

Cheers, Patrick

On 12/01/2010 08:48 PM, Micha Silver wrote:

Hi Patrick

On 12/01/2010 05:29 PM, Patrick_schirmer wrote:

Hi, me again :-/

I got two vectorfiles with points - one file representing the current location of persons and the other one representing previous locations of the same persons. Persons are unique and have an id which is present in both files. I would like to calculate the distance each person has moved. Thought it would be best doing this in the datbase.

my approach:
v.db.addcolumn map=GR_xy_current col=“x_current double precision, y_current double precision, distance double precision”
v.db.addcolumn map=GR_xy_last col=“x_last double precision, y_last double precision”
v.to.db map=GR_xy_current option=coor col=x_current,y_current
v.to.db map=GR_xy_last option=coor col=x_last,y_last
v.db.join map=GR_xy_current column=id otable=GR_xy_last ocolumn=id

All commands will run except the last one, creating this error:

DBMI-Postgres driver error:
Cannot execute:
ALTER TABLE GR_xy_current ADD COLUMN x_arbeit DOUBLE PRECISION(8)
ERROR: syntax error at or near “(”
LINE 1: … TABLE GR_xy_current ADD COLUMN x_arbeit DOUBLE PRECISION(8)
^

ERROR: Error while executing: 'ALTER TABLE GR_xy_current ADD COLUMN

x_last DOUBLE PRECISION(8)’
ERROR: Unable to add column <x_last DOUBLE PRECISION(8)>.
ERROR: Error creating column <x_last>.

datbase: PG; Grass-version 7.0

4

Thanks Micha,

Yes, commands are what I typed, just renamed the filenames before sending mail to the list:
x_arbeit = x_last
x_wohn = x_current

That syntax is an error in postgres: double precision does not take a length qualifier.
There’s nothing in the v.db.join script that I can find that would add such a thing."

Is it a problem of postgres or of v.db.addcolumn which is called within v.db.join? Can someone reproduce this mistake?

Well, postgresql doesn’t accept a length qualifier for the type “double precision”. But the grass module v.db.addcol doesn’t add any qualifier either. So I can’t see where the error is originating from.

Would you mind to post the output of:
v.info -c
v.db.connect -p
for each of the point vectors?
Maybe that will give us some hint…

··· 
-- 
Micha Silver
Arava Development Co. +972-52-3665918
[http://www.surfaces.co.il](http://www.surfaces.co.il)
5

Thanks Micha,

Yes, commands are what I typed, just renamed the filenames before sending mail to the list:
x_arbeit = x_last
x_wohn = x_current

That syntax is an error in postgres: double precision does not take a length qualifier.
There’s nothing in the v.db.join script that I can find that would add such a thing."

Is it a problem of postgres or of v.db.addcolumn which is called within v.db.join? Can someone reproduce this mistake?

Well, postgresql doesn’t accept a length qualifier for the type “double precision”. But the grass module v.db.addcol doesn’t add any qualifier either. So I can’t see where the error is originating from.

Would you mind to post the output of:
v.info -c
v.db.connect -p
for each of the point vectors?
Maybe that will give us some hint…

And one more test, please:
db.describe table=

··· 
-- 
Micha Silver
Arava Development Co. +972-52-3665918
[http://www.surfaces.co.il](http://www.surfaces.co.il)
 



_______________________________________________
grass-user mailing list
[grass-user@lists.osgeo.org](mailto:grass-user@lists.osgeo.org)
[http://lists.osgeo.org/mailman/listinfo/grass-user](http://lists.osgeo.org/mailman/listinfo/grass-user)

This mail was received via Mail-SeCure System.


-- 
Micha Silver
[http://www.surfaces.co.il/](http://www.surfaces.co.il/)
Arava Development Co.  +972-52-3665918
6

Micha,

attached a txt-file with the output you asked for. Following workaround did work for the files, which makes me think that it is not a problem of postgresql:

v.db.addcolumn map=GR_xy_wohnort col=“x_arbeit double precision, y_arbeit double precision”
echo “UPDATE GR_xy_wohnort SET x_arbeit=(SELECT x_arbeit FROM GR_xy_arbeitsort WHERE GR_xy_arbeitsort.id=GR_xy_wohnort.id)” > help1.txt
echo “UPDATE GR_xy_wohnort SET y_arbeit=(SELECT y_arbeit FROM GR_xy_arbeitsort WHERE GR_xy_arbeitsort.id=GR_xy_wohnort.id)” > help2.txt
db.execute help1.txt
db.execute help2.txt
rm help2.txt
rm help1.txt

Cheers, Patrick

On 12/03/2010 03:47 PM, Micha Silver wrote:

On 03/12/2010 16:01, Micha Silver wrote:

On 12/03/2010 09:53 AM, Patrick_schirmer wrote:

Thanks Micha,

Yes, commands are what I typed, just renamed the filenames before sending mail to the list:
x_arbeit = x_last
x_wohn = x_current

That syntax is an error in postgres: double precision does not take a length qualifier.
There’s nothing in the v.db.join script that I can find that would add such a thing."

Is it a problem of postgres or of v.db.addcolumn which is called within v.db.join? Can someone reproduce this mistake?

Well, postgresql doesn’t accept a length qualifier for the type “double precision”. But the grass module v.db.addcol doesn’t add any qualifier either. So I can’t see where the error is originating from.

Would you mind to post the output of:
v.info -c
v.db.connect -p
for each of the point vectors?
Maybe that will give us some hint…

And one more test, please:
db.describe table=

Cheers, Patrick

On 12/01/2010 08:48 PM, Micha Silver wrote:

Hi Patrick

On 12/01/2010 05:29 PM, Patrick_schirmer wrote:

Hi, me again :-/

I got two vectorfiles with points - one file representing the current location of persons and the other one representing previous locations of the same persons. Persons are unique and have an id which is present in both files. I would like to calculate the distance each person has moved. Thought it would be best doing this in the datbase.

my approach:
v.db.addcolumn map=GR_xy_current col=“x_current double precision, y_current double precision, distance double precision”
v.db.addcolumn map=GR_xy_last col=“x_last double precision, y_last double precision”
v.to.db map=GR_xy_current option=coor col=x_current,y_current
v.to.db map=GR_xy_last option=coor col=x_last,y_last
v.db.join map=GR_xy_current column=id otable=GR_xy_last ocolumn=id

All commands will run except the last one, creating this error:

DBMI-Postgres driver error:
Cannot execute:
ALTER TABLE GR_xy_current ADD COLUMN x_arbeit DOUBLE PRECISION(8)
ERROR: syntax error at or near “(”
LINE 1: … TABLE GR_xy_current ADD COLUMN x_arbeit DOUBLE PRECISION(8)
^

ERROR: Error while executing: 'ALTER TABLE GR_xy_current ADD COLUMN

x_last DOUBLE PRECISION(8)’
ERROR: Unable to add column <x_last DOUBLE PRECISION(8)>.
ERROR: Error creating column <x_last>.

datbase: PG; Grass-version 7.0

(attachments)

PG_n_vector_description.txt (5.09 KB)

7

Micha,

attached a txt-file with the output you asked for. Following workaround did work for the files, which makes me think that it is not a problem of postgresql:

I’m glad you found a way to solve the problem, but if you don’t mind to help find why it didn’t “just work” with v.db.join as it should, then another check please:
Can you do db.describe with the -c option as it appears in the v.db.join script. So:

db.describe -c driver=pg database= table= | grep ‘^Column’ | cut -d ‘:’ -f 3 | tr -s ’ ’ ‘_’

(The above all on one line)
What output do you get ? You should see a string of the column names. And the doulbe precision columns should appear as “DOUBLE_PRECISION” ( with no width after the name)

Thanks,
Micha

··· 
-- 
Micha Silver
[http://www.surfaces.co.il/](http://www.surfaces.co.il/)
Arava Development Co.  +972-52-3665918
8

Micha,

Sure, hope that I can help to find the problem as I can't solve it myself (yet). Here the results of the commands.....

GRASS 7.0.svn (Survey):~ > db.describe -c driver=pg database=schirpat table=gr_xy_wohnort | grep '^Column' | cut -d ':' -f 3 | tr -s ' ' '_'
INTEGER
CHARACTER
INTEGER
CHARACTER
DOUBLE_PRECISION
CHARACTER

GRASS 7.0.svn (Survey):~ > db.describe -c driver=pg database=schirpat table=gr_xy_arbeitsort | grep '^Column' | cut -d ':' -f 3 | tr -s ' ' '_'
INTEGER
CHARACTER
INTEGER
CHARACTER
DOUBLE_PRECISION

Looks pretty much as it should be to me.
Patrick

I'm glad you found a way to solve the problem, but if you don't mind to help find why it didn't "just work" with v.db.join as it should, then another check please:
Can you do db.describe with the -c option as it appears in the v.db.join script. So:

db.describe -c driver=pg database=<your database> table=<your table> | grep '^Column' | cut -d ':' -f 3 | tr -s ' ' '_'

(The above all on one line)
What output do you get ? You should see a string of the column names. And the doulbe precision columns should appear as "DOUBLE_PRECISION" ( with *no* width after the name)

Thanks,
Micha

v.db.addcolumn map=GR_xy_wohnort col="x_arbeit double precision, y_arbeit double precision"
echo "UPDATE GR_xy_wohnort SET x_arbeit=(SELECT x_arbeit FROM GR_xy_arbeitsort WHERE GR_xy_arbeitsort.id=GR_xy_wohnort.id)" > help1.txt
echo "UPDATE GR_xy_wohnort SET y_arbeit=(SELECT y_arbeit FROM GR_xy_arbeitsort WHERE GR_xy_arbeitsort.id=GR_xy_wohnort.id)" > help2.txt
db.execute help1.txt
db.execute help2.txt
rm help2.txt
rm help1.txt

Cheers, Patrick