DBF-to-mySQL JOIN: 3 CASES

[Frank Warmerdam]

On Mon, 10 Jan 2005 16:35:18 -0600, Janice Denovan
<jdenovan at georeferenceonline.com> wrote:
> ### CASE 2
> ##################################################################
> ### This mapfile code executes but fails to apply the WHERE clause
> ### Connectiontype OGR
>     CONNECTIONTYPE OGR
>    CONNECTION "POLY4800.shp"
>    DATA "SELECT * FROM POLY4800 LEFT
> JOIN 'odbc:hmomap'.tbl_results_mapping ON
>    POLY4800.OBJECTID = tbl_results_mapping.object_id
>    WHERE tbl_results_mapping.request_id = 2"

Janice,

I think your fundamental problem is that the WHERE clause is not working
in the above configuration, right?    I wasn't too sure how this would work
myself, so I reviewed the OGR SQL docs at:

  http://www.gdal.org/ogr/ogr_sql.html

Unfortunately, one of the caveats is that WHERE clauses may not
refer to fields in the joined table.  This is an issue with the OGR SQL
interpreter, not SQL in general. In fact, I am a bit surprised it does not
trigger an error. I guess the feature has a NULL field value for the
joined fields at the point where the WHERE clause is interpreted.

I could *in theory* modify the OGR SQL interpreter to try
re-evaluate the WHERE clause after loading the features but
this won't make things any faster and it would be somewhat tough
to implement.   I would add that this sort of sophisticated SQL
functionality may never be all that efficiently implemented in OGR
SQL.  I'm afraid for a good solution you might be better looking
at moving everything into a spatial RDBMS or otherwise
pre-preparing the data for fast use.

Time to take my son to swimming ... so I'll cut my comments there.

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