[gdal-dev] SQL "LEFT JOIN"

[Kris R. DeLaney]

et al:

Actually, the SQL page at the OGR2OGR site shows syntax for DBF joins:

http://www.gdal.org/ogr/ogr_sql.html  (scroll down the page a ways for 
actual examples)

Unfortunately, I could not get any of these examples to work either.  Maybe 
someone else can ???

I am trying to avoid going through the extra hoops of Postgre/PostGIS. 
Tentatively, before I read that OGR2OGR should do the join, I had composed 
an application in VBA using the MS Jet driver.  It works fine for batch 
joining and creating renamed DBF's (by first importing them to Access) but, 
unfortunately, the Jet driver has an 8.3 filename limitation ... so it 
creates other problems and additional work.

OGR2OGR has proved very powerful for many other tasks.  If I can get to also 
do DBF joins, I can perform all of necessary shapefile operations in one 
step.

Thanks,
Kris R. DeLaney



----- Original Message ----- 
From: "Brian Hamlin" <maplabs at light42.com>
To: "Kris R. DeLaney" <botany at strato.net>
Cc: <gdal-dev at lists.osgeo.org>
Sent: Monday, July 13, 2009 2:39 PM
Subject: Re: [gdal-dev] SQL "LEFT JOIN"


On Jul 12, 2009, at 4:56 PM, Kris R. DeLaney wrote:

> Re: GDAL 1.6.0, released 2008/11/26
>
> I have be unable to use OGR2OGR to accomplish an SQL LEFT JOIN of a 
> shapefile with the attributes of a master dbf table. If my syntax is not 
> correct, I hope someone can show me where it is wrong.
>
> The files I am using are all in the same directory. I have tried countless 
> variation of syntax, but the two which seem correct, but don't work, are:
>
> (1) ogr2ogr -sql "SELECT test.*, master.DRCLASSDCD FROM test LEFT JOIN 
> master.dbf.MUKEY ON test.MUKEY = master.MUKEY" outshpdir test.shp
>
> (2) ogr2ogr -sql "SELECT test.*, master.DRCLASSDCD FROM test LEFT JOIN 
> 'x:/JUNK/JOIN/master.dbf'.MUKEY ON test.MUKEY = master.MUKEY" outfiles 
> test.shp
>
> Small test files of actual data being tried are at: 
> http://botanicalexplorer.com/gdal/test_files.zip
>
> Any assistance would be greatly appreciated.
>
>


without knowing any better, I set up the data and tried

ogr2ogr -sql "SELECT test.*, master.DRCLASSDCD FROM test LEFT JOIN
master.MUKEY ON test.MUKEY = master.MUKEY" -f geoJSON out.json
soils.shp

as a simple way of testing.. (geoJSON is an easy human readable format)

I suspect that ogr2ogr isnt bringing in the 2nd data file at all.
though others would know better
I suspect that bringing it into Postgres (or other supperted db) would
change that

   -Brian


>
>
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/gdal-dev