[GRASSLIST:10279] joining attribute tables using SQL commands

GRASS GRASS user
1

hello.
I am a beginner trying to join attribute tables.

To be more specific, I have a .dbf table called streetcl, which gives street
blocks and their various attributes. It contains an attribute called FNODE_,
which is the ID of its starting intersection. I used the command:

echo "ALTER TABLE streetcl ADD COLUMN ELEVFROM double" | db.execute

to create an attribute called ELEVFROM. In this column, i want to put the
elevation of the intersection where each block originates. I have another
.dbf table called intersect which gives the city's intersections; it has a column called ELEV (the intersection's elevation) and a column called STREETCL_ (the ID of the
intersection as referred to in the streetcl table.)

This seems like it should not be so complicated, but i'm getting SQL parser errors.

My first attempt was this command:

echo "UPDATE streetcl SET ELEVFROM=(SELECT ELEV FROM intersect WHERE
intersect.STREETCL_=streetcl.FNODE_)" | db.execute

This tells me:
DBMI-DBF driver error:
SQL parser error in statement:
...

Then, to test, I tried to do more simple commands. I created a column in
streetcl called TEST, and tried to set it equal to another column (same type)
in streetcl, called LENGTH.

echo "UPDATE streetcl SET TEST=LENGTH" | db.execute

this gives me the same error. A command like:

echo "UPDATE streetcl SET TEST=30" | db.execute

is fine, but even:
echo "UPDATE streetcl SET TEST=30+2" | db.execute
(with or w/out parens around "30+2) gives me the same SQL parser error.

Any idea what is going on? Am i even going about this in the right way?
Should i be using SQL commands or is there another approach entirely?
thanks very much.
jessi

2

Hi Jessi,
unfortunately, it is not possible to join tables with the dbf-driver. However,
if you use sqlite, Postgres or Mysql it's possible. There is an example for
sqlite on this list:

--> http://article.gmane.org/gmane.comp.gis.grass.user/10882/match=sqlite+join

Best regards,

Wolfgang

--

Am Montag 13 Februar 2006 pH:11:21 nachmittags/abends schrieb j b:

hello.
I am a beginner trying to join attribute tables.

To be more specific, I have a .dbf table called streetcl, which gives
street blocks and their various attributes. It contains an attribute called
FNODE_, which is the ID of its starting intersection. I used the command:

echo "ALTER TABLE streetcl ADD COLUMN ELEVFROM double" | db.execute

to create an attribute called ELEVFROM. In this column, i want to put the
elevation of the intersection where each block originates. I have another
.dbf table called intersect which gives the city's intersections; it has a
column called ELEV (the intersection's elevation) and a column called
STREETCL_ (the ID of the
intersection as referred to in the streetcl table.)

This seems like it should not be so complicated, but i'm getting SQL parser
errors.

My first attempt was this command:

echo "UPDATE streetcl SET ELEVFROM=(SELECT ELEV FROM intersect WHERE
intersect.STREETCL_=streetcl.FNODE_)" | db.execute

This tells me:
DBMI-DBF driver error:
SQL parser error in statement:
...

Then, to test, I tried to do more simple commands. I created a column in
streetcl called TEST, and tried to set it equal to another column (same
type)
in streetcl, called LENGTH.

echo "UPDATE streetcl SET TEST=LENGTH" | db.execute

this gives me the same error. A command like:

echo "UPDATE streetcl SET TEST=30" | db.execute

is fine, but even:
echo "UPDATE streetcl SET TEST=30+2" | db.execute
(with or w/out parens around "30+2) gives me the same SQL parser error.

Any idea what is going on? Am i even going about this in the right way?
Should i be using SQL commands or is there another approach entirely?
thanks very much.
jessi

3

Hello.
thanks for the help. what about using ogr2ogr, or ogrinfo? i have both of these running on my computer, but was also getting SQL parser errors when i tried pretty simple SQL commands. is it just an extremely simplified version of SQL?
i can also try using sqlite----i tried that earlier looking at that previous post, but i'll give it another try. i'll still have .dbf files at the end if i manipulate them with the sqlite driver, right?
thanks again,
jessi

Hi Jessi,
unfortunately, it is not possible to join tables with the dbf-driver. However,
if you use sqlite, Postgres or Mysql it's possible. There is an example for
sqlite on this list:

--> http://article.gmane.org/gmane.comp.gis.grass.user/10882/match=sqlite+join

Best regards,

Wolfgang

--

Am Montag 13 Februar 2006 pH:11:21 nachmittags/abends schrieb j b:
> hello.
> I am a beginner trying to join attribute tables.
>
> To be more specific, I have a .dbf table called streetcl, which gives
> street blocks and their various attributes. It contains an attribute called
> FNODE_, which is the ID of its starting intersection. I used the command:
>
> echo "ALTER TABLE streetcl ADD COLUMN ELEVFROM double" | db.execute
>
> to create an attribute called ELEVFROM. In this column, i want to put the
> elevation of the intersection where each block originates. I have another
> .dbf table called intersect which gives the city's intersections; it has a
> column called ELEV (the intersection's elevation) and a column called
> STREETCL_ (the ID of the
> intersection as referred to in the streetcl table.)
>
> This seems like it should not be so complicated, but i'm getting SQL parser
> errors.
>
> My first attempt was this command:
>
> echo "UPDATE streetcl SET ELEVFROM=(SELECT ELEV FROM intersect WHERE
> intersect.STREETCL_=streetcl.FNODE_)" | db.execute
>
> This tells me:
> DBMI-DBF driver error:
> SQL parser error in statement:
> ...
>
> Then, to test, I tried to do more simple commands. I created a column in
> streetcl called TEST, and tried to set it equal to another column (same
> type)
> in streetcl, called LENGTH.
>
> echo "UPDATE streetcl SET TEST=LENGTH" | db.execute
>
> this gives me the same error. A command like:
>
> echo "UPDATE streetcl SET TEST=30" | db.execute
>
> is fine, but even:
> echo "UPDATE streetcl SET TEST=30+2" | db.execute
> (with or w/out parens around "30+2) gives me the same SQL parser error.
>
> Any idea what is going on? Am i even going about this in the right way?
> Should i be using SQL commands or is there another approach entirely?
> thanks very much.
> jessi

4

Hi Jessi,
it would be helpful, if you could paste the commands and error-messages in
your posts (what exactly did you try to do with ogr2ogr/ogrinfo?). And: yes,
SQL-support of dbf is quite limited [1].

Hello.
thanks for the help. what about using ogr2ogr, or ogrinfo? i have both of
these running on my computer, but was also getting SQL parser errors when i
tried pretty simple SQL commands. is it just an extremely simplified
version of SQL?
i can also try using sqlite----i tried that earlier looking at that
previous post, but i'll give it another try. i'll still have .dbf files at
the end if i manipulate them with the sqlite driver, right?

No, because you'll have to switch to another database using db.connect. This
link [2] might be helpful for you.

thanks again,
jessi

Best regards,

Wolfgang

[1] http://grass.itc.it/grass61/manuals/html61_user/dbf.html
[2] http://grass.itc.it/grass61/manuals/html61_user/db.connect.html

>Hi Jessi,
>unfortunately, it is not possible to join tables with the dbf-driver.
>However,
>if you use sqlite, Postgres or Mysql it's possible. There is an example
> for sqlite on this list:
>
>-->
>http://article.gmane.org/gmane.comp.gis.grass.user/10882/match=sqlite+join
>
>Best regards,
>
>Wolfgang
>
>--
>
>Am Montag 13 Februar 2006 pH:11:21 nachmittags/abends schrieb j b:
> > hello.
> > I am a beginner trying to join attribute tables.
> >
> > To be more specific, I have a .dbf table called streetcl, which gives
> > street blocks and their various attributes. It contains an attribute
>
>called
>
> > FNODE_, which is the ID of its starting intersection. I used the
>
>command:
> > echo "ALTER TABLE streetcl ADD COLUMN ELEVFROM double" | db.execute
> >
> > to create an attribute called ELEVFROM. In this column, i want to put
>
>the
>
> > elevation of the intersection where each block originates. I have
>
>another
>
> > .dbf table called intersect which gives the city's intersections; it
> > has
>
>a
>
> > column called ELEV (the intersection's elevation) and a column called
> > STREETCL_ (the ID of the
> > intersection as referred to in the streetcl table.)
> >
> > This seems like it should not be so complicated, but i'm getting SQL
>
>parser
>
> > errors.
> >
> > My first attempt was this command:
> >
> > echo "UPDATE streetcl SET ELEVFROM=(SELECT ELEV FROM intersect WHERE
> > intersect.STREETCL_=streetcl.FNODE_)" | db.execute
> >
> > This tells me:
> > DBMI-DBF driver error:
> > SQL parser error in statement:
> > ...
> >
> > Then, to test, I tried to do more simple commands. I created a column
> > in streetcl called TEST, and tried to set it equal to another column
> > (same type)
> > in streetcl, called LENGTH.
> >
> > echo "UPDATE streetcl SET TEST=LENGTH" | db.execute
> >
> > this gives me the same error. A command like:
> >
> > echo "UPDATE streetcl SET TEST=30" | db.execute
> >
> > is fine, but even:
> > echo "UPDATE streetcl SET TEST=30+2" | db.execute
> > (with or w/out parens around "30+2) gives me the same SQL parser error.
> >
> > Any idea what is going on? Am i even going about this in the right way?
> > Should i be using SQL commands or is there another approach entirely?
> > thanks very much.
> > jessi

5

Hi Wolfgang:
thanks for the help so far.
according to documentation i have read (such as http://mapserver.gis.umn.edu/docs/howto/ogr_howto & http://www.gdal.org/ogr/ogr_sql.html) i should be able to use ogrinfo or possibly ogr2ogr
to join databases. however, i'm having trouble with it.
my intention was to use a SQL command like:

UPDATE streetcl SET ELEVFROM=(SELECT ELEV FROM intersect WHERE intersect.STREETCL_=streetcl.FNODE_)

or

SELECT streetcl.*, intersect.ELEV FROM streetcl LEFT JOIN intersect ON streetcl.FNODE_ =intersect.STREETCL_

to join the databases. these both gave me errors when i gave them as the sql parameter to ogrinfo (the first was looking for a SELECT, and the second couldn't find the FROM). i tried to break it down to simpler SQL commands, as shown below, and got the same errors. For example:

ogrinfo /home/jessi/moreGrass streetcl -sql 'SELECT * FROM streetcl'

gave this feedback:
INFO: Open of `/home/jessi/moreGrass'
using driver `ESRI Shapefile' successful.
layer names ignored in combination with -sql.
ERROR 1: SQL: Missing FROM clause in SELECT statement.

this confuses me, as there is a FROM clause. the command
ogrinfo /home/jessi/moreGrass streetcl -sql 'UPDATE streetcl SET TEST=LENGTH'

gave me this:
INFO: Open of `/home/jessi/moreGrass'
using driver `ESRI Shapefile' successful.
layer names ignored in combination with -sql.
ERROR 1: SQL: Missing keyword SELECT

hopefully this can give you an idea. it seems to me that there must be a problem with the SQL parser being used, but maybe not. the DBF driver is supposed to support these commands.
thanks again.
take care,
jessi

Hi Jessi,
it would be helpful, if you could paste the commands and error-messages in
your posts (what exactly did you try to do with ogr2ogr/ogrinfo?). And: yes,
SQL-support of dbf is quite limited [1].

> Hello.
> thanks for the help. what about using ogr2ogr, or ogrinfo? i have both of
> these running on my computer, but was also getting SQL parser errors when i
> tried pretty simple SQL commands. is it just an extremely simplified
> version of SQL?
> i can also try using sqlite----i tried that earlier looking at that
> previous post, but i'll give it another try. i'll still have .dbf files at
> the end if i manipulate them with the sqlite driver, right?
No, because you'll have to switch to another database using db.connect. This
link [2] might be helpful for you.

> thanks again,
> jessi

Best regards,

Wolfgang

[1] http://grass.itc.it/grass61/manuals/html61_user/dbf.html
[2] http://grass.itc.it/grass61/manuals/html61_user/db.connect.html
>
> >Hi Jessi,
> >unfortunately, it is not possible to join tables with the dbf-driver.
> >However,
> >if you use sqlite, Postgres or Mysql it's possible. There is an example
> > for sqlite on this list:
> >
> >-->
> >http://article.gmane.org/gmane.comp.gis.grass.user/10882/match=sqlite+join
> >
> >Best regards,
> >
> >Wolfgang
> >