OGR-join problem with DBF files

[Frank Warmerdam]

On Mon, 7 Mar 2005 07:12:37 -0600, Rainer Spittel
<gosia.rainer at t-online.de> wrote:
>     CONNECTIONTYPE OGR
>     CONNECTION "ms_wasser/baden/badeseenbremen.shp"
>     DATA "SELECT * FROM badeseenbremen LEFT JOIN ms_wasser/baden/bade ON
> badeseenbremen.GewaessNr = bade.GewaessNr"
>     [...]
...
> Warning: [MapServer Error]: msOGRFileOpen(): ExecuteSQL(SELECT * FROM
> badeseenbremen LEFT JOIN ms_wasser/baden/bade ON badeseenbremen.GewaessNr =
> bade.GewaessNr) failed. SQL: Corrupt JOIN clause, expecting ON keyword. in

Rainer,

In the above, you can't have slashes in the target table
identifier.  That is ms_wasser/baden/bade is not valid.  Since
the .dbf file (bade.dbf) is in the same directory as the shapefile,
you could use "ms_wasser/baden/badeseenbremen" as your
datasource name, and then just refer to tables (layers)
badeseenbremen and bade by name.  Something like:

   CONNECTIONTYPE OGR
   CONNECTION "ms_wasser/baden/badeseenbremen"
   DATA "SELECT * FROM badeseenbremen LEFT JOIN bade ON
badeseenbremen.GewaessNr = bade.GewaessNr"

> >ogrinfo.exe badeseenbremen.dbf -sql "SELECT * FROM
> badeseenbremen LEFT JOIN bade.dbf ON badeseenbremen.GewaessNr = bade.GewaessNr"
> INFO: Open of `badeseenbremen.dbf'
> using driver `ESRI Shapefile' successful.
> ERROR 1: SELECT from table bade.dbf failed, no such table/featureclass.

Here again the problem is that you can't use bade.dbf as a table name.
If you wanted to select badeseenbremen.dbf directly above as your
datasource, you would need to quote the different datasource name of
the joined table.  This would be something like:

ogrinfo badeseenbremen.dbf  -sql "SELECT * FROM
   badeseenbremen LEFT JOIN 'bade.dbf'.bade ON
badeseenbremen.GewaessNr = bade.GewaessNr"

Be wary of quoting at the win32 command shell ... there might be problems.

I hope this helps.

Best regards,
--
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent