[GRASS-user] removing join

GRASS GRASS user
1

Hello,
I have performed a join between a vector map and a postgreSQL table using v.db.join. I would like to remove this join now. I cannot figure out how to do this.
Thank you Jennifer

2

Hello,
I have performed a join between a vector map and a postgreSQL table using v.db.join. I would like to remove this join now. I cannot figure out how to do this.

I think you can just drop the additional columns with v.db.dropcol.

··· 
-- 
Micha Silver
GIS Consultant, Arava Development Co.
[http://www.surfaces.co.il](http://www.surfaces.co.il)
3

Thank you Micha for the quick response. When I perform this join through the GUI on Windows I get the following error

ERROR: value too long for type character (1)

and all my added fields which are of data type character have a length of 1 instead of the original 255. Any ideas what is going on? I am using postgreSQL 9.1 and GRASS 6.4.2 on Windows.
Thanks Jennifer

On 3/28/2012 12:56 PM, Micha Silver wrote:

On 03/28/2012 08:49 PM, Jennifer Boehnert wrote:

Hello,
I have performed a join between a vector map and a postgreSQL table
using v.db.join. I would like to remove this join now. I cannot
figure out how to do this.

I think you can just drop the additional columns with v.db.dropcol.

Thank you Jennifer

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

This mail was received via Mail-SeCure System.

--
Micha Silver
GIS Consultant, Arava Development Co.
http://www.surfaces.co.il

--
Jennifer Boehnert
GIS Coordinator
National Center of Atmospheric Research
PO Box 3000
Boulder, CO, 80307

4

On 03/28/2012 10:38 PM, Jennifer Boehnert wrote:

Thank you Micha for the quick response. When I perform this join through the GUI on Windows I get the following error

ERROR: value too long for type character (1)

Hi Jennifer:
Ahhh, I remember something about a bug in the v.db.join script.

The script first gets a list of columns from the join table, then loops thru that list, creates a new column in the target table, and runs an UPDATE to get all the values from the join table into the target. The trouble comes with columns of type CHARACTER - the script does not check the length of the field, so by default (in Postgresql) it creates a field of length 1.
Here's from the PostgreSQL manual:

The notationsvarchar(n)andchar(n)are aliases forcharacter varying(n)andcharacter(n), respectively.characterwithout length specifier is equivalent tocharacter(1). Ifcharacter varyingis used without length specifier, the type accepts strings of any size. The latter is aPostgreSQLextension.

Since the script uses the column definition "CHARACTER" and not "CHARACTER VARYING" the default length is 1.

Here's a way to work around the problem. Let's Suppose you have a target vector called 'vect', and the join table called 'otable', your text column is 'long_text', and the id columns for the join are 'cat' and 'oid', then

# first drop the wrong (length 1) text column
v.db.dropcol vect col=long_text
# Now recreate that column correctly
v.db.addcol vect col="long_text varchar(255)"
# And finally the update
echo "UPDATE vect SET long_text=(SELECT long_text
     FROM otable
     WHERE otable.oid=vect.cat);" | db.execute database=<your db> driver=pg

That's a one time fix. In order to get this working correctly permanently, changes are required to the v.db.join script. I'll suggest this to the devs.

Regards,
Micha

and all my added fields which are of data type character have a length of 1 instead of the original 255. Any ideas what is going on? I am using postgreSQL 9.1 and GRASS 6.4.2 on Windows.
Thanks Jennifer

On 3/28/2012 12:56 PM, Micha Silver wrote:

On 03/28/2012 08:49 PM, Jennifer Boehnert wrote:

Hello,
I have performed a join between a vector map and a postgreSQL table
using v.db.join. I would like to remove this join now. I cannot
figure out how to do this.

I think you can just drop the additional columns with v.db.dropcol.

Thank you Jennifer

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

This mail was received via Mail-SeCure System.

--
Micha Silver
GIS Consultant, Arava Development Co.
http://www.surfaces.co.il

--
Micha Silver
GIS Consultant, Arava Development Co.
http://www.surfaces.co.il

5

Thank you very much for the workaround. I will give that a try.
Jennifer

On 3/30/2012 1:46 AM, Micha Silver wrote:

On 03/28/2012 10:38 PM, Jennifer Boehnert wrote:

Thank you Micha for the quick response. When I perform this join
through the GUI on Windows I get the following error

ERROR: value too long for type character (1)

Hi Jennifer:
Ahhh, I remember something about a bug in the v.db.join script.

The script first gets a list of columns from the join table, then loops
thru that list, creates a new column in the target table, and runs an
UPDATE to get all the values from the join table into the target. The
trouble comes with columns of type CHARACTER - the script does not check
the length of the field, so by default (in Postgresql) it creates a
field of length 1.
Here's from the PostgreSQL manual:

The notationsvarchar(n)andchar(n)are aliases forcharacter
varying(n)andcharacter(n), respectively.characterwithout length
specifier is equivalent tocharacter(1). Ifcharacter varyingis used
without length specifier, the type accepts strings of any size. The
latter is aPostgreSQLextension.

Since the script uses the column definition "CHARACTER" and not
"CHARACTER VARYING" the default length is 1.

Here's a way to work around the problem. Let's Suppose you have a target
vector called 'vect', and the join table called 'otable', your text
column is 'long_text', and the id columns for the join are 'cat' and
'oid', then

# first drop the wrong (length 1) text column
v.db.dropcol vect col=long_text
# Now recreate that column correctly
v.db.addcol vect col="long_text varchar(255)"
# And finally the update
echo "UPDATE vect SET long_text=(SELECT long_text
FROM otable
WHERE otable.oid=vect.cat);" | db.execute database=<your db> driver=pg

That's a one time fix. In order to get this working correctly
permanently, changes are required to the v.db.join script. I'll suggest
this to the devs.

Regards,
Micha

and all my added fields which are of data type character have a length
of 1 instead of the original 255. Any ideas what is going on? I am
using postgreSQL 9.1 and GRASS 6.4.2 on Windows.
Thanks Jennifer

On 3/28/2012 12:56 PM, Micha Silver wrote:

On 03/28/2012 08:49 PM, Jennifer Boehnert wrote:

Hello,
I have performed a join between a vector map and a postgreSQL table
using v.db.join. I would like to remove this join now. I cannot
figure out how to do this.

I think you can just drop the additional columns with v.db.dropcol.

Thank you Jennifer

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

This mail was received via Mail-SeCure System.

--
Micha Silver
GIS Consultant, Arava Development Co.
http://www.surfaces.co.il

--
Jennifer Boehnert
GIS Coordinator
National Center of Atmospheric Research
PO Box 3000
Boulder, CO, 80307

6

I recall a similar problem with MySQL, and that Micha Silver produced a join2 script that did not truncate (maybe it can be found in the mail archives?), - this improved script has, apparently, not made it into the most recent releases. SQLite manages with the current join, at least on my Mac.

Jon

On 30.3.2012, at 09:46, Micha Silver wrote:

On 03/28/2012 10:38 PM, Jennifer Boehnert wrote:

Thank you Micha for the quick response. When I perform this join through the GUI on Windows I get the following error

ERROR: value too long for type character (1)

Hi Jennifer:
Ahhh, I remember something about a bug in the v.db.join script.

The script first gets a list of columns from the join table, then loops thru that list, creates a new column in the target table, and runs an UPDATE to get all the values from the join table into the target. The trouble comes with columns of type CHARACTER - the script does not check the length of the field, so by default (in Postgresql) it creates a field of length 1.
Here's from the PostgreSQL manual:

The notationsvarchar(n)andchar(n)are aliases forcharacter varying(n)andcharacter(n), respectively.characterwithout length specifier is equivalent tocharacter(1). Ifcharacter varyingis used without length specifier, the type accepts strings of any size. The latter is aPostgreSQLextension.

Since the script uses the column definition "CHARACTER" and not "CHARACTER VARYING" the default length is 1.

Here's a way to work around the problem. Let's Suppose you have a target vector called 'vect', and the join table called 'otable', your text column is 'long_text', and the id columns for the join are 'cat' and 'oid', then

# first drop the wrong (length 1) text column
v.db.dropcol vect col=long_text
# Now recreate that column correctly
v.db.addcol vect col="long_text varchar(255)"
# And finally the update
echo "UPDATE vect SET long_text=(SELECT long_text
   FROM otable
   WHERE otable.oid=vect.cat);" | db.execute database=<your db> driver=pg

That's a one time fix. In order to get this working correctly permanently, changes are required to the v.db.join script. I'll suggest this to the devs.

Regards,
Micha

and all my added fields which are of data type character have a length of 1 instead of the original 255. Any ideas what is going on? I am using postgreSQL 9.1 and GRASS 6.4.2 on Windows.
Thanks Jennifer

On 3/28/2012 12:56 PM, Micha Silver wrote:

On 03/28/2012 08:49 PM, Jennifer Boehnert wrote:

Hello,
I have performed a join between a vector map and a postgreSQL table
using v.db.join. I would like to remove this join now. I cannot
figure out how to do this.

I think you can just drop the additional columns with v.db.dropcol.

Thank you Jennifer

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

This mail was received via Mail-SeCure System.

--
Micha Silver
GIS Consultant, Arava Development Co.
http://www.surfaces.co.il

--
Micha Silver
GIS Consultant, Arava Development Co.
http://www.surfaces.co.il

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

7 
I recall a similar problem with MySQL, and that Micha Silver produced a join2 script that did not truncate (maybe it can be found in the mail archives?), - this improved script has, apparently, not made it into the most recent releases. SQLite manages with the current join, at least on my Mac.

Issue opened on trac:
http://trac.osgeo.org/grass/ticket/1631
I’m looking forward to feedback…

The problem does not occur with sqlite because it’s very lax in data types. Sqlite ignores all the length restrictions to data types, and somehow dynamically allocates as much space is needed for each data field. So a column of type CHARACTER is the same as TEXT and the same as VARCHAR(8), or VARCHAR(255) etc.

Regards,
Micha

··· 
-- 
Micha Silver
GIS Consultant, Arava Development Co.
[http://www.surfaces.co.il](http://www.surfaces.co.il)
8

Well, the join2 solved the data length problem and worked for me while I
was using MySQL, but I currently use SQLite as I find it less cumbersome
to set up and manage.
Regards, Jon

> body p { margin-bottom: 0cm; margin-top: 0pt; } On

03/30/2012 07:34 PM, Jon Eiriksson wrote: I recall
a similar problem with MySQL, and that Micha Silver produced
a join2 script that did not truncate (maybe it can be found
in the mail archives?), - this improved script has,
apparently, not made it into the most recent releases. SQLite
manages with the current join, at least on my Mac.
     Issue opened on trac:
     http://trac.osgeo.org/grass/ticket/1631
     I'm looking forward to feedback...

     The problem does not occur with sqlite because it's very lax in data
   types. Sqlite ignores all the length restrictions to data types,
and somehow dynamically allocates as much space is needed for
each data field. So a column of type CHARACTER is the same as
TEXT and the same as VARCHAR(8), or VARCHAR(255) etc.

     Regards,
     Micha

             Jon On 30.3.2012, at 09:46, Micha Silver wrote:
    On 03/28/2012 10:38 PM, Jennifer Boehnert wrote:
           Thank you Micha for the quick response. When I
perform this join through the GUI on Windows I get the
following error ERROR: value too long for type character (1)
                   Hi Jennifer: Ahhh, I remember something
about a bug in the v.db.join script. The script first gets a
list of columns from the join table, then loops thru that
list, creates a new column in the target table, and runs an
UPDATE to get all the values from the join table into the
target. The trouble comes with columns of type CHARACTER -
the script does not check the length of the field, so by
default (in Postgresql) it creates a field of length 1.
Here's from the PostgreSQL manual: The
notationsvarchar(n)andchar(n)are aliases forcharacter
varying(n)andcharacter(n), respectively.characterwithout
length specifier is equivalent tocharacter(1). Ifcharacter
varyingis used without length specifier, the type accepts
strings of any size. The latter is aPostgreSQLextension.
Since the script uses the column definition "CHARACTER" and
not "CHARACTER VARYING" the default length is 1. Here's a
way to work around the problem. Let's Suppose you have a
target vector called 'vect', and the join table called
'otable', your text column is 'long_text', and the id columns
for the join are 'cat' and 'oid', then # first drop the
wrong (length 1) text column v.db.dropcol vect col=long_text
# Now recreate that column correctly v.db.addcol vect
col="long_text varchar(255)" # And finally the update echo
"UPDATE vect SET long_text=(SELECT long_text FROM otable
WHERE otable.oid=vect.cat);" | db.execute database=
driver=pg That's a one time fix. In order to get this
working correctly permanently, changes are required to the
v.db.join script. I'll suggest this to the devs. Regards,
Micha and all my added fields which are
of data type character have a length of 1 instead of the
original 255. Any ideas what is going on? I am using
postgreSQL 9.1 and GRASS 6.4.2 on Windows. Thanks Jennifer
On 3/28/2012 12:56 PM, Micha Silver wrote:
     On 03/28/2012 08:49 PM, Jennifer Boehnert wrote:
                    Hello, I have performed a join between a
vector map and a postgreSQL table using v.db.join. I would
like to remove this join now. I cannot figure out how to do
this. I think you can just drop
the additional columns with v.db.dropcol.
         Thank you Jennifer
_______________________________________________ grass-user
mailing list grass-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-user This mail
was received via Mail-SeCure System.
     -- Micha Silver GIS Consultant, Arava Development Co.
http://www.surfaces.co.il
        -- Micha Silver GIS Consultant, Arava Development
Co. http://www.surfaces.co.il
_______________________________________________ grass-user
mailing list grass-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-user
     This mail was received via Mail-SeCure System.

     -- Micha Silver GIS Consultant, Arava Development Co.
http://www.surfaces.co.il