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

[Yewondwossen Assefa]

Frank Warmerdam wrote:
> 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.
>
I do not see any thing refering to imsv7. I only see COMPSMN as part of 
the layer lists.
> 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.
>
 I have tried to add the schema and layer name but was not successful in 
getting a proper join.
> 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.
>
 I understand that:. I initially tested with vrt layer  but the join 
took extremely long time (3-4 minutes with ogrinfo, indexed on the join 
key) on about 3000 features in the shape and the 2700 in the database.

Thanks a lot for taking the time  to look into this.
> Best regards,


-- 
----------------------------------------------------------------
Assefa Yewondwossen           
Software Analyst   

Email: assefa at dmsolutions.ca    
http://www.dmsolutions.ca/

Phone: (613) 565-5056 (ext 14)
Fax:   (613) 565-0925
----------------------------------------------------------------