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/t...@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/t...@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/t...@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/t...@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/t...@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: ass...@dmsolutions.ca
http://www.dmsolutions.ca/
Phone: (613) 565-5056 (ext 14)
Fax: (613) 565-0925
----------------------------------------------------------------
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/gdal-dev
