[mapserver-users] SQL join question

[Frank Warmerdam]

Jim Hagedorn wrote:
> I am using  OGR in my map file and have a CONNECTION and DATA lines.  I 
> have a SQL statement in the DATA.  It works when I use it on the ACCESS 
> database that is the Personal GeoDatabase in ESRI but when I put it into 
> the map file I get an error message.  It should bring back 33 records 
> and the message says that the search brought back no results.  I’m using 
> a left join, which is in the example for OGR and geodatabases but the 
> only join I can make work is an inner join and that doesn’t bring back 
> the correct results like the left join does.
> 
>  
> 
> This is the sql that atleast brings back some results, all be it not the 
> results I am expecting:
> 
> SELECT *
> 
> FROM CEMSHEET_2 INNER JOIN milbank_cemetary_UTM ON 
> CEMSHEET_2.LOT=milbank_cemetary_UTM.lot
> 
>  
> 
> And this one brings back the expected results in access but gives me the 
> no results returned in GeoMoose when I run a search.
> 
>  
> 
> SELECT CEMSHEET_2.NAME, CEMSHEET_2.LOT, CEMSHEET_2.AGE
> 
> FROM CEMSHEET_2 LEFT JOIN milbank_cemetary_UTM ON CEMSHEET_2.LOT = 
> milbank_cemetary_UTM.lot
> 
>  
> 
>  From what I have read in the documentation on mapserver, that the left 
> join is the only join that is suppost to work but I get results when I 
> use inner join.

Jim,

If you are using SELECT statements in the DATA statement of an layer
using OGR connectiontype, then you need to focus on OGR join support. This
will be completely distinct from the MapServer JOIN object.

Furthermore, OGR actually passes the SQL through access when you operate
on personal geodatabases and so any SQL operation supported by Access
should be ok.

I'd suggest you do some testing at the commandline with ogrinfo to
determine what sort of results OGR produces for different queries.

Something like:

  ogrinfo abc.mdb -sql "select * from NHDPointEventFC"

I tried simple queries against a personal geodatabase and I got
reasonable results.  I didn't try any joins.

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