WG: [UMN_MAPSERVER-USERS] WG: OGR, ODBC join on OGR, ODBC

[Arnd Wippermann]

Hello Frank,

many thanks for your hint. It runs perfect.

That's the way to join ODBC-tables:

the ovf-file GEOLINES.ovf in the same directory as the mapfile

<OGRVRTDataSource>
    <OGRVRTLayer name="LINESLYR">
        <SrcDataSource>ODBC:GEOOBJEKT,GEOLINES</SrcDataSource>

<!--    not needed. Instead using SrcSQL
     	<SrcLayer>LINESLYR</SrcLayer>
-->
        <SrcSQL>SELECT * FROM GEOLINES LEFT JOIN GEOLINES_TREPPE ON
GEOLINES.ID_GEOOBJEKT = GEOLINES_TREPPE.ID_GEOOBJEKT;</SrcSQL>

<!--    apparently not needed, because the column position holds all the
info

    	<GeometryType>wkbLineString</GeometryType>
        <LayerSRS>WGS84</LayerSRS>
-->

	<GeometryField encoding="WKT" field="position"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

and the interesting part of the mapfile

LAYER
  NAME 'LINES'
  GROUP 'LINES'
  TYPE LINE
  STATUS ON

  CONNECTION 'GEOLINES.ovf'
  CONNECTIONTYPE OGR
  DATA 'LINESLYR'           # <OGRVRTLayer name="LINESLYR">

  CLASSITEM 'STUFENANZAHL'  # this is the column from the joined table
GEOLINES_TREPPE
  CLASS
    NAME 'Treppen'
    EXPRESSION ([STUFENANZAHL] > 100)
    STYLE
      COLOR 64 64 64
      WIDTH 5
    END
    STYLE
      COLOR 255 128 0
      WIDTH 3
    END
  END

  CLASS
    NAME 'SONST'
    STYLE
      COLOR 0 255 255
      WIDTH 2
    END
  END
 
Mit freundlichen Grüssen

Arnd Wippermann
http://gis.ibbeck.de/ginfo/


-----Ursprüngliche Nachricht-----
Von: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU] Im
Auftrag von Frank Warmerdam
Gesendet: Montag, 14. Mai 2007 22:49
An: MAPSERVER-USERS at LISTS.UMN.EDU
Betreff: Re: [UMN_MAPSERVER-USERS] WG: OGR, ODBC join on OGR, ODBC

Arnd Wippermann wrote:
> Hello,
> 
> I have an Access DB to hold the geometry as WKT-String and attributes 
> of a line layer and use OGR with ODBC to display my features.
> 
> GEOLINES           the line geometry as WKT-String
> GEOLINES_TREPPE    the attributes for the lines
> GEOMETRY_COLUMNS   the configuration of table, objecttyp and srs
> 
> I have no problem with
> 
> CONNECTIONTYPE OGR
> CONNECTION 'ODBC:GEOOBJEKT,GEOLINES'
> 
> to display my lines on the map.
> 
> But I want to classify my lines with values from the table
GEOLINES_TREPPE.
> 
> In Access the following query delivers what I expect:
> SELECT * FROM GEOLINES LEFT JOIN GEOLINES_TREPPE ON 
> GEOLINES.ID_GEOOBJEKT = GEOLINES_TREPPE.ID_GEOOBJEKT;
> 
> When I use this query for my GEOLINES layer in a mapfile and add the 
> DATA-line, nothing will display and also no error message is given.

Arnd,

I believe the problems is that the ODBC driver does not know what column in
your select result to use as the geometry since it finds this in the
GEOMETRY_COLUMNS table.  But this mechanism only works for simple tables,
not custom selects.

One option might be to use a VRT wrapper for the ODBC datasource to
accomplish this instead of trying to use GEOMETRY_COLUMNS.

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    | President OSGeo, http://osgeo.org