Join shape and dbms data: OGR?

[Frank Warmerdam]

Michael Schulz wrote:
> Dear List,
>
> i want to evaluate the possibility to integrate a layer in mapserver,
> that takes the geometry data
> from a shape file (polygons with a unique id) and the attribute data
> from a MS-Access DB (this is
> unfortunately a precondition). The attribute data should be joined with
> the shape-file unique
> polygon id and thus accessible for classification and labeling.
>
> I looked through the OGR connection possibilities and now i am wondering
> if this could be achieved
> using an OGR virtual datasource, which has a SQLSrc defined that
> performs the join?

Michael,

In theory you can do it via an OGR connection without even using the VRT
stuff.  The DATA statement in the map file can be a SELECT statement when
you are using the OGR connection type.  In theory if you connect to a shapefile
you could have a select statement that also joins attribute information from
an ODBC connection.

First, I think you will need to have a .dbf associated with your shapefile that
has at least your relation key.  I don't think it is possible to use the FID
(ie. the shapefile sequence number) in the OGR SQL statements.

You might use something like:

   SELECT * from city
     LEFT JOIN 'ODBC:YourAccessDSN'.city_info ON city.id = city_info.id

assuming your shapefile is called "city.shp" and has a column called id,
and that your msaccess table is called city_info and also has a column
called id to relate on.

The 'ODBC:<YourAccessDSN>' portion of the above is a bit of OGR SQL special
syntax allowing you to refer to a different datasource than the one you listed
in your CONNECTION string.  Assuming you have setup a system DSN called
YourAccessDSN, and that there are no userid or password required it would look
something like the above.

I would add that connecting to ODBC datasources with alot of tables can be
somewhat expensive.  If there are alot of tables in your access database, you
might want to list the one you want access to in the datasource name so OGR
only has to collect the table definition for that one table.

Lastly, OGR is going to end up doing a SELECT on the ODBC datasource for each
shape it reads from the shapefile.  Something like "selct * from city_info
where id = <n>".  So make sure you index your secondary table on relation
key or performance will completely suck.  As it is, this will not exactly be
a high performance operation.

Useful info:

   http://ogr.maptools.org/ogr_sql.html
   http://ogr.maptools.org/drv_odbc.html

Good luck,
--
---------------------------------------+--------------------------------------
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