[gdal-dev] Another Join issue with OGR/ODBC

[Frank Warmerdam]

Yewondwossen Assefa wrote:
> Hi all,
> 
> I am into some issues when trying to do a  join using a shape file as a 
> primary table and an ODBC (sql server 2005) connection as the secondary 
> table.
> 
> The issue has to do with the schema name.
> 
> The following query (with a schema name: imsv7) works as expected:
>  - ogrinfo ODBC:sa/ttt at my_db -sql "SELECT * FROM imsv7.COMPSMN WHERE 
> COMPKEY=23533"
> 
> This same query without a schema does not work  (which is what  I 
> expected):
>    - ogrinfo ODBC:sa/ttt at my_db -sql "SELECT * FROM COMPSMN WHERE 
> COMPKEY=23533"
> 
> My issue is that I need to give the schema name when doing a join but 
> don't see how I can do that. Here is the  join statement:
> 
>  - ogrinfo f:/msapps/ttt/sqlserver/Sewer_Manholes_20090818.shp -sql 
> "SELECT *  FROM Sewer_Manholes_20090818 shp LEFT JOIN 
> 'ODBC:sa/ttt at my_db'.COMPSMN ON shp.COMPKEY = COMPSMN.COMPKEY  where 
> COMPKEY = 23533"

Assefa,

The core problem is that when you open the ODBC datasource directly,
your SQL gets sent to SQLServer but when you open the shapefile, the
SQL gets parsed and processed by OGR which knows nothing about schemas
and can only operate on features from the joined datasource that appears
as regular layers on it.

So, when you do "ogrinfo ODBC:sa/tt at my_db" do you see a layer corresponding
to imsv7?  If not, you cannot join to it properly.

It *might* be possible to include imsv7.compsmn as a table name in the
ODBC datasource name.  Something like:

   ODBC:sa/tt at my_db:imsv7.compsmn

If this works this table should be translated into a layer you can join
to.

In theory you could also wrap the ODBC datasource in a VRT datasource
using <SrcSQL> to ensure that the compsmn features end up as proper
features in a proper layer but I'm generally nervous about adding
this many layers of complexity, and it is essentially sure that if
you do this the join performance will be terrible.

Best regards,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent