Oracle and SELECTs with OGR . .

[Bob Basques]

Frank Warmerdam wrote:

>>What we want to do as the "outer select":
>>
>>select * from sewer_permits1 where ogr_fid in (select max(ogr_fid) from
>>sewer_permits1 group by id);
>>
>>We can't seem to get this to be used AFTER the Spatial Select occurs
>>from the MapServer Request.  It's rather slow if it's used before the
>>MapServer Spatial Filter, so we need the Spatial aspects to run first,
>>then run this statement on the output from the MapServer Spatial
>>operation instead of the whole DB.
>>    
>>
> Bob,
>
>I don't think it is possible to use an accelerated
>spatial query with a SELECT statement (that is using
>the ExecuteSQL() entry in the driver) in the OGR OCI
>driver.   If you aren't in a position to use the built-in
>Oracle support in Mapserver, you may have to start
>changing code in non-trivial ways.
>  
>
What about passing the IMGEXT into the MAPFILE from the CGI request 
(can't that be done just like a template) and we do the whole thing in 
the ExecuteSQL in the MapFile.

something like:

select * from sewer_permits1 
 where ogr_fid 
    in (select max(ogr_fid) 
          from sewer_permits1
         where MDSYS.SDO_RELATE(Table0.GEOMETRY,
                                  MDSYS.SDO_GEOMETRY (3003,
                                                      41072,
                                                      NULL,
                                                      MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
                                                      MDSYS.SDO_ORDINATE_ARRAY([minx],[miny],0,
                                                                               [minx],[maxy],0,
                                                                               [maxx],[maxy],0,
                                                                               [maxx],[miny],0,
                                                                               [minx],[miny],0)
                                  ),
                                  'MASK=ANYINTERACT QUERYTYPE=WINDOW') = 'TRUE'
         group by id);

Will this work ok you think, or can we even pass the IMGEXT into the SQL 
like this?

bobb


>Best regards,
>--
>---------------------------------------+-----------------------------------=
>---
>I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.c=
>om
>light and sound - activate the windows | http://pobox.com/~warmerdam
>and watch the world go round - Rush    | Geospatial Programmer for Rent
>
>  
>