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" <mapl...@light42.com>
To: "Emilio Mayorga" <emiliomayo...@gmail.com>
Cc: "Kris R. DeLaney" <bot...@strato.net>; <gdal-dev@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<bot...@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" <mapl...@light42.com>
To: "Kris R. DeLaney" <bot...@strato.net>
Cc: <gdal-dev@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/gdal-dev
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/gdal-dev
