[Mapserver-users] OGR JOINs

[Frank Warmerdam]

Jan Hartmann wrote:
> WOW, this is important! Frank, could you be a bit more specific on this 
> JOIN support:
> 
> 1) How do you specify the tables in the join? In your example you use a 
> shapefile called "shppoly", and you use the tables "poly" and "idlink" 
> for the JOIN. Where are those two tables located?

Jan,

The particular example noted is available from the MapServer "msautotest"
cvs tree.  It is one of my test suite so if you want you can pull it out of
VCS to try yourself.

In the case in question the idlink.dbf is in the same directory as the
shapefile it is linked from.  However, one of the hard parts of the JOIN
support I implemented for DM Solutions was supporting linking to external
tables.

> 2) What are the restrictions? How much SQL do you support?

The full documentation of "OGR SQL" is at:

   http://gdal.velocet.ca/projects/opengis/ogrhtml/ogr_sql.html

Note that if you are working against a real RDBMS like Oracle or PostGIS
the SQL would actually be interpreted by the RDBMS so OGR SQL isn't
used.  OGR SQL is used as a fallback for non RDBMS formats.

Note that the OGR SQL LEFT JOIN isn't quite a proper join in the RDBMS sense.
Also, you need to index the key column in the secondary table to get decent
performance, and currently indexing is only supported on shapefiles (and
standalone .dbf files).  Oddly it is not supported on MapInfo files (for instance)
even though I use the MapInfo .ind format for the attribute indexing.

> 3) Has ogr2ogr some of this JOIN functionality?

Oddly not, though ogrinfo does.  I will add this capability to ogr2ogr today.
The lack of it is kind of silly.

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    | Geospatial Programmer for Rent