[gdal-dev] joining shapefile to PG table

[William Kyngesburye]

I'm having a problem joining a Postgres table to a shapefile.  I've done this in the past with MySQL tables.

ogrinfo -al -so myshapefile.shp -sql "SELECT * FROM myshapefile LEFT JOIN 'PG:dbname=mypgdb host=localhost user=someuser password='.mypgtable ON myshapefile.keyfield = mypgtable.keyfield"
INFO: Open of `myshqpefile.shp'
      using driver `ESRI Shapefile' successful.
ERROR 1: SQL Expression Parsing Error: syntax error


ogrinfo on the table works:

ogrinfo -al -so 'PG:dbname=mypgdb host=localhost user=someuser password=' mypgtable
INFO: Open of `PG:dbname=mypgdb host=localhost user=someuser password='
      using driver `PostgreSQL' successful.
...

I'm using a character key field (I remember problems with that in mysql), but that shouldn't affect the syntax.  (If GDAL 1.8 still has this limitation, major bummer, I'll have to convert all my shapefiles to PostGIS to join the tables.)


The mysql method has a tables=mypgtable parameter in the connection, but that doesn't help the PG connection.  Testing the mysql method (different data) gives me a segfault on the join, but works on the shapefile and table separately.  This used to work (GDAL 1.6, maybe 1.7?).

GDAL 1.8.0, Postgres 9.0.4 (both GDAL libpq and server), MySQL 5.1.52 (GDAL) & 5.1.41 (server)

-----
William Kyngesburye <kyngchaos*at*kyngchaos*dot*com>
http://www.kyngchaos.com/

"We are at war with them. Neither in hatred nor revenge and with no particular pleasure I shall kill every ___ I can until the war is over. That is my duty."

"Don't you even hate 'em?"

"What good would it do if I did? If all the many millions of people of the allied nations devoted an entire year exclusively to hating the ____ it wouldn't kill one ___ nor shorten the war one day."

<Ha, ha> "And it might give 'em all stomach ulcers."

- Tarzan, on war