[gdal-dev] SQL "LEFT JOIN"

[Emilio Mayorga]

Kris,

This statement worked for me a few months back:

ogr2ogr -sql "SELECT
basinid,basinname,area,basinorder,mouth_lon,mouth_lat FROM stn30test
srcgeom LEFT JOIN 'dbffilelongname.dbf'.dbffilelongname jointbl ON
srcgeom.basinid = jointbl.basinid" -f "ESRI Shapefile"
stn30test_join.shp stn30test.shp

I can't find the exact GDAL version, but I'm pretty sure it's 1.5.x;
it's from FWTools2.2.8 (on Windows). stn30test.shp is the source shape
file, dbffilelongname.dbf the joined dbf, and stn30test_join.shp the
output. I didn't qualify the select fields with a table alias b/c
they're all coming from the dbf file, so I don't know if that would
have an impact. All files are in the same folder.

Hope that helps.

-Emilio Mayorga



On Mon, Jul 13, 2009 at 5:37 PM, Kris R. DeLaney<botany at strato.net> wrote:
> 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