[gdal-dev] SQL "LEFT JOIN"

[Dylan Beaudette]

On Monday 13 July 2009, Brian Hamlin wrote:
> 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

Hi,

I would second Brian's suggestion to perform these type of operations in a 
relational database. Since you are working with SSURGO data, I may be able to 
send you some Postgresql database templates that we use.

Cheers,
Dylan




-- 
Dylan Beaudette
Soil Resource Laboratory
http://casoilresource.lawr.ucdavis.edu/
University of California at Davis
530.754.7341