Join shape and dbms data: OGR?

[Michael Schulz]

Frank Warmerdam schrieb:
> 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,

Hi Frank,

thanks for your answer. It is working perfectly now.

For the moment it is only one table i want to join to the geometry of the shp-file, so at first
sight performance is good, but i will play with the indexes a little.

For all who have to use a similar approach here's a mapfile snipplet:

   LAYER
     ...
     CONNECTIONTYPE OGR
     CONNECTION "c:\mstest\data\fnpf.shp"  # shapefile holding only geometry and PID in .dbf
     DATA "SELECT * FROM fnpf LEFT JOIN 'ODBC:fnp'.fnp ON fnpf.PID=fnp.PID"
      # join to systemDSN fnp (here msaccess-db "fnp.mdb" with table fnp) on attribute PID,
      # now all fields of the db are also accessible for mapserver, e.g. "AZJAHR"
     LABELITEM "AZJAHR"
     ...
   END

Thanks, Michael


--
-----------------------------------------------------------
Michael Schulz                                in medias res
Dipl.-Geologe                              Gesellschaft für
                                 Informationstechnologie mbH
                                      In den Weihermatten 66
                                             79108 Freiburg
                                      0761 55695-95 (Fax 96)
mschulz at webgis.de              www.webgis.de/www.zopecms.de