[GRASSLIST:10301] Re: joining attribute tables using SQL commands

[j b]

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
> > >