DBF-to-mySQL JOIN: 3 CASES

[Janice Denovan]

Hi List,

Have enjoyed working with MapServer 4.1 and reading this list over 2 months.

We have a polygon shapefile with 4800 objects (POLY4800.SHP).

We run non-spatial queries against the shape file using an in-house, multi-
user
software application and write the query scores to mySQL 4.x tables.

We extract request_id, object_id and query_score from the mySQL tables
and append to a mySQL table for mapping purposes (tbl_results_mapping.myd).
tbl_results_mapping.myd is indexed on request_id and indexed on object_id).

We need to display the shapefile in MapServ 4.1 symbolized by query_score
and
filtered by request_id. I see this problem as a simple 1:1 table join.

The work is distributed across two servers:
Server1: WinXP/IIS/MapServ 4.1 (supports WMS/WFS)
Server2: WinXP/IIS/mySQL 4.0 (basic mySQL, no spatial extensions)

So far:
### CASE 1
##################################################################
### This mapfile code works but takes 15+ seconds to refresh the map
### 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"

### 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"

### CASE 3
##################################################################
### This mapfile code executes and might draw faster for small extents but
### still too slow; WHERE clause not working
### Connectiontype WFS/OGR
    CONNECTIONTYPE WFS
    CONNECTION "http://127.0.0.1/scripts/mapserv.exe?map=wfs.map"

    wfs.map for CASE 3
    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"
    CLASS
      COLOR 255 0 255
    END

Can you suggest a way to optimize this system ?  A shapefile-based solution
is preferred
at this time.

Thanks very much,
Janice
Vancouver, BC