[gdal-dev] SQL "LEFT JOIN"

[Kris R. DeLaney]

O.K.:

The following syntax for a ogr2ogr dbf LEFT JOIN works:


ogr2ogr -sql "SELECT AREASYMBOL, MUKEY, DRCLASSDCD FROM soils LEFT JOIN 
'master.dbf'.master ON soils.MUKEY = master.MUKEY" outshpdir soils.shp


Thanks to all for your help in figuring this out.

Kris R. DeLaney





----- Original Message ----- 
From: "Brian Hamlin" <maplabs at light42.com>
To: "Emilio Mayorga" <emiliomayorga at gmail.com>
Cc: "Kris R. DeLaney" <botany at strato.net>; <gdal-dev at lists.osgeo.org>
Sent: Tuesday, July 14, 2009 2:53 AM
Subject: Re: [gdal-dev] SQL "LEFT JOIN"


ok, well n that case, given your test data, an expression that works is

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

   substitute a Shp file directory, etc to suit your purposes

    -Brian


On Jul 13, 2009, at 6:53 PM, Emilio Mayorga wrote:

> 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
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/gdal-dev